show us a true cut and paste


ops$tkyte@ORA817DEV.US.ORACLE.COM> delete from plan_table;

158 rows deleted.

ops$tkyte@ORA817DEV.US.ORACLE.COM> explain plan set statement_id = 'DD' for     
  2  select * from emp;

Explained.

ops$tkyte@ORA817DEV.US.ORACLE.COM> @/export/home/ora817/rdbms/admin/utlxpls.sql

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |       |      |        |       |       |
|  TABLE ACCESS FULL        |EMP       |       |      |        |       |       |
--------------------------------------------------------------------------------

ops$tkyte@ORA817DEV.US.ORACLE.COM>


works for me and I've never seen it "not work" 

1) use explain plan and @?/rdbms/admin/utlxpls.sql to query the plan.  That output will show the pstart/pstop

but, in your case, you have but one index partition.  so it wont be very interesting.  As we discussed right above -- in order to be unique and local, it would have to be part of the partition key, name is not, hence it will be a global index and by default that will be a single partition.

ops$tkyte@ORA817DEV> alter table t add constraint t_pk primary key(name);

Table altered.

ops$tkyte@ORA817DEV> create index t_idx on t(idno);

Index created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> delete from plan_table;

3 rows deleted.

ops$tkyte@ORA817DEV> explain plan for
  2  select * from t where name = 'One';

Explained.

ops$tkyte@ORA817DEV> set echo off
@?/rdbms/admin/utlxpls.sql

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     3 |   30 |      1 |       |       |
|  TABLE ACCESS BY GLOBAL IN|T         |     3 |   30 |      1 | ROWID | ROW L |
|   INDEX RANGE SCAN        |T_PK      |     3 |      |      1 |       |       |
--------------------------------------------------------------------------------

6 rows selected.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> delete from plan_table;

3 rows deleted.

ops$tkyte@ORA817DEV> explain plan for
  2  select * from t where idno = '11';

Explained.

ops$tkyte@ORA817DEV> set echo off
@?/rdbms/admin/utlxpls.sql

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     1 |   10 |      1 |       |       |
|  TABLE ACCESS BY GLOBAL IN|T         |     1 |   10 |      1 |     2 |     2 |
|   INDEX RANGE SCAN        |T_IDX     |     1 |      |      1 |       |       |
--------------------------------------------------------------------------------

6 rows selected.


<b>see the pstart/pstop columns...</b>


 

that would not be the 9i plan_table.

rerun utlxplan.sql to get the right one created, that one is really old.  OTHER_TAG is a column in that table these days.

ops$tkyte@ORA9IR2> desc plan_table
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 STATEMENT_ID                           VARCHAR2(30)
 TIMESTAMP                              DATE
 REMARKS                                VARCHAR2(80)
 OPERATION                              VARCHAR2(30)
 OPTIONS                                VARCHAR2(255)
 OBJECT_NODE                            VARCHAR2(128)
 OBJECT_OWNER                           VARCHAR2(30)
 OBJECT_NAME                            VARCHAR2(30)
 OBJECT_INSTANCE                        NUMBER(38)
 OBJECT_TYPE                            VARCHAR2(30)
 OPTIMIZER                              VARCHAR2(255)
 SEARCH_COLUMNS                         NUMBER
 ID                                     NUMBER(38)
 PARENT_ID                              NUMBER(38)
 POSITION                               NUMBER(38)
 COST                                   NUMBER(38)
 CARDINALITY                            NUMBER(38)
 BYTES                                  NUMBER(38)
 OTHER_TAG                              VARCHAR2(255)
 PARTITION_START                        VARCHAR2(255)
 PARTITION_STOP                         VARCHAR2(255)
 PARTITION_ID                           NUMBER(38)
 OTHER                                  LONG
 DISTRIBUTION                           VARCHAR2(30)
 CPU_COST                               NUMBER(38)
 IO_COST                                NUMBER(38)
 TEMP_SPACE                             NUMBER(38)
 ACCESS_PREDICATES                      VARCHAR2(4000)
 FILTER_PREDICATES                      VARCHAR2(4000)
 
 

it is not an "entry", it is the view definition itself.

ops$tkyte@ORA9IR2> set long 5000
ops$tkyte@ORA9IR2> select text from all_views where view_name = 'USER_TAB_COLS'; 
TEXT
--------------------------------------------------------------------------------select o.name,
       c.name,
       decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                       2, decode(c.scale, null,
                                 decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                                 'NUMBER'),
                       8, 'LONG',
                       9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                       12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
                       58, nvl2(ac.synobj#, (select o.name from obj$ o
                                where o.obj#=ac.synobj#), ot.name),
                       69, 'ROWID',
                       96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                       105, 'MLSLABEL',
                       106, 'MLSLABEL',
                       111, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                       113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                       121, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       122, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       123, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       178, 'TIME(' ||c.scale|| ')',
                       179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',<b>
                       180, 'TIMESTAMP(' ||c.scale|| ')',</b>
                       181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
                       231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
                       182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
                       183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
                             c.scale || ')',
                       208, 'UROWID',
                       'UNDEFINED'),
 

KEY KEY -> you have something like 

where partition_key between :x and :y;

at runtime, it knows "I will have the keys to get the low partitoin and the high partition" -- hence "key key"


ROWID (ROW L) -> rowid to rowid last (one way of reading it).  Means I'll get a rowid for the first partition we'll hit from the global index and then get more global rowids and finally get a last global rowid -- so we really have no idea how many or what partitions we'll hit until we actually range scan the global index.


1) yes

2) usually (bind variable peeking can change this)

3) not really, pstart = 2 pstop = 3 and there are 8 partitions.  we have eliminated 6 partitions but we will use global stats to optimize.


ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /
Table created.

ops$tkyte@ORA9IR2> create index tidx1 on t(x) global;
Index created.

ops$tkyte@ORA9IR2> variable x varchar2(20)
ops$tkyte@ORA9IR2> @plan "select * from t where dt = to_date(:x)"
PLAN_TABLE_OUTPUT
----------------------------------------

-----------------------------------------------------------------------
| Id  | Operation              |  Name| Bytes | Cost  | Pstart| Pstop |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    36 |     2 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |       |       |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | T    |    36 |     2 |   KEY |   KEY |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T"."DT"=TO_DATE(:Z))
Note: cpu costing is off
15 rows selected.

ops$tkyte@ORA9IR2> @plan "select * from t where x = to_Number(:x)"
PLAN_TABLE_OUTPUT
-------------------------------------------------------

-----------------------------------------------------------------------
| Id  | Operation                          |  Name       |Pstart| Pstop |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |      |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| T           |ROWID | ROW L |
|*  2 |   INDEX RANGE SCAN                 | TIDX1       |      |       |
-------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."X"=TO_NUMBER(:Z))
Note: cpu costing is off
15 rows selected.

ops$tkyte@ORA9IR2> @plan "select * from t where dt >= to_date(:x) and dt < to_date( '14-mar-2003' )"

PLAN_TABLE_OUTPUT
----------------------------------------------------

-----------------------------------------------------------------
| Id  | Operation                 |  Name       | Pstart| Pstop |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |       |       |
|*  1 |  FILTER                   |             |       |       |
|   2 |   PARTITION RANGE ITERATOR|             |   KEY |     2 |
|*  3 |    TABLE ACCESS FULL      | T           |   KEY |     2 |
-----------------------------------------------------------------

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

   1 - filter(TO_DATE(:Z)<TO_DATE('2003-03-14 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   3 - filter("T"."DT">=TO_DATE(:Z))
Note: cpu costing is off
17 rows selected.