SQL*Plus: Release 10.2.0.2.0 - Production on Sat Oct 28 11:42:38 2006

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> drop table test_par
  2  /

Table dropped.

SQL>
SQL> create table test_par
  2     (
  3     col1 number not null,
  4     col2 number not null
  5     )
  6  nologging compress pctfree 0
  7  partition by range (col1)
  8  (partition p1_to_4 values less than (5),
  9  partition p5 values less than (6)
 10  )
 11  /

Table created.

SQL>
SQL> insert /*+ append */ into test_par
  2  select mod(rownum,4)+1,rownum
  3  from dual
  4  connect by level <= 100000
  5  /

100000 rows created.

SQL>
SQL> create bitmap index idx02_test_par
  2  on test_par (col2)
  3  local nologging
  4  /

Index created.

SQL>
SQL> begin
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3     ownname          => user,
  4     tabname          => 'test_par',
  5     partname         => null,
  6     estimate_percent => 100,
  7     block_sample     => false,
  8     method_opt       => 'for all columns size 1',
  9     degree           => null,
 10     granularity      => 'ALL',
 11     cascade          => true,
 12     stattab          => NULL,
 13     statid           => NULL,
 14     statown          => NULL,
 15     no_invalidate    => FALSE);
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL> set linesize 500 pagesize 0
SQL>
SQL> variable l_col1 number
SQL>
SQL> define l_col1 = 5
SQL> truncate table plan_table
  2  /

Table truncated.

SQL> explain plan for
  2  select count(*) from test_par
  3  where col1 = :l_col1
  4  /

Explained.

SQL>
SQL> select * from table (dbms_xplan.display)
  2  /

---------------------------------------------------------------------------------------
| Id  | Operation               |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |     1 |     3 |    11 |       |       |
|   1 |  SORT AGGREGATE         |             |     1 |     3 |       |       |       |
|   2 |   PARTITION RANGE SINGLE|             |       |       |       |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL    | TEST_PAR    | 25000 | 75000 |    11 |   KEY |   KEY |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TEST_PAR"."COL1"=TO_NUMBER(:Z))

Note: cpu costing is off

16 rows selected.

SQL>
SQL> define l_col1 = 1
SQL> truncate table plan_table
  2  /

Table truncated.

SQL>
SQL> explain plan for
  2  select count(*) from test_par
  3  where col1 = :l_col1
  4  /

Explained.

SQL>
SQL> select * from table (dbms_xplan.display)
  2  /

---------------------------------------------------------------------------------------
| Id  | Operation               |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |     1 |     3 |    11 |       |       |
|   1 |  SORT AGGREGATE         |             |     1 |     3 |       |       |       |
|   2 |   PARTITION RANGE SINGLE|             |       |       |       |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL    | TEST_PAR    | 25000 | 75000 |    11 |   KEY |   KEY |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TEST_PAR"."COL1"=TO_NUMBER(:Z))

Note: cpu costing is off

16 rows selected.

SQL> truncate table plan_table
  2  /

Table truncated.

SQL>
SQL> explain plan for
  2  select count(*) from test_par
  3  where col1 = 5
  4  /

Explained.

SQL>
SQL> select * from table (dbms_xplan.display)
  2  /

------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    13 |     2 |       |       |
|   1 |  SORT AGGREGATE      |             |     1 |    13 |       |       |       |
|*  2 |   TABLE ACCESS FULL  | TEST_PAR    |     1 |    13 |     2 |     2 |     2 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TEST_PAR"."COL1"=5)

Note: cpu costing is off

15 rows selected.

SQL> truncate table plan_table
  2  /

Table truncated.

SQL>
SQL> explain plan for
  2  select count(*) from test_par
  3  where col1 =1
  4  /

Explained.

SQL>
SQL> select * from table (dbms_xplan.display)
  2  /

------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  | Pstart| Pstop |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |     3 |    11 |       |       |
|   1 |  SORT AGGREGATE      |             |     1 |     3 |       |       |       |
|*  2 |   TABLE ACCESS FULL  | TEST_PAR    | 25000 | 75000 |    11 |     1 |     1 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("TEST_PAR"."COL1"=1)

Note: cpu costing is off

15 rows selected.

SQL>