Skip to Main Content
  • Questions
  • Partition pruning/elimination -reg..

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, rahul.

Asked: December 17, 2001 - 9:32 pm UTC

Last updated: August 24, 2016 - 12:54 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Dear Tom,

Sorry for the delay.

1 CREATE TABLE emp (no number, name VARCHAR2(10) ,
2 PRIMARY KEY (no, name))
3 partition by hash(no)
4 (
5 PARTITION PART1 TABLESPACE "TS1" ,
6 PARTITION PART2 TABLESPACE "TS2"
7* )
8 /

Table created.

insert into emp(no,name) values (&1,'&2');-- insert about 10 rows
CREATE INDEX emp_IDX
ON emp(no)
LOCAL
(PARTITION PART1 TABLESPACE TS1_INDX_N,
PARTITION PART2 TABLESPACE TS2_INDX_N)

exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'EMP_SCHEMA')
OR
ANALYZE TABLE EMP PARTITION(PART1) COMPUTE STATISTICS;
ANALYZE TABLE EMP PARTITION(PART2) COMPUTE STATISTICS;
ANALYZE INDEX EMP_IDX PARTITION(PART1) COMPUTE STATISTICS;
ANALYZE INDEX EMP_IDX PARTITION(PART1) COMPUTE STATISTICS;

EXPLAIN PLAN SET STATEMENT_ID='NO_PRUNE'
FOR
SELECT * FROM EMP
WHERE NO IN(100,455,777)

SELECT PARTITION_START, PARTITION_STOP FROM PLAN_TABLE WHERE STATEMENT_ID='NO_PRUNE';

KEY(INLIST)---------> IS THE DISPLAYED VALUE.(NO EKIMINATION OCCURS)

EXPLAIN PLAN SET STATEMENT_ID='PRUNE'
FOR
SELECT * FROM EMP
WHERE NO IN(100);

SELECT PARTITION_START, PARTITION_STOP FROM PLAN_TABLE WHERE STATEMENT_ID='PRUNE';

A PARTITION(start/stop) VALUE IS AVAILABLE. (ELIMINATION OCCURS)

This is the actual problem. It appears that for multiple values in the IN list, there is no elimination.


Please advise,
Thanks,
Rahul.

and Tom said...

In your case, you have a primary key on (NO,NAME).

This primary key created an index. It is by default a GLOBAL index. It it not partitioned.

When it sees "where no in ( x, y, z )", it is (rightly) choosing the primary key index to answer the query. No partition elimination needed (or desired in fact).

Lets try this without the key:

tkyte@TKYTE816> CREATE TABLE emp (no number, name VARCHAR2(10) )
2 partition by hash(no)
3 (
4 PARTITION PART1 TABLESPACE "P2" ,
5 PARTITION PART2 TABLESPACE "P3"
6 )
7 /

Table created.

tkyte@TKYTE816>
tkyte@TKYTE816> insert into emp select empno, ename from scott.emp;

14 rows created.

tkyte@TKYTE816>
tkyte@TKYTE816> select * from emp partition(part1);

NO Tablespace Name
---------- ----------------
7369 SMITH
7499 ALLEN
7654 MARTIN
7698 BLAKE
7782 CLARK
7839 KING
7876 ADAMS
7934 MILLER

8 rows selected.

tkyte@TKYTE816> select * from emp partition(part2);

NO Tablespace Name
---------- ----------------
7521 WARD
7566 JONES
7788 SCOTT
7844 TURNER
7900 JAMES
7902 FORD

6 rows selected.

tkyte@TKYTE816>
tkyte@TKYTE816>
tkyte@TKYTE816> CREATE INDEX emp_IDX
2 ON emp(no)
3 LOCAL
4 (PARTITION PART1 TABLESPACE p2,
5 PARTITION PART2 TABLESPACE p3)
6
tkyte@TKYTE816> analyze table emp compute statistics;

Table analyzed.

tkyte@TKYTE816>
tkyte@TKYTE816> alter tablespace p3 offline;

Tablespace altered.

tkyte@TKYTE816> select * from emp where no in (7369);

NO Tablespace Name
---------- ----------------
7369 SMITH

tkyte@TKYTE816> select * from emp where no in ( 7369, 7499, 7654 );

NO Tablespace Name
---------- ----------------
7369 SMITH
7499 ALLEN
7654 MARTIN

tkyte@TKYTE816>
tkyte@TKYTE816> select * from emp where no in ( 7521 );
select * from emp where no in ( 7521 )
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: 'C:\ORACLE\ORADATA\TKYTE816\P3.DBF'


tkyte@TKYTE816> select * from emp where no in ( 7521, 7566, 7788 );
select * from emp where no in ( 7521, 7566, 7788 )
*
ERROR at line 1:
ORA-00376: file 9 cannot be read at this time
ORA-01110: data file 9: 'C:\ORACLE\ORADATA\TKYTE816\P3.DBF'


tkyte@TKYTE816> alter tablespace p3 online;

Tablespace altered.


As you can see -- partition elimination takes place with "where no in ( x)" and "where no in (x,y,z)"....

watch out for those global indexes.


Rating

  (71 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

partition elimination

Rahul, January 03, 2002 - 2:11 pm UTC

Tom,

I dropped the global index and created a local index:
alter table emp
add constraint local_idx
primary key(no,name)
using index local

select * from emp partition(part1)
select * from emp partition(part2), both return values from their appropriate partitions.

Now when i do a select * from emp where no in(x) results in start partition and stop partition in the plan table with the partition value-- say 2.
select * from emp where no in(x,y) results in key(inlist)in plan_table.(Note: x,y belong to same partition)

Is there a way to tell the optimizer to use the partition ?

Thanks





Tom Kyte
January 03, 2002 - 3:33 pm UTC

It won't do it unless it feels its worth the effort.  We can "trick it" into thinking it is in fact worth the effect:

ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE TABLE emp (no number, name VARCHAR2(10)       )
  2  partition by hash(no)
  3  (
  4    PARTITION PART1   TABLESPACE testing_lmt  ,
  5    PARTITION PART2   TABLESPACE users
  6  )
  7  /

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into emp select empno, ename from scott.emp;
14 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp partition(part1);

        NO NAME
---------- ------------------------------
      7369 SMITH
      7499 ALLEN
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7876 ADAMS
      7934 MILLER

8 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp partition(part2);

        NO NAME
---------- ------------------------------
      7521 WARD
      7566 JONES
      7788 SCOTT
      7844 TURNER
      7900 JAMES
      7902 FORD

6 rows selected.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> CREATE INDEX emp_IDX
  2  ON emp(no)
  3  LOCAL
  4  (PARTITION PART1 TABLESPACE testing_lmt,
  5   PARTITION PART2 TABLESPACE users)
  6  /

Index created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table emp compute statistics;
Table analyzed.

<b>here is the part where we trick it into thinking its "bigger than a bread basket"</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'EMP', 'PART1', numrows => 1000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'EMP', 'PART2', numrows => 1000000, numblks => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'EMP', numrows => 2000000, numblks => 200000 );

PL/SQL procedure successfully completed.


ops$tkyte@ORA817DEV.US.ORACLE.COM> alter tablespace users offline;
Tablespace altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp where no in (7369);

        NO NAME
---------- ------------------------------
      7369 SMITH

ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp where no in ( 7369, 7499, 7654 );

        NO NAME
---------- ------------------------------
      7369 SMITH
      7499 ALLEN
      7654 MARTIN

<b>It obviously did partition elimination here, if it didn't, we would get the errors like we do below:</b>


ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp where no in ( 7521 );
select * from emp where no in ( 7521 )
              *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/d04/oradata/ora817dev/users.dbf'


ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from emp where no in ( 7521, 7566, 7788 );
select * from emp where no in ( 7521, 7566, 7788 )
              *
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/d04/oradata/ora817dev/users.dbf'


ops$tkyte@ORA817DEV.US.ORACLE.COM> alter tablespace users online;

Tablespace altered. 

Partition Pruning and Performance.

Anthony, December 09, 2002 - 10:09 am UTC

Hi tom,

Can find more info on your site that talks about partition pruning and performace. Look at this query for instance.

tst_usr@tst> select sum(num_rows) from user_taB_partitions where table_name='PART_TBL1';

SUM(NUM_ROWS)
-------------
1884708

tst_usr@tst> SELECT INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME LIKE 'PART%'

INDEX_NAME COLUMN_NAME
------------------------------ -----------------------------
PART_PK INTERNAL_KEY

tst_usr@tst> select PARTITION_NAME,HIGH_value from user_tab_partitions where table_NAME='PART_TBL1';

PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------
PART_PART3 1500001
PART_PART4 2000001
PART_PART5 2500001
PART_PART6 3000001
PART_PART1 500001
PART_PART2 1000001

tst_usr@tst> select * from PART_TBL1 Partition (PART_PART1) where internal_key between 1 and 50000

49089 rows selected.

Elapsed: 00:00:52.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1322 Card=48656 Byte
s=4768288)

1 0 TABLE ACCESS (FULL) OF 'PART_TBL1' (Cost=1322 Card=48656 B
ytes=4768288)





Statistics
----------------------------------------------------------
21 recursive calls
12 db block gets
216826 consistent gets
9356 physical reads
395224 redo size
4480574 bytes sent via SQL*Net to client
265394 bytes received via SQL*Net from client
3274 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
49089 rows processed

I'm expecting that ORACLE will only read 1 partition. But based on the explain plan it did an FTS... Can you explain why?

Cheers,
NOTNA

Tom Kyte
December 09, 2002 - 10:26 am UTC

It is just the default level of information autotrace gives you.  It is not showing you the partition start/stop.  Consider:


ops$tkyte@ORA920> CREATE TABLE t
  2  (
  3    x int primary key,
  4    collection_year int
  5  )
  6  PARTITION BY RANGE (COLLECTION_YEAR) (
  7    PARTITION PART_95 VALUES LESS THAN (1996) ,
  8    PARTITION PART_96 VALUES LESS THAN (1997) ,
  9    PARTITION PART_97 VALUES LESS THAN (1998) ,
 10    PARTITION PART_98 VALUES LESS THAN (1999) ,
 11    PARTITION PART_99 VALUES LESS THAN (2000) ,
 12    PARTITION PART_00 VALUES LESS THAN (2001) ,
 13    PARTITION PART_01 VALUES LESS THAN (MAXVALUE)
 14  )
 15  ;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t
  2  select rownum, 1995+mod(rownum,7) from all_objects;

29345 rows created.


ops$tkyte@ORA920> set autotrace traceonly
ops$tkyte@ORA920> select * from t partition(part_98);

4192 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=2132)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=82 Bytes=2132)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        295  consistent gets
          0  physical reads
          0  redo size
      74679  bytes sent via SQL*Net to client
       3568  bytes received via SQL*Net from client
        281  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       4192  rows processed

ops$tkyte@ORA920> set autotrace off

<b>full table scan -- but missing the partition start/stop information</b>

ops$tkyte@ORA920> explain plan set statement_id = 't1' for
  2  select * from t partition(part_98);

Explained.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

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

-----------------------------------------------------------------------------------
| Id  | Operation            |  Name  | Rows  | Bytes | Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |    82 |  2132 |     2 |       |       |
|   1 |  TABLE ACCESS FULL   | T      |    82 |  2132 |     2 |     4 |     4 |
-------------------------------------------------------------------------------

Note: cpu costing is off

9 rows selected.

<b>But the more "verbose" output of the explain plan shows the partitions that will be accessed...</b>



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

2 rows deleted.

 

Partition Pruning

Wayne, June 21, 2004 - 5:33 pm UTC

Tom,
We are in Oracle817 (will move to 9iR2 in 2 months).
I have a big table like this (each partition has approx 2.5M rows):
TRANS_FACT
(TRANS_NO, NUMBER(12) NOT NULL,
TRANS_DATE DATE NOT NULL, -- Partition Key (partitioned monthly)
TRANS_TYPE NUMBER(10),
STORE_ID NUMBER(10),
PROD_ID NUMBER(10),
....
)

My problem is:
When I create two local bitmap indexes (index1 on trans_date, index2 on trans_type), partition pruning doesn't work for the following query (EXPLAIN PLAN shows it is doing partition iteration):
select count(*), trans_type
from trans_fact
where trans_date between '1-jan-2004' and '31-jan-2004' -- restricting to partition (P200401)
group by trans_type;

But if I create an prefixed index to include both (trans_date, trans_type), it now starts pruning. My issue with doing so is, all indexes will have to include the partition key, making them taking more spaces and bitmap indexes may not be feasible because of the cardinalities.

Could you explain why separate indexes won't let partition pruning work? or there are some setting I should do to make it work?

Thanks,

Tom Kyte
June 21, 2004 - 8:52 pm UTC

entire simple example would be?

a simple 2 partition table with bitmap index and a call to dbms_stats.set_table_stats should be enough to emulate the behavior...



Partition Pruning

Wayne, June 22, 2004 - 4:32 am UTC

Hi Tom,
I used the Oracle 9iR2 on my laptop to conduct the following test (I copied SH.SALES table into my schema and make it 8 times as big as the original one).
Could you let me know the following:
1) What's the difference between set_table_stats and gather_table_stats in dbms_stats?
2) The following results shows costs is higher after dbms_stats.gather_index_stats() is called, but it does run a little faster, could you explain a little bit from the following result? Will they behave differently in 8i then in 9i?
----------------------------------------------------
select count(*) from sales; -- partitioned by time_id

COUNT(*)
----------
8130168

Elapsed: 00:00:10.04
select count(*) from sales partition (p_q2_1998);

COUNT(*)
----------
581640


========
Sorry, I don't know how to paste my explain plan output here nice and clean, it seems to wrap lines and looks garbled. but in short,
1) With two individual bitmap indexes (time_id)and (prod_id), cost =7; After gather_index_stats on them cost = 37;
2) With 1 bitmap index (time_id, product_id) the cost = 3;
After gather_index_stats, cost = 75;
Here's the plan detail:
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 75 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | |
|* 2 | FILTER | | | | | | |
| 3 | PARTITION RANGE ITERATOR| | | | | KEY | KEY |
|* 4 | INDEX SKIP SCAN | SALES_TIME_PROD_BIDX | 125 | 1625 | 75 | KEY | KEY |
----------------------------------------------------------------------------------------------------

Does that indicate separate indexes will work fine? I will test in the 8i environment later.

Thank you very much!


Tom Kyte
June 22, 2004 - 8:40 am UTC

1) should be fairly obvious?

set -- you give us numbers, you tell us what you would like us to believe about the table.

gather -- we read the table and figure out what it is all about.


2) without proper statistics, you cannot possibly get correct costing information. Hence the low numbers were a side effect of the optimizer not having data to work with. If the plans did not change but ran "faster" the second time, you are seeing the effects of the buffer cache, you just read all of the data.

In general, bitmaps should be on single columns. If you have two bitmaps one on time_id and one on prod_id, queries such as:

where time_id = :x and prod_id = :y (bitmap AND)

where time_id = :x or prod_id = :y (bitmap OR)

where time_id = :x (single bitmap)

where prod_id = :y (single bitmap)


are all easy -- with an index on time_id,prod_id -- the 2cnd and 4th queries most likely cannot use the bitmaps.


Stats Helps

Wayne, June 23, 2004 - 1:27 am UTC

Tom,
Your advice is most helpful. I re-created one bitmap index on the partition key (trans_date), and one for each low cardinality foreign key columns and run
dbms_stats.gather_index_stats('owner', 'ind_name');
After that, partition prunings are blazingly fast(most run within 1-5 sec).

Could you kindly point out what the best ways are to collect stats on table partitions so that CBO can find the best execution plan?
Apparently, GATHER_TABLE_STATS doesn't analyze the table's local indexes, nor does the GATHER_SCHEMA_STATS do, which is scheduled on weekends by the DBA. I am thinking about gathering stats for the most recent partitions (loop for all indexes) when the ETL is completed, will that work? Any better ways in 9i? Can we monitor indexes on the partition level?

Could you also give a short example explaining how to export the stats from one table and emport into another table to trick the optimizer for testing?

Thank you very much!

Tom Kyte
June 23, 2004 - 8:55 am UTC

sure they do? if you have cascade=> true, and the granularity set to default, both global and local stats would be gathered on indexes.

if there were a single best way, there would be a single option "analyze;"

if you just analyze newly added partitions, you will not be maintaining global stats, which are relevant in most cases. You will need to decide if you need both global and local. You want global stats in cases where

a) more than one partition could be accessed (eg: you see KEY KEY in the explain plans)
b) the partition is not "known" at parse time (eg: bind variables for the constraint against the partition key)

import/export:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4347359891525#9562846651724 <code>

Analyze Partitioned Table

Wayne, June 23, 2004 - 12:10 pm UTC

Very helpful!
I will spend more time studying the details of the DBMS_STATS package. It is a newer but important package but it was not coverage in your "Expert One-on-One" book and I didn't study the whole long parameter lists of some of its sub-programs.




Tom Kyte
June 23, 2004 - 1:06 pm UTC

Yah, i started on dbms_stats in chapter 6 of that book -- but I had to take it out as to do it justice was going to require a book itself

DBMS_STATS

Wayne, June 23, 2004 - 3:52 pm UTC

Tom,
Any plans to write a book teaching us on how to best use the DBMS_STATS package? I think this is one of the most important packages if not the most important one. So far, Oracle documentation just tells us how to call its subprograms, but it didn't tell us how to judge on the best parameters we should provide.
So if we don't have stats, CBO is confused;
If we have wrong or inaccurate stats, CBO is misled;
If we over-anylze, we waste resources (Maybe that's why 9i provides the new MONITOR option on index?);



Index selection question

A reader, June 05, 2006 - 11:26 am UTC

Hi Tom.

I have a query optimization question that's been puzzling me for a couple of day:

Here is the DDL for the table and index:
PROMPT Creating Table 'GDS_MESSAGE'
CREATE TABLE GDS_MESSAGE
 (BEP_TIME DATE NOT NULL
 ,REGION VARCHAR2(8) NOT NULL
 ,EVT_NUM NUMBER NOT NULL
 ,LD_FILE_ID VARCHAR2(30) NOT NULL
 ,MSG_REC_TYPE NUMBER NOT NULL
 ,MSG_CATEGORY VARCHAR2(1) NOT NULL
 ,FILE_OFFSET NUMBER NOT NULL
 ,AP_TIME_T NUMBER NOT NULL
 ,BEP_SRC_ID NUMBER NOT NULL
 ,LABEL VARCHAR2(2)
 ,TECH_ACK VARCHAR(1)
 ,BLK_ID VARCHAR2(240)
 ,RF_MODE VARCHAR2(240)
 ,MSN VARCHAR2(240)
 ,SSV NUMBER
 ,DL_FLAG NUMBER(1)
 ,UL_DELIV_METHOD NUMBER
 ,UL_SCORE_KEY NUMBER DEFAULT -1
 ,AGENCY VARCHAR2(2)
 ,TAIL VARCHAR2(7)
 ,FLT_NUM VARCHAR2(5)
 ,SMI VARCHAR2(3)
 ,MSG_LENGTH NUMBER
 ,MSG_RECIPIENTS VARCHAR2(400)
 ,MSG_DATE_DDHHMM VARCHAR2(6)
 ,IN_MSG_CORRELATION_NUM NUMBER
 ,DL_MSG_CORRELATION_NUM NUMBER
 ,MEDIA_TYPE NUMBER
 ,PHASE_OF_FLIGHT VARCHAR2(1)
 ,AGENCY_NUM NUMBER
 ,STN_NAME VARCHAR2(10)
 ,IN_ORG_ADDR VARCHAR2(7)
 ,IN_ORG_TIME_T NUMBER
 ,IN_TARGET_STATION VARCHAR2(10)
 ,IN_TARGET_METHOD VARCHAR2(1)
 ,DISCONNECT_TIME_T NUMBER
 ,CONNECT_TIME_T NUMBER
 ,ICP_REASON_CODE NUMBER
 ,ICP_UL_ADDRESSEE VARCHAR2(7)
 ,ICP_UL_NUM_MEDIA NUMBER
 ,ICP_UL_BLK_NUM NUMBER
 ,ICP_UL_CODE NUMBER
 ,MSG_TEXT1 VARCHAR2(2500)
 ,MSG_TEXT2 VARCHAR2(4000)
 )
 PARALLEL
 NOLOGGING
 PARTITION BY RANGE (BEP_TIME)
 SUBPARTITION BY LIST (REGION)
 SUBPARTITION TEMPLATE (
  SUBPARTITION CHINA VALUES ('CHINA')
  ,SUBPARTITION THAILAND VALUES ('THAILAND')
  ,SUBPARTITION US VALUES ('US')
  )
 (
   PARTITION Part_20060529 VALUES LESS THAN (TO_DATE( '2006/06/01', 'YYYY/MM/DD'))
       TABLESPACE data
 )
 TABLESPACE DATA
/

alter table gds_message
add
(CONSTRAINT GDS_MSG_PK PRIMARY KEY (BEP_TIME, REGION, EVT_NUM) deferrable USING INDEX LOCAL nologging ENABLE)
/

alter index GDS_MSG_PK parallel
/

PROMPT Creating Index 'GDS_MSG_STN_I'
CREATE INDEX GDS_MSG_STN_I ON GDS_MESSAGE
 (STN_NAME) LOCAL PARALLEL nologging
/

After populating the table with data and analyzing the table and indexes, the following query insists on doing an index range scan (using GDS_MSG_PK) instead of using GDS_MSG_STN_I which is faster.

Take a look a the autotrace below:

SQL> set autotrace traceonly explain
SQL> SELECT  NVL(a.msn, ' '), a.tech_ack, a.ssv, a.blk_id,
  2   DECODE(a.dl_flag, 1, 'N', 2, 'C', 3, 'D', '-'),
  3   c.success_failure_flag, a.msg_length, a.msg_text1
  4   FROM gds_message a, gds_ul_score_reason c
  5   WHERE a.bep_time between to_date ('29-MAY-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
  6   AND to_date ('29-MAY-2006 23:59:59','DD-MON-YYYY HH24:MI:SS')
  7   AND a.region = 'US'
  8   AND a.ul_score_key=c.ul_score_key
  9   AND a.stn_name='DFW'
 10  ORDER BY a.evt_num;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=139
          )

   1    0   SORT (ORDER BY) (Cost=6 Card=1 Bytes=139)
   2    1     NESTED LOOPS (Cost=5 Card=1 Bytes=139)
   3    2       PARTITION RANGE (SINGLE) (Cost=4 Card=1 Bytes=133)
   4    3         PARTITION LIST (SINGLE) (Cost=4 Card=1 Bytes=133)
   5    4           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'GDS_MESSAG
          E' (TABLE) (Cost=4 Card=1 Bytes=133)

   6    5             INDEX (RANGE SCAN) OF 'GDS_MSG_PK' (INDEX) (Cost
          =3 Card=1)

   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'GDS_UL_SCORE_REASON'
           (TABLE) (Cost=1 Card=1 Bytes=6)

   8    7         INDEX (UNIQUE SCAN) OF 'GDS_USR_PK' (INDEX (UNIQUE))
           (Cost=0 Card=1)




SQL> SELECT /*+ no_index(a gds_msg_pk) */ NVL(a.msn, ' '), a.tech_ack, a.ssv, a.blk_id,
  2   DECODE(a.dl_flag, 1, 'N', 2, 'C', 3, 'D', '-'),
  3   c.success_failure_flag, a.msg_length, a.msg_text1
  4   FROM gds_message a, gds_ul_score_reason c
  5   WHERE a.bep_time between to_date ('29-MAY-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
  6   AND to_date ('29-MAY-2006 23:59:59','DD-MON-YYYY HH24:MI:SS')
  7   AND a.region = 'US'
  8   AND a.ul_score_key=c.ul_score_key
  9   AND a.stn_name='DFW'
 10  ORDER BY a.evt_num;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7060 Card=1 Bytes=
          139)

   1    0   SORT (ORDER BY) (Cost=7060 Card=1 Bytes=139)
   2    1     NESTED LOOPS (Cost=7059 Card=1 Bytes=139)
   3    2       PARTITION RANGE (SINGLE) (Cost=7058 Card=1 Bytes=133)
   4    3         PARTITION LIST (SINGLE) (Cost=7058 Card=1 Bytes=133)
   5    4           TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'GDS_MESSAG
          E' (TABLE) (Cost=7058 Card=1 Bytes=133)

   6    5             INDEX (SKIP SCAN) OF 'GDS_MSG_STN_I' (INDEX) (Co
          st=22 Card=542)

   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'GDS_UL_SCORE_REASON'
           (TABLE) (Cost=1 Card=1 Bytes=6)

   8    7         INDEX (UNIQUE SCAN) OF 'GDS_USR_PK' (INDEX (UNIQUE))
           (Cost=0 Card=1)


I have ran both queries through tkprof and the outcode of the plan is the same.

When the index GDS_MSG_PK is scanned, the cost is "2" whereas when the index GDS_MSG_STN_I is scanned, the cost is "22", but the primary key scan returns in 5 minutes, whereas the index scan return in 30 seconds.

I really don't like using the above hint to solve this problem.  Can you help me understand why this query is insisting on the primary key scan.

Thanks,
Yves 

Tom Kyte
June 05, 2006 - 12:16 pm UTC

one must presume it is getting the estimated cardinality wrong - you would be able to determine that by comparing the explain plan to the out come from the tkprof (compare the guess - the plan - to reality - the tkprof and see where it differs and then we can start asking "why" it differs)

List Partition and stats

Jonathan Lewis, June 05, 2006 - 12:39 pm UTC

By an odd coincidence, someone sent me an email this morning about a bug that is present up to 10.1.0.5 and 10.2.0.2 where elimination down to a single list subpartition results in the partition-level stats being used in the calculation of selectivity rather than the subpartition stats - it's just possible that your problem is an example of the same thing. The bug is not yet published though.

As a side observation - the columns of your primary key seem to be the wrong way round if this query is typical. The region name is frequently repeated, appears in the predicate list with an equality; the date column appears with a range predicate. This suggests that the region name should be the first column and the date colum the second column. The swap won't, by itself, make much difference to the resource usage given your subpartition definition, but it does mean you could then compress the index on the first column - which could give you a performance edge.


Note for Wayne: dbms_stats is one of the big three topics in volume 2 of Cost Based Oracle. As Tom says, it's a very big topic.





tkprof output

A reader, June 05, 2006 - 2:03 pm UTC

Tom, here is the TKPROF output from both queries:
SELECT /*+ index(a gds_msg_stn_i) */ TO_CHAR(a.bep_time,'HH24MISS'),
a.tail, a.agency||a.flt_num, a.label,
NVL(a.msn, ' '), a.tech_ack, a.ssv, a.blk_id,
DECODE(a.dl_flag, 1, 'N', 2, 'C', 3, 'D', '-'),
c.success_failure_flag, a.msg_length, a.msg_text1
FROM gds_message a, gds_ul_score_reason c
WHERE a.bep_time between to_date ('29-MAY-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
AND to_date ('29-MAY-2006 23:59:59','DD-MON-YYYY HH24:MI:SS')
AND a.region = 'US'
AND a.ul_score_key=c.ul_score_key
AND a.stn_name='DFW'
ORDER BY a.evt_num

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1146 0.96 0.92 4714 33751 0 17173
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1148 0.96 0.92 4714 33751 0 17173

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67

Rows Row Source Operation
------- ---------------------------------------------------
17173 SORT ORDER BY (cr=33751 pr=4714 pw=0 time=857387 us)
17173 NESTED LOOPS (cr=33751 pr=4714 pw=0 time=997016 us)
17173 PARTITION RANGE SINGLE PARTITION: 88 88 (cr=16576 pr=4714 pw=0 time=601997 us)
17173 PARTITION LIST SINGLE PARTITION: 1 1 (cr=16576 pr=4714 pw=0 time=550456 us)
17173 TABLE ACCESS BY LOCAL INDEX ROWID GDS_MESSAGE PARTITION: 90 90 (cr=16576 pr=4714 pw=0 time=516102 us)
17173 INDEX RANGE SCAN GDS_MSG_STN_I PARTITION: 90 90 (cr=39 pr=39 pw=0 time=35458 us)(object id 60747)
17173 TABLE ACCESS BY INDEX ROWID GDS_UL_SCORE_REASON (cr=17175 pr=0 pw=0 time=297140 us)
17173 INDEX UNIQUE SCAN GDS_USR_PK (cr=2 pr=0 pw=0 time=120796 us)(object id 52813)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1146 0.00 0.00
db file sequential read 4714 0.00 0.07
SQL*Net message from client 1146 0.01 3.90
SQL*Net more data to client 1134 0.00 0.01
********************************************************************************

SELECT TO_CHAR(a.bep_time,'HH24MISS'),
a.tail, a.agency||a.flt_num, a.label,
NVL(a.msn, ' '), a.tech_ack, a.ssv, a.blk_id,
DECODE(a.dl_flag, 1, 'N', 2, 'C', 3, 'D', '-'),
c.success_failure_flag, a.msg_length, a.msg_text1
FROM gds_message a, gds_ul_score_reason c
WHERE a.bep_time between to_date ('29-MAY-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
AND to_date ('29-MAY-2006 23:59:59','DD-MON-YYYY HH24:MI:SS')
AND a.region = 'US'
AND a.ul_score_key=c.ul_score_key
AND a.stn_name='DFW'
ORDER BY a.evt_num

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1146 33.70 33.47 291240 2749682 0 17173
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1148 33.70 33.47 291240 2749682 0 17173

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 67

Rows Row Source Operation
------- ---------------------------------------------------
17173 SORT ORDER BY (cr=2749682 pr=291240 pw=0 time=33409644 us)
17173 NESTED LOOPS (cr=2749682 pr=291240 pw=0 time=28248366 us)
17173 PARTITION RANGE SINGLE PARTITION: 88 88 (cr=2732507 pr=291240 pw=0 time=27836208 us)
17173 PARTITION LIST SINGLE PARTITION: 1 1 (cr=2732507 pr=291240 pw=0 time=27784680 us)
17173 TABLE ACCESS BY LOCAL INDEX ROWID GDS_MESSAGE PARTITION: 90 90 (cr=2732507 pr=291240 pw=0 time=27733156 us)
8189785 INDEX RANGE SCAN GDS_MSG_PK PARTITION: 90 90 (cr=31846 pr=31846 pw=0 time=16499158 us)(object id 61752)
17173 TABLE ACCESS BY INDEX ROWID GDS_UL_SCORE_REASON (cr=17175 pr=0 pw=0 time=323037 us)
17173 INDEX UNIQUE SCAN GDS_USR_PK (cr=2 pr=0 pw=0 time=130113 us)(object id 52813)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1146 0.00 0.00
db file sequential read 291240 0.00 3.67
latch: object queue header operation 1 0.00 0.00
SQL*Net message from client 1146 0.02 4.00
SQL*Net more data to client 1134 0.00 0.01
********************************************************************************


Tom Kyte
June 05, 2006 - 2:40 pm UTC

ok, so do the comparision for us - what is different.

(please try to work through it a bit yourself??)

see, I don't have your tables and don't know your indexes and what columns they are.

You do, I was sort of hoping you would compare them and tell us about it.

(you know these plans do not "match up", did you notice that)

TKPROF Output

A reader, June 05, 2006 - 2:32 pm UTC

Tom, the cardinality is 31846 using the primary key and 39 using the index GDS_MSG_STN_I.

Using the primary key, there are 8,189,785 records scanned, versus 17,173 using the index.

Please advise if the plan selection is a bug.

Thanks.

To Johnathan Lewis: The primary key order is for a range-list partition. I won't be able to reverse the order because it has to match the partition order. If not, Oracle will throw an error about the order.

Tom, please advise.

Thanks again.

Tom Kyte
June 05, 2006 - 2:44 pm UTC

Please provide a tad more detail on the 31846 using the primary key? Where do you see 8,189,785.

TKPROF output

A reader, June 05, 2006 - 2:50 pm UTC

Here is the main difference:
One plan has:
8189785 INDEX RANGE SCAN GDS_MSG_PK PARTITION: 90 90 (cr=31846 pr=31846
pw=0 time=16499158 us)(object id 61752)


It is scanning 8189785 though index GDS_MSG_PK.

Whereas the other plan has:
17173 INDEX RANGE SCAN GDS_MSG_STN_I PARTITION: 90 90 (cr=39 pr=39 pw=0
time=35458 us)(object id 60747)

Only 17173 rows are scanned through index GDS_MSG_STN_I. This is the better plan.

The rest of the two plans are pretty much equal except for the above differences.

Thanks.

Tom Kyte
June 05, 2006 - 3:14 pm UTC

autotrace traceonly explain these for us:

select * from gds_message a
WHERE a.bep_time between
to_date ('29-MAY-2006 00:00:00','DD-MON-YYYY HH24:MI:SS')
AND to_date ('29-MAY-2006 23:59:59','DD-MON-YYYY HH24:MI:SS');

select * from gds_message a
where a.stn_name='DFW';





PK ordering

Jonathan Lewis, June 05, 2006 - 3:09 pm UTC

Which version of Oracle ?

As far as I know, there is no restriction on the order of columns of a composite partitioned PK index - even when declared as local. For example - from a 9.2.0.6 system:

========================
drop table pt_rl;

create table pt_rl (
state_id varchar2(2) not null,
date_loaded date,
spare number(6),
padding varchar2(100)
)
partition by range (date_loaded)
subpartition by list (state_id)
subpartition template (
subpartition ca values('CA'),
subpartition TX values('TX'),
subpartition the_rest values(default)
)
(
partition p_2002_jan values less than (
to_date('01-feb-2002','dd-mon-yyyy')
),
partition p_2002_feb values less than (maxvalue)
)
;

alter table pt_rl add constraint pt_pk primary key (date_loaded, state_id, spare)
using index local;

select index_name, locality from user_part_indexes;

alter table pt_rl drop primary key;

alter table pt_rl add constraint pt_pk primary key (state_id, date_loaded, spare)
using index local;

select index_name, locality from user_part_indexes;

=====================

Results:

Table dropped.

Table created.

Table altered.

INDEX_NAME LOCALI
-------------------- ------
PT_PK LOCAL

1 row selected.

Table altered.

Table altered.

INDEX_NAME LOCALI
-------------------- ------
PT_PK LOCAL

1 row selected.
==================================



Partition statistics

Jonathan Lewis, June 05, 2006 - 3:20 pm UTC

Comparing the cardinalities from the explain plans with the row counts from tkprof, it looks as if your statistics are a long way off. The predictions, regardless of choice of index, are badly wrong.

I would check the column stats - num_distinct, density, low, high, and any histograms for the specific subpartition, and for the partition, to see if they look reasonable for the data you have.

I note that the 'range partition' start/stop is 90, which means your real table has far more partitions/subpartitions than your sample code - so if there is a problem of Oracle picking the wrong level of stats, there is plenty of scope for error.


TKPROF Output

A reader, June 06, 2006 - 8:14 am UTC

Jonathan, please elaborate a bit more on the first paragraph of your reply.
What are you comparing and how does it tell you that the statistics are way off.

When you say check the column stats, what view will I find them in.

I am using 10.2.0.1.

The range partition start/stop is 90. I did not want to put on Tom's website 90 days
of partition DDL. I do not aggree that there is plenty of scope for error. With
partition pruning, the scope is narrowed to a specific partition.

Please advise.

Thanks.

Tom Kyte
June 06, 2006 - 8:51 am UTC

can you post what I asked for - that'll help us tell you why we believe your statistics are way off.

The estimated cardinalities will be far from reality.


user_tab_columns
user_tab_histograms

Histogram request

A reader, June 06, 2006 - 10:02 am UTC

SQL> SELECT SUBSTR(column_name,1,30), NUM_DISTINCT, DENSITY, high_value, low_value from user_tab_columns where table_name = 'GDS_MESSAGE';

SUBSTR(COLUMN_NAME,1,30)       NUM_DISTINCT    DENSITY HIGH_VALUE                                                       LOW_VALUE
------------------------------ ------------ ---------- ---------------------------------------------------------------- ----------------------------------------------------------------
BEP_TIME                            1064137 9.39728625 786A0604183C3C                                                   786A0515010102
REGION                                    1 3.71460513 5553                                                             5553
EVT_NUM                             9324053 1.07249497 C40A484D3A                                                       C20660
LD_FILE_ID                              735 0.00136054 323030363036303432333A34323A34322D55532D3435                     323030363035323130303A30303A30302D55532D31
MSG_REC_TYPE                              6 3.71460513 C11E                                                             C119
MSG_CATEGORY                              3 0.33333333 4F                                                               2D
FILE_OFFSET                        19314468 5.17746592 C41A3B4046                                                       C3022D25
AP_TIME_T                           1065338 9.38669229 C50C322F3830                                                     C50C31120161
BEP_SRC_ID                                2        0.5 C121                                                             C120
LABEL                                   101 0.00990099 5F7F                                                             3130
TECH_ACK                                 38 0.02631578 5A                                                               06
BLK_ID                                   37 0.02702702 5A                                                               06
RF_MODE                                   2        0.5 52                                                               32
MSN                                     701 0.00142653 53393941                                                         30303031
SSV                                      64   0.015625 C15B                                                             3E6466
DL_FLAG                                   4       0.25 C104                                                             80
UL_DELIV_METHOD                           8      0.125 C10E                                                             80
UL_SCORE_KEY                             12 3.71460513 C111                                                             3E6366
AGENCY                                  106 3.71460513 BF00                                                             0000
TAIL                                   8370 0.00011947 5A53455341                                                       20202020202020

SUBSTR(COLUMN_NAME,1,30)       NUM_DISTINCT    DENSITY HIGH_VALUE                                                       LOW_VALUE
------------------------------ ------------ ---------- ---------------------------------------------------------------- ----------------------------------------------------------------
FLT_NUM                                2996 0.00055803 52434839                                                         00000000
SMI                                     132 0.00757575 575852                                                           413830
MSG_LENGTH                              322 0.00310559 C2205B                                                           80
MSG_RECIPIENTS                          355 0.00281690 525552444C5841205A52484F433852                                   525552444C58412041425A44533858
MSG_DATE_DDHHMM                       20057 4.98579049 333132333535                                                     303130303035
IN_MSG_CORRELATION_NUM                 1591 0.00062853 CA055E3F563021442E440E                                           80
DL_MSG_CORRELATION_NUM                    1          1 80                                                               80
MEDIA_TYPE                                5        0.2 C104                                                             3E6466
PHASE_OF_FLIGHT                           5        0.2 55                                                               41
AGENCY_NUM                               62 0.01612903 C3073824                                                         80
STN_NAME                                988 0.00168350 5A5341                                                           41414C
IN_ORG_ADDR                             144 0.00694444 59485A4954514B                                                   414952474C5848
IN_ORG_TIME_T                           134 0.00746268 C50C322E433D                                                     80
IN_TARGET_STATION                       277 0.00361010 59595A                                                           41414C
IN_TARGET_METHOD                          3 0.33333333 32                                                               30
DISCONNECT_TIME_T                         1          1 80                                                               80
CONNECT_TIME_T                            1          1 80                                                               80
ICP_REASON_CODE                          14 3.71460513 C20A62                                                           80
ICP_UL_ADDRESSEE                       4363 0.00022920 5A532D535842                                                     303033323353
ICP_UL_NUM_MEDIA                          3 0.33333333 C104                                                             80
ICP_UL_BLK_NUM                            2        0.5 C102                                                             80

SUBSTR(COLUMN_NAME,1,30)       NUM_DISTINCT    DENSITY HIGH_VALUE                                                       LOW_VALUE
------------------------------ ------------ ---------- ---------------------------------------------------------------- ----------------------------------------------------------------
ICP_UL_CODE                               3 0.33333333 C105                                                             80
MSG_TEXT1                          20411077 4.89930051 4941485F54332C4941485F54332C3139312E3234303035312C31300A         01322E2E2E434D3032335F7F4A03
MSG_TEXT2                             10158 9.84445757 5F5F5F5F5F202020205F5F5F5F5F2020205F5F5F5F0D0A202020202020202020 0304

44 rows selected
 

range vs list partitioning

A reader, June 06, 2006 - 10:08 am UTC

Hi

I have to partition a table to hold historical data (48 monhts data). I want to partition by year. My question is can I use list partition instead of range partition? Advantage of one over another?

Cheers

Tom Kyte
June 06, 2006 - 11:19 am UTC

you can use list if you have the year in a separate field.

But that would be a horrible idea - you store DATES in DATE types - therefore, it doesn't make sense to use a list partition, since you'll have a nice DATE or TIMESTAMP field - the only thing that would make sense would be a range partition

Because we all know much better than to stick a date in a number or a string don't we...

HISTOGRAM

A reader, June 06, 2006 - 10:10 am UTC

SQL> SELECT SUBSTR(COLUMN_NAME,1,30), ENDPOINT_NUMBER, ENDPOINT_VALUE, SUBSTR(ENDPOINT_ACTUAL_VALUE,1,30) from user_tab_histograms where table_name = 'GDS_MESSAGE';

SUBSTR(COLUMN_NAME,1,30)       ENDPOINT_NUMBER ENDPOINT_VALUE SUBSTR(ENDPOINT_ACTUAL_VALUE,1
------------------------------ --------------- -------------- ------------------------------
REGION                                    4260 4.430286729725 
MSG_REC_TYPE                               770             24 
MSG_REC_TYPE                              3552             25 
MSG_REC_TYPE                              3686             26 
MSG_REC_TYPE                              3992             27 
MSG_REC_TYPE                              3995             28 
MSG_REC_TYPE                              4260             29 
UL_SCORE_KEY                              3490             -2 
UL_SCORE_KEY                              3524              1 
UL_SCORE_KEY                              3528              4 
UL_SCORE_KEY                              3554              5 
UL_SCORE_KEY                              3911              8 
UL_SCORE_KEY                              3912              9 
UL_SCORE_KEY                              3933             10 
UL_SCORE_KEY                              3940             11 
UL_SCORE_KEY                              3993             12 
UL_SCORE_KEY                              4145             13 
UL_SCORE_KEY                              4259             15 
UL_SCORE_KEY                              4260             16 
STN_NAME                                   241 4.637236437966 
STN_NAME                                   242 4.637633961560 
STN_NAME                                   243 4.637642676657 
STN_NAME                                   244 4.638444465662 
STN_NAME                                   245 4.638444667456 
STN_NAME                                   246 4.638650458885 
STN_NAME                                   247 4.638652251480 
STN_NAME                                   248 4.639247839230 
STN_NAME                                   249 4.639251800638 
STN_NAME                                   250 4.639262892581 
STN_NAME                                   251 4.639267055771 
STN_NAME                                   252 4.639267055831 
STN_NAME                                   253 4.639267058926 
STN_NAME                                   254 4.689953070958 
STN_NAME                                     0 3.388236737693 
STN_NAME                                     1 3.388443724967 
STN_NAME                                     2 3.388652685546 
STN_NAME                                     3 3.389450513143 
STN_NAME                                     4 3.390459879933 
STN_NAME                                     5 3.390662113566 
STN_NAME                                     6 3.390866320407 
STN_NAME                                     7 3.391686536149 
STN_NAME                                     8 3.391686542339 
STN_NAME                                     9 3.392090600406 
STN_NAME                                    10 3.392298967355 
STN_NAME                                    11 3.440768178566 
STN_NAME                                    12 3.441171449914 
STN_NAME                                    13 3.441580267232 
STN_NAME                                    14 3.442388394490 
STN_NAME                                    15 3.442787704429 
STN_NAME                                    16 3.442787906235 
STN_NAME                                    17 3.443004789594 
STN_NAME                                    18 3.443004991400 
STN_NAME                                    19 3.443004997590 
STN_NAME                                    20 3.443616431009 
STN_NAME                                    21 3.444220941889 
STN_NAME                                    22 3.444619661364 
STN_NAME                                    23 3.444619667506 
STN_NAME                                    24 3.444619667941 
STN_NAME                                    25 3.492081882582 
STN_NAME                                    26 3.492687390004 
STN_NAME                                    27 3.493504820381 
STN_NAME                                    28 3.493699131149 
STN_NAME                                    29 3.494308193949 
STN_NAME                                    30 3.494308395731 
STN_NAME                                    31 3.494320078173 
STN_NAME                                    32 3.494320279979 
STN_NAME                                    33 3.494320286145 
STN_NAME                                    34 3.494320286568 
STN_NAME                                    35 3.494729687773 
STN_NAME                                    36 3.494927959998 
STN_NAME                                    37 3.495122074533 
STN_NAME                                    38 3.495539601364 
STN_NAME                                    39 3.496140945608 
STN_NAME                                    40 3.496338226827 
STN_NAME                                    41 3.496338227480 
STN_NAME                                    42 3.544005845654 
STN_NAME                                    43 3.544402778313 
STN_NAME                                    44 3.544402784527 
STN_NAME                                    45 3.544818726178 
STN_NAME                                    46 3.544818732356 
STN_NAME                                    47 3.544818732404 
STN_NAME                                    48 3.544818735451 
STN_NAME                                    49 3.544818735463 
STN_NAME                                    50 3.545028479027 
STN_NAME                                    51 3.545028680809 
STN_NAME                                    52 3.545028680833 
STN_NAME                                    53 3.545028686382 
STN_NAME                                    54 3.545028687023 
STN_NAME                                    55 3.545028689477 
STN_NAME                                    56 3.545028690106 
STN_NAME                                    57 3.545028690130 
STN_NAME                                    58 3.545028693188 
STN_NAME                                    59 3.547657471229 
STN_NAME                                    60 3.547868016372 
STN_NAME                                    61 3.547868218153 
STN_NAME                                    62 3.547868218178 
STN_NAME                                    63 3.547868218190 
STN_NAME                                    64 3.547868224343 
STN_NAME                                    65 3.547868224512 
STN_NAME                                    66 3.548271287719 
STN_NAME                                    67 3.598353786089 
STN_NAME                                    68 3.600392326710 
STN_NAME                                    69 3.600395697619 
STN_NAME                                    70 3.600395697643 
STN_NAME                                    71 3.600395703808 
STN_NAME                                    72 3.600395703821 
STN_NAME                                    73 3.647857918873 
STN_NAME                                    74 3.650082847446 
STN_NAME                                    75 3.650896318905 
STN_NAME                                    76 3.651496277914 
STN_NAME                                    77 3.700179607320 
STN_NAME                                    78 3.701606506141 
STN_NAME                                    79 3.703227312527 
STN_NAME                                    80 3.704636781539 
STN_NAME                                    81 3.748234454807 
STN_NAME                                    82 3.748435694340 
STN_NAME                                    83 3.753930167410 
STN_NAME                                    84 3.754741463794 
STN_NAME                                    85 3.755556923356 
STN_NAME                                    86 3.755556923405 
STN_NAME                                    87 3.803614349905 
STN_NAME                                    88 3.803614349941 
STN_NAME                                    89 3.803614356094 
STN_NAME                                    90 3.803614356131 
STN_NAME                                    91 3.803617317249 
STN_NAME                                    92 3.803617519031 
STN_NAME                                    93 3.803617524604 
STN_NAME                                    94 3.803617525221 
STN_NAME                                    95 3.804032472821 
STN_NAME                                    96 3.805847590024 
STN_NAME                                    97 3.806250861371 
STN_NAME                                    98 3.806251068726 
STN_NAME                                    99 3.807273696949 
STN_NAME                                   100 3.807470975074 
STN_NAME                                   101 3.855552962341 
STN_NAME                                   102 3.855553164159 
STN_NAME                                   103 3.856556783160 
STN_NAME                                   104 3.856556984954 
STN_NAME                                   105 3.856556990515 
STN_NAME                                   106 3.856556991143 
STN_NAME                                   107 3.910297245793 
STN_NAME                                   108 3.959395139873 
STN_NAME                                   109 3.959395146075 
STN_NAME                                   110 3.959395146498 
STN_NAME                                   111 3.959399101281 
STN_NAME                                   112 3.959399107471 
STN_NAME                                   113 3.959399107483 
STN_NAME                                   114 3.959399107507 
STN_NAME                                   115 3.959399110602 
STN_NAME                                   116 3.959399113673 
STN_NAME                                   117 3.959587664356 
STN_NAME                                   118 3.960210397714 
STN_NAME                                   119 3.960597621610 
STN_NAME                                   120 3.960597823404 
STN_NAME                                   121 3.960597826487 
STN_NAME                                   122 3.960597826499 
STN_NAME                                   123 3.960615051806 
STN_NAME                                   124 3.960813914494 
STN_NAME                                   125 3.961018323153 
STN_NAME                                   126 3.961824275336 
STN_NAME                                   127 3.962025514917 
STN_NAME                                   128 3.964262716408 
STN_NAME                                   129 4.011313945280 
STN_NAME                                   130 4.011715833846 
STN_NAME                                   131 4.011715840024 
STN_NAME                                   132 4.011717418446 
STN_NAME                                   133 4.011720587524 
STN_NAME                                   134 4.011720593109 
STN_NAME                                   135 4.011720594149 
STN_NAME                                   136 4.011929749873 
STN_NAME                                   137 4.012124449383 
STN_NAME                                   138 4.012124651201 
STN_NAME                                   139 4.012124657391 
STN_NAME                                   140 4.012133164481 
STN_NAME                                   141 4.012738467642 
STN_NAME                                   142 4.012926440157 
STN_NAME                                   143 4.012926440169 
STN_NAME                                   144 4.012926443264 
STN_NAME                                   145 4.013335055706 
STN_NAME                                   146 4.014144177015 
STN_NAME                                   147 4.014966363572 
STN_NAME                                   148 4.014966565378 
STN_NAME                                   149 4.014966571544 
STN_NAME                                   150 4.014966571568 
STN_NAME                                   151 4.015771321704 
STN_NAME                                   152 4.115157505606 
STN_NAME                                   153 4.116772175558 
STN_NAME                                   154 4.117179610132 
STN_NAME                                   156 4.118599969268 
STN_NAME                                   157 4.118600171049 
STN_NAME                                   158 4.118600171086 
STN_NAME                                   159 4.118600171122 
STN_NAME                                   160 4.118600174144 
STN_NAME                                   161 4.118600174156 
STN_NAME                                   162 4.118600174180 
STN_NAME                                   164 4.118600174217 
STN_NAME                                   165 4.118600177239 
STN_NAME                                   166 4.118600177287 
STN_NAME                                   167 4.118600177312 
STN_NAME                                   168 4.118600183429 
STN_NAME                                   169 4.118600183477 
STN_NAME                                   170 4.118600183501 
STN_NAME                                   171 4.118616607182 
STN_NAME                                   172 4.167277161804 
STN_NAME                                   173 4.167699246141 
STN_NAME                                   174 4.168501236915 
STN_NAME                                   175 4.168501240022 
STN_NAME                                   176 4.168501240046 
STN_NAME                                   177 4.168510744307 
STN_NAME                                   178 4.168510747402 
STN_NAME                                   179 4.168698313270 
STN_NAME                                   180 4.168710399313 
STN_NAME                                   181 4.168710402359 
STN_NAME                                   182 4.168710402407 
STN_NAME                                   183 4.169512778781 
STN_NAME                                   184 4.171334234237 
STN_NAME                                   185 4.171544188868 
STN_NAME                                   186 4.271529337679 
STN_NAME                                   187 4.271543008248 
STN_NAME                                   188 4.272961192345 
STN_NAME                                   189 4.273566495942 
STN_NAME                                   190 4.322850377094 
STN_NAME                                   191 4.322851961053 
STN_NAME                                   192 4.322851962092 
STN_NAME                                   193 4.322856712300 
STN_NAME                                   194 4.323453890828 
STN_NAME                                   195 4.323454098799 
STN_NAME                                   196 4.323652753516 
STN_NAME                                   197 4.323652960871 
STN_NAME                                   198 4.323862708146 
STN_NAME                                   199 4.323866871348 
STN_NAME                                   200 4.323866880633 
STN_NAME                                   201 4.323866883728 
STN_NAME                                   202 4.323866886194 
STN_NAME                                   203 4.323866886835 
STN_NAME                                   204 4.324062363116 
STN_NAME                                   205 4.324062564934 
STN_NAME                                   206 4.324278065536 
STN_NAME                                   207 4.324668666954 
STN_NAME                                   208 4.324682926983 
STN_NAME                                   209 4.325074311616 
STN_NAME                                   210 4.325279307692 
STN_NAME                                   211 4.325478170380 
STN_NAME                                   212 4.325898079641 
STN_NAME                                   213 4.326704031872 
STN_NAME                                   214 4.326704034919 
STN_NAME                                   215 4.326704034967 
STN_NAME                                   216 4.327102549530 
STN_NAME                                   217 4.327722905994 
STN_NAME                                   218 4.374773933096 
STN_NAME                                   219 4.376808512310 
STN_NAME                                   220 4.377612678159 
STN_NAME                                   221 4.377806787157 
STN_NAME                                   222 4.377812534947 
STN_NAME                                   223 4.378218975420 
STN_NAME                                   224 4.378829824553 
STN_NAME                                   225 4.379645672809 
STN_NAME                                   226 4.429340745465 
STN_NAME                                   227 4.532981683464 
STN_NAME                                   228 4.533992433048 
STN_NAME                                   230 4.587122838830 
STN_NAME                                   232 4.587123631112 
STN_NAME                                   235 4.587124423394 
STN_NAME                                   236 4.634388977793 
STN_NAME                                   237 4.635195318717 
STN_NAME                                   238 4.635808544695 
STN_NAME                                   239 4.635819046150 
STN_NAME                                   240 4.636831582043 
ICP_REASON_CODE                           3992              0 
ICP_REASON_CODE                           4002            115 
ICP_REASON_CODE                           4004            231 
ICP_REASON_CODE                           4005            311 
ICP_REASON_CODE                           4007            985 
ICP_REASON_CODE                           4014            986 
ICP_REASON_CODE                           4016            987 
ICP_REASON_CODE                           4032            988 
ICP_REASON_CODE                           4040            989 
ICP_REASON_CODE                           4042            990 
ICP_REASON_CODE                           4046            991 
ICP_REASON_CODE                           4049            992 
ICP_REASON_CODE                           4054            995 
ICP_REASON_CODE                           4260            997 
BEP_TIME                                     0 2453877.000011 
EVT_NUM                                      0            595 
LD_FILE_ID                                   0 2.605922183099 
MSG_CATEGORY                                 0 2.336533586340 
FILE_OFFSET                                  0          14436 
AP_TIME_T                                    0     1148170096 
BEP_SRC_ID                                   0             31 
LABEL                                        0 2.553961017291 
TECH_ACK                                     0 3.115378115120 
BLK_ID                                       0 3.115378115120 
RF_MODE                                      0 2.596148429267 
MSN                                          0 2.502076229872 
SSV                                          0             -1 
DL_FLAG                                      0              0 
UL_DELIV_METHOD                              0              0 
TAIL                                         0 1.668050818239 
SMI                                          0 3.386389136943 
MSG_LENGTH                                   0              0 

SUBSTR(COLUMN_NAME,1,30)       ENDPOINT_NUMBER ENDPOINT_VALUE SUBSTR(ENDPOINT_ACTUAL_VALUE,1
------------------------------ --------------- -------------- ------------------------------
MSG_RECIPIENTS                               0 4.274988650627 
MSG_DATE_DDHHMM                              0 2.502279051456 
IN_MSG_CORRELATION_NUM                       0              0 
DL_MSG_CORRELATION_NUM                       0              0 
MEDIA_TYPE                                   0             -1 
PHASE_OF_FLIGHT                              0 3.374992958047 
AGENCY_NUM                                   0              0 
IN_ORG_ADDR                                  0 3.389864304808 
IN_ORG_TIME_T                                0              0 
IN_TARGET_STATION                            0 3.388236737693 
IN_TARGET_METHOD                             0 2.492302492096 
DISCONNECT_TIME_T                            0              0 
CONNECT_TIME_T                               0              0 
ICP_UL_ADDRESSEE                             0 2.502078610432 
ICP_UL_NUM_MEDIA                             0              0 
ICP_UL_BLK_NUM                               0              0 
ICP_UL_CODE                                  0              0 
MSG_TEXT1                                    0 6.210076126431 
MSG_TEXT2                                    0 1.565802021401 
BEP_TIME                                     1 2453891.999988 
EVT_NUM                                      1        9717657 

SUBSTR(COLUMN_NAME,1,30)       ENDPOINT_NUMBER ENDPOINT_VALUE SUBSTR(ENDPOINT_ACTUAL_VALUE,1
------------------------------ --------------- -------------- ------------------------------
LD_FILE_ID                                   1 2.605922183099 
MSG_CATEGORY                                 1 4.101914518242 
FILE_OFFSET                                  1       25586369 
AP_TIME_T                                    1     1149465547 
BEP_SRC_ID                                   1             32 
LABEL                                        1 4.958440675804 
TECH_ACK                                     1 4.673067172681 
BLK_ID                                       1 4.673067172681 
RF_MODE                                      1 4.257683423998 
MSN                                          1 4.321212727275 
SSV                                          1             90 
DL_FLAG                                      1              3 
UL_DELIV_METHOD                              1             13 
TAIL                                         1 4.689956497742 
SMI                                          1 4.535211754469 
MSG_LENGTH                                   1           3190 
MSG_RECIPIENTS                               1 4.274988650627 
MSG_DATE_DDHHMM                              1 2.658049551120 
IN_MSG_CORRELATION_NUM                       1 4.936285473267 
DL_MSG_CORRELATION_NUM                       1              0 
MEDIA_TYPE                                   1              3 

SUBSTR(COLUMN_NAME,1,30)       ENDPOINT_NUMBER ENDPOINT_VALUE SUBSTR(ENDPOINT_ACTUAL_VALUE,1
------------------------------ --------------- -------------- ------------------------------
PHASE_OF_FLIGHT                              1 4.413452329754 
AGENCY_NUM                                   1          65535 
IN_ORG_ADDR                                  1 4.635819071300 
IN_ORG_TIME_T                                1     1149456660 
IN_TARGET_STATION                            1 4.639266853989 
IN_TARGET_METHOD                             1 2.596148429267 
DISCONNECT_TIME_T                            1              0 
CONNECT_TIME_T                               1              0 
ICP_UL_ADDRESSEE                             1 4.689937483265 
ICP_UL_NUM_MEDIA                             1              3 
ICP_UL_BLK_NUM                               1              1 
ICP_UL_CODE                                  1              4 
MSG_TEXT1                                    1 3.803617612278 
MSG_TEXT2                                    1 4.952025866646 
 

Clarification

Jonathan Lewis, June 06, 2006 - 11:09 am UTC

I am comparing the cardinalities from the explain plans, e.g.

INDEX (SKIP SCAN) OF 'GDS_MSG_STN_I' (INDEX) (Cost=22 Card=542)
INDEX (RANGE SCAN) OF 'GDS_MSG_PK' (INDEX) (Cost=3 Card=1)

with the row counts from the tkprof outputs, e.g.
Rows Row Source Operation
------- ----------------------------------------------------
17173 INDEX RANGE SCAN GDS_MSG_STN_I PARTITION: 90 90
8189785 INDEX RANGE SCAN GDS_MSG_PK PARTITION: 90 90


card = 542 is not a good estimate for 17,173 rows.
card = 1 is a very poor estimate for 8,189,785 rows.

Something is wrong with the statistics - or possibly (as I pointed out in my original post) Oracle is using the statistics from the wrong level (i.e. partition or table level instead of subpartition level) and therefore using values for "num_distinct" or "density", or low/high that are totally inappropriate for the specific partition you are querying.

In passing - have you given us the correct definition for the index gds_msg_stn_I ? The path shows a skip scan, but that doesn't make sense given that your code extract defined the index to be a single column index.





Tom Kyte
June 06, 2006 - 11:24 am UTC

I pointed that out before as well - that the plans we are asked to compare are not the same...


range vs list partitioning

A reader, June 06, 2006 - 12:35 pm UTC

Hi

I will have a seperate column for YEAR, it uses number datatype.

If I use list partitioning instead of range will queries such as between 2004 and 2006 uses partition elimination?

Tom Kyte
June 06, 2006 - 1:50 pm UTC

ugh, hate it.  more than hate it.  guess the sarcasm above didn't come through....


Yes, it will, see the pstart/pstops from dbms_xplan:

ops$tkyte@ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  PARTITION BY list (x)
  8  (
  9    PARTITION part1 VALUES ( 2002 ),
 10    partition part2 values ( 2003 ),
 11    partition part3 values ( 2004 )
 12  )
 13  /
 
Table created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select * from t where x between 2003 and 2004;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3439700702
 
----------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Pstart| Pstop |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |     1 |    36 |       |       |
|   1 |  PARTITION LIST ITERATOR|      |     1 |    36 |     2 |     3 |
|   2 |   TABLE ACCESS FULL     | T    |     1 |    36 |     2 |     3 |
-----------------------------------------------------------------------
 
Note
-----
   - dynamic sampling used for this statement
 

Partition Pruning

Paresh, July 05, 2006 - 10:11 am UTC

Tom,

I have a question regarding partition pruning. We have a global temporary table (table starting with TR_), a partitioned table (hh_advance) and a normal table (HH_ACTUAL_ADVANCES). HH_ADVANCE is partitioned on settlement_date (range partition) and each partition contains data for one day. Our system has data for around 16 months and the big processes work on one settlement_date at a time. Hence the decision on daily partition.

We have a query that takes 7 minutes from sqlplus, however, when it runs from the package, it takes around an hour. I have had a look at the runtime execution plans for both the instances and they show up different plans. I have listed below the execution plan when it ran from sql*plus, however, I do not have the execution plan when ran from within the package. The difference was that it was doing PARTITION RANGE ALL (TABLE ACCESS FULL) of HH_ADVANCE table. In order to get the exact execution plan when run from the package, I will have to wait till the project team kicks off the process again. Could you explain what could be the reasons for the difference and how should I tune it. Please let me know if you need more details.

We are using 10.2.0.1 db.

HH_ADVANCE table has been analyzed with 10% sample using dbms_stats.gather_table_stats (global).
HH_ACTUAL_ADVANCES - analyzed using dbms_stats.gather_table_stats with 10% sample data.

Table data volume
~~~~~~~~~~~~~~~~~
HH_ADVANCE = 1 billion records evenly spread across 500 partitions (500 different settlement_dates).
TR_VALID_WORK_4WEEK_DATES_T = 273012 records
HH_ACTUAL_ADVANCE = Around 200 million records

Indexes
~~~~~~~

HH_ADVANCE
~~~~~~~~~~
1. An index on mpan_setdt_uk_id, meter_period_number

HH_ACTUAL_ADVANCES
~~~~~~~~~~~~~~~~~~
1. An index on mpan, meter_register_id, meter_id_serial_number, settlement_date
2. An index on mpan_setdt_uk_id

Query
~~~~~
SELECT CASE
WHEN INSTR(REPLACE (stragg(profile2day_and_4week)
over(PARTITION BY mpan_setdt_uk_id),'T','U'),
'U') > 0 THEN
'U'
ELSE
'V'
END bsc_validation_status,
profile2day_and_4week period_validation_status,
ratio_per_cons,
comp_per_rt_tol, -- consumption period ratio tolerance
SUM(CASE
WHEN profile2day_and_4week = 'U' THEN
meter_period_consumption
ELSE
0
END) over(PARTITION BY mpan_setdt_uk_id) sum_out_tol,
meter_period_consumption,
mpan,
meter_id_serial_no,
meter_register_id,
settlement_date,
tot_cons,
period_tol,
mpan_setdt_uk_id,
meter_period_number,
prev_period_consumption,
hh_advance_id,
hh_advance_header_id,
hh_advance_header_id_det,
four_week_avg,
two_working_avg
FROM (SELECT CASE
WHEN (meter_period_consumption >=
(four_week_avg * 10 / 100) AND
meter_period_consumption <=
(four_week_avg * 200 / 100))
AND four_week_count >= 2 THEN
'V'
WHEN (meter_period_consumption >=
(two_working_avg * 10 / 100) AND
meter_period_consumption <=
(two_working_avg * 200 / 100) AND
two_working_count = 2) THEN
'V'
WHEN (four_week_count < 2 AND two_working_count < 2) THEN
'T'
ELSE
'U' -- go for yearly validation.
END profile2day_and_4week,
meter_period_consumption / tot_cons ratio_per_cons,
CASE
WHEN meter_period_consumption / tot_cons > period_tol THEN
'Y'
ELSE
'N'
END comp_per_rt_tol,
meter_period_consumption,
mpan,
meter_id_serial_no,
meter_register_id,
settlement_date,
tot_cons,
period_tol,
mpan_setdt_uk_id,
meter_period_number,
--Hh_Advance_Header_Id,
hh_advance_id,
hh_advance_header_id,
hh_advance_header_id_det,
prev_period_consumption,
four_week_avg,
two_working_avg
FROM (SELECT AVG(CASE
WHEN hah.prev_date_type = 'D' THEN
hah.meter_period_consumption
ELSE
NULL
END) two_working_avg,
AVG(CASE
WHEN hah.prev_date_type = 'W' THEN
hah.meter_period_consumption
ELSE
NULL
END) four_week_avg,
COUNT(CASE
WHEN hah.prev_date_type = 'D' THEN
hah.meter_period_consumption
ELSE
NULL
END) two_working_count,
COUNT(CASE
WHEN hah.prev_date_type = 'W' THEN
hah.meter_period_consumption
ELSE
NULL
END) four_week_count,
tvd.meter_period_consumption,
lag(tvd.meter_period_consumption,
1,
NULL) over(PARTITION BY tvd.mpan_setdt_uk_id ORDER
BY tvd.meter_period_number) prev_period_consumption,
tvd.mpan,
tvd.meter_id_serial_no,
tvd.meter_register_id,
tvd.settlement_date,
tvd.tot_cons,
tvd.mpan_setdt_uk_id,
tvd.meter_period_number,
tvd.hh_advance_id,
tvd.hh_advance_header_id,
tvd.period_tol,
tvd.hh_advance_header_id_det
FROM tr_valid_for_profile tvd,
(SELECT x.meter_id_serial_no,
x.meter_register_id,
x.settlement_date,
x.mpan_setdt_uk_id_profile,
x.mpan_setdt_uk_id,
ha.meter_period_number,
ha.meter_period_consumption,
x.mpan,
x.prev_date_type
FROM (SELECT /*+ index(haa) */ haa.meter_id_serial_no,
haa.meter_register_id,
haa.settlement_date,
tvpd.mpan_setdt_uk_id mpan_setdt_uk_id_profile,
haa.mpan_setdt_uk_id,
haa.mpan,
tvpd.prev_date_type FROM hh_actual_advances haa,
tr_valid_work_4week_dates tvpd
WHERE haa.settlement_date = tvpd.valid_prev_date
AND haa.meter_id_serial_no =tvpd.meter_id_serial_no
AND haa.mpan = tvpd.mpan
AND haa.meter_register_id =tvpd.meter_register_id
) x,
hh_advance ha
WHERE x.mpan_setdt_uk_id = ha.mpan_setdt_uk_id
AND ha.settlement_date = x.settlement_date) hah
WHERE tvd.mpan = hah.mpan(+)
AND tvd.meter_id_serial_no = hah.meter_id_serial_no(+)
AND tvd.meter_register_id = hah.meter_register_id(+)
AND tvd.mpan_setdt_uk_id =
hah.mpan_setdt_uk_id_profile(+)
AND tvd.meter_period_number =
hah.meter_period_number(+)
GROUP BY tvd.meter_period_consumption,
tvd.mpan,
tvd.meter_id_serial_no,
tvd.meter_register_id,
tvd.settlement_date,
tvd.tot_cons,
tvd.period_tol,
tvd.mpan_setdt_uk_id,
tvd.meter_period_number,
tvd.hh_advance_header_id,
tvd.hh_advance_id,
tvd.hh_advance_header_id_det) t) t1
/


Plan hash value: 4274381546

------------------------------------------------------------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pst
art| Pstop |
------------------------------------------------------------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | | | | 576K(100)| |
| |
| 1 | WINDOW BUFFER | | 2197K| 423M| | 576K (1)| 02:14:26 |
| |
| 2 | VIEW | | 2197K| 423M| | 576K (1)| 02:14:26 |
| |
| 3 | WINDOW BUFFER | | 2197K| 186M| | 576K (1)| 02:14:26 |
| |
| 4 | SORT GROUP BY | | 2197K| 186M| 454M| 576K (1)| 02:14:26 |
| |
|* 5 | HASH JOIN RIGHT OUTER | | 2197K| 186M| | 547K (1)| 02:07:49 |
| |
| 6 | VIEW | | 1 | 32 | | 544K (1)| 02:07:07 |
| |
|* 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| HH_ADVANCE | 1 | 20 | | 50 (0)| 00:00:01 | ROW
L | ROW L |
| 8 | NESTED LOOPS | | 1 | 85 | | 544K (1)| 02:07:07 |
| |
| 9 | NESTED LOOPS | | 1 | 65 | | 544K (1)| 02:07:06 |
| |
| 10 | TABLE ACCESS FULL | TR_VALID_WORK_4WEEK_DATES_T | 271K| 8762K| | 233 (4)| 00:00:04 |
| |
| 11 | TABLE ACCESS BY INDEX ROWID | HH_ACTUAL_ADVANCES | 1 | 32 | | 2 (0)| 00:00:01 |
| |
|* 12 | INDEX RANGE SCAN | HAA_MPMIMPMS_IX | 1 | | | 2 (0)| 00:00:01 |
| |
| 13 | PARTITION HASH SINGLE | | 48 | | | 3 (0)| 00:00:01 |
1 | 1 |
|* 14 | INDEX RANGE SCAN | HH_ADVANCE_MP_SET_PRD_NO_IX | 48 | | | 3 (0)| 00:00:01 |
1 | 1 |
| 15 | TABLE ACCESS FULL | TR_VALID_FOR_PROFILE_T | 2197K| 119M| | 2987 (4)| 00:00:42 |
| |
------------------------------------------------------------------------------------------------------------------------------------
------------

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

5 - access("TVD"."MPAN"="HAH"."MPAN" AND "TVD"."METER_ID_SERIAL_NO"="HAH"."METER_ID_SERIAL_NO" AND
"TVD"."METER_REGISTER_ID"="HAH"."METER_REGISTER_ID" AND "TVD"."MPAN_SETDT_UK_ID"="HAH"."MPAN_SETDT_UK_ID_PROFILE" AND
"TVD"."METER_PERIOD_NUMBER"="HAH"."METER_PERIOD_NUMBER")
7 - filter("HA"."SETTLEMENT_DATE"="HAA"."SETTLEMENT_DATE")
12 - access("HAA"."MPAN"="TVPD"."MPAN" AND "HAA"."METER_REGISTER_ID"="TVPD"."METER_REGISTER_ID" AND
"HAA"."METER_ID_SERIAL_NO"="TVPD"."METER_ID_SERIAL_NO" AND "HAA"."SETTLEMENT_DATE"="TVPD"."VALID_PREV_DATE")
14 - access("HAA"."MPAN_SETDT_UK_ID"="HA"."MPAN_SETDT_UK_ID")

Thanks very much in advance.

Paresh


Tom Kyte
July 08, 2006 - 7:59 am UTC

query v$sql_plan, the plan is in there - you need not wait for anything.

Partition pruning and union all

David Pujol, August 08, 2007 - 2:03 pm UTC

Hi Tom,

I'm investigating one "wrong" execution plan in a datawarehouse database. Optimizer doesn't execute partition pruning on union all view .., or I'm not achieve  ....

Let me show you my tescase. How I could achieve partition pruning without execute changes in a union all view?

CREATE TABLE TEST01 (
OBJECT_ID    NUMBER
,OBJECT_NAME  VARCHAR2(200)
,OBJECT_TYPE  VARCHAR2(100)
,PART_ID      NUMBER NOT NULL)
PARTITION BY LIST (PART_ID)
 (PARTITION P0 VALUES (0)
 ,PARTITION P1 VALUES (1)
 ,PARTITION P2 VALUES (2)
 ,PARTITION P3 VALUES (3)
 ,PARTITION P4 VALUES (4)
 ,PARTITION P5 VALUES (5)
 ,PARTITION P6 VALUES (6)
 ,PARTITION P7 VALUES (7)
 ,PARTITION P8 VALUES (8)
 ,PARTITION P9 VALUES (9)
 ,PARTITION P10 VALUES (10));
 
CREATE TABLE TEST02 (
OBJECT_ID    NUMBER
,OBJECT_NAME  VARCHAR2(200)
,OBJECT_TYPE  VARCHAR2(100)
,PART_ID      NUMBER NOT NULL)
COMPRESS
PARTITION BY LIST (PART_ID)
 (PARTITION P0 VALUES (0)
 ,PARTITION P1 VALUES (1)
 ,PARTITION P2 VALUES (2)
 ,PARTITION P3 VALUES (3)
 ,PARTITION P4 VALUES (4)
 ,PARTITION P5 VALUES (5)
 ,PARTITION P6 VALUES (6)
 ,PARTITION P7 VALUES (7)
 ,PARTITION P8 VALUES (8)
 ,PARTITION P9 VALUES (9)
 ,PARTITION P10 VALUES (10)); 
 
CREATE VIEW TEST_VIEW AS 
select * from test01
union all
select * from test02;

CREATE TABLE LKJ
(PART_ID   NUMBER NOT NULL
 ,OTRO      VARCHAR2(10) NOT NULL);

insert into test01
 select object_id, object_name, object_type, 1 from dba_objects; 

insert into test01
 select object_id, object_name, object_type, 2 from dba_objects; 
 
 insert into test01
 select object_id, object_name, object_type, 3 from dba_objects; 
 
 insert into test01
 select object_id, object_name, object_type, 4 from dba_objects; 
 
 insert into test01
 select object_id, object_name, object_type, 5 from dba_objects; 
 
 insert into test01
 select object_id, object_name, object_type, 6 from dba_objects; 
 
 insert into test01
 select object_id, object_name, object_type, 7 from dba_objects; 
 
 insert into test01
 select object_id, object_name, object_type, 8 from dba_objects; 
 
 insert into test01
 select object_id, object_name, object_type, 9 from dba_objects; 
 
 insert into test01
 select object_id, object_name, object_type, 10 from dba_objects; 
 
 insert into test02
 select object_id, object_name, object_type, 1 from dba_objects; 

insert into test02
 select object_id, object_name, object_type, 2 from dba_objects; 
 
 insert into test02
 select object_id, object_name, object_type, 3 from dba_objects; 
 
 insert into test02
 select object_id, object_name, object_type, 4 from dba_objects; 
 
 insert into test02
 select object_id, object_name, object_type, 5 from dba_objects; 
 
 insert into test02
 select object_id, object_name, object_type, 6 from dba_objects; 
 
 insert into test02
 select object_id, object_name, object_type, 7 from dba_objects; 
 
 insert into test02
 select object_id, object_name, object_type, 8 from dba_objects; 
 
 insert into test02
 select object_id, object_name, object_type, 9 from dba_objects; 
 
 insert into test02
 select object_id, object_name, object_type, 10 from dba_objects; 
 
insert into lkj values (1, 'primera');
insert into lkj values (2, 'segunda');
insert into lkj values (3, 'tercera');
insert into lkj values (4, 'cuarta');
insert into lkj values (5, 'quinta');
insert into lkj values (6, 'sexta');
insert into lkj values (7, 'septima');
insert into lkj values (8, 'octava');
insert into lkj values (9, 'novena');
insert into lkj values (10, 'decima');

SQL> exec dbms_stats.gather_table_stats('sabadell', 'test02', granularity=>'all', cascade=>true, estimate_percent=>dbms_stats.auto_sample_size)
Procedimiento PL/SQL terminado correctamente.

SQL> exec dbms_stats.gather_table_stats('sabadell', 'test01', granularity=>'all', cascade=>true , estimate_percent=>dbms_stats.auto_sample_size)
Procedimiento PL/SQL terminado correctamente.

SQL> exec dbms_stats.gather_table_stats('sabadell', 'lkj', granularity=>'all', cascade=>true, estimate_percent=>100)
Procedimiento PL/SQL terminado correctamente.

Select * from TEST_VIEW a,
              LKJ b
        WHERE a.PART_ID = b.PART_ID and
              b.OTRO = 'primera';


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1949220219

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           | 28465 |  5281K|   345   (2)| 00:00:05 |       |       |
|*  1 |  HASH JOIN            |           | 28465 |  5281K|   345   (2)| 00:00:05 |       |       |
|*  2 |   TABLE ACCESS FULL   | LKJ       |     1 |    10 |     3   (0)| 00:00:01 |       |       |
|   3 |   VIEW                | TEST_VIEW |   284K|    48M|   340   (1)| 00:00:05 |       |       |
|   4 |    UNION-ALL          |           |       |       |            |          |       |       |
|   5 |     PARTITION LIST ALL|           |   142K|  4453K|   178   (2)| 00:00:03 |     1 |    11 |
|   6 |      TABLE ACCESS FULL| TEST01    |   142K|  4453K|   178   (2)| 00:00:03 |     1 |    11 |
|   7 |     PARTITION LIST ALL|           |   142K|  4441K|   163   (2)| 00:00:02 |     1 |    11 |
|   8 |      TABLE ACCESS FULL| TEST02    |   142K|  4441K|   163   (2)| 00:00:02 |     1 |    11 |
---------------------------------------------------------------------------------------------------

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

   1 - access("A"."PART_ID"="B"."PART_ID")
   2 - filter("B"."OTRO"='primera')
 
Thanks in advance Tom

Regards
David Pujol

Tom Kyte
August 14, 2007 - 10:23 am UTC

why or how could this prune?


you partition by part_id

you query by otro


rows can come from EVERY SINGLE partition - in both tables.

I fail to see how any partition can be eliminated.

more pruning

David Pujol, August 14, 2007 - 3:20 pm UTC

Yes, I query by otro field but:

Select * from TEST_VIEW a,
LKJ b
WHERE a.PART_ID = b.PART_ID and
b.OTRO = 'primera';

when otro ='primera' only returns one PART_ID in LKJ and I don't undesrstand why optimizer does't push in view PART_ID and prune partition Px.

In 10gR2 traces I got:

SQL:******* UNPARSED QUERY IS *******
SELECT "A"."OBJECT_ID" "OBJECT_ID","A"."OBJECT_NAME" "OBJECT_NAME","A"."OBJECT_TYPE" "OBJECT_TYPE","A"."PART_ID" "PART_ID","B"."PART_ID" "PART_ID","B"."OTRO" "OTRO" FROM ( (SELECT "TEST01"."OBJECT_ID" "OBJECT_ID","TEST01"."OBJECT_NAME" "OBJECT_NAME","TEST01"."OBJECT_TYPE" "OBJECT_TYPE","TEST01"."PART_ID" "PART_ID" FROM SABADELL."TEST01" "TEST01") UNION ALL (SELECT "TEST02"."OBJECT_ID" "OBJECT_ID","TEST02"."OBJECT_NAME" "OBJECT_NAME","TEST02"."OBJECT_TYPE" "OBJECT_TYPE","TEST02"."PART_ID" "PART_ID" FROM SABADELL."TEST02" "TEST02")) "A","SABADELL"."LKJ" "B" WHERE "A"."PART_ID"="B"."PART_ID" AND "B"."OTRO"='primera'
Query block (0x73183ba0) unchanged
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#1).
PM: Checking validity of predicate move-around in SEL$1 (#1).
PM: PM bypassed: Query block is a set operator pseudo-query block.
PM: Passed validity checks.
JPPD: JPPD bypassed: View not on right-side of outer join

I think that my problem is optimizer doesn't push LKJ in union all view. Can you explain me what's mean JPPD: JPPD bypassed: View not on right-side of outer join?


Thanks a lot

Regards
David Pujol

more pruning

A reader, August 14, 2007 - 3:31 pm UTC

And look:

If I create view as:

CREATE or replace VIEW TEST_VIEW AS
select a.otro, b.* from test01 b, lkj a
where a.PART_ID = b.PART_ID
union all
select a.otro, b.* from test02 b, lkj a
where a.PART_ID = b.PART_ID;

and I query:

Select *
from test_view
where otro='primera';

then

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28464 | 5198K| 37 (0)| 00:00:01 | | |
| 1 | VIEW | TEST_VIEW | 28464 | 5198K| 37 (0)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | NESTED LOOPS | | 14252 | 584K| 19 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS FULL | LKJ | 1 | 10 | 3 (0)| 00:00:01 | | |
| 5 | PARTITION LIST ITERATOR| | 14252 | 445K| 16 (0)| 00:00:01 | KEY | KEY |
|* 6 | TABLE ACCESS FULL | TEST01 | 14252 | 445K| 16 (0)| 00:00:01 | KEY | KEY |
| 7 | NESTED LOOPS | | 14212 | 582K| 18 (0)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | LKJ | 1 | 10 | 3 (0)| 00:00:01 | | |
| 9 | PARTITION LIST ITERATOR| | 14212 | 444K| 15 (0)| 00:00:01 | KEY | KEY |
|* 10 | TABLE ACCESS FULL | TEST02 | 14212 | 444K| 15 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------

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

4 - filter("A"."OTRO"='primera' AND "A"."PART_ID">=1 AND "A"."PART_ID"<=10)
6 - filter("A"."PART_ID"="B"."PART_ID")
8 - filter("A"."OTRO"='primera' AND "A"."PART_ID">=1 AND "A"."PART_ID"<=10)
10 - filter("A"."PART_ID"="B"."PART_ID")

I don't understand why optimizer doesn't push condition a.otro='primera' when join is outer union all.

I hope that you can explain me about know WHY optimizer doen't push the condition in union all view, sorry, but I didn't understand your last review.

Thanks a lot

Regards
David Pujol

Tom Kyte
August 20, 2007 - 12:40 pm UTC

umm, it looks pushed to me - steps 4 and 8 happen before 2 does.

you started with "why no partition pruning", you will really need to stick with that thought and tell us HOW you could possibly partition eliminate in YOUR EXAMPLE

More partition pruning

David Pujol, August 20, 2007 - 8:16 pm UTC

Are you Tom?.

I apologize to you, but it seems that you don't "understand" my problem or you did not follow correctly my testcase.
Sorry again and thanks in advance.


I think that my problem is with UNION ALL and push predicate:

**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#1).
PM: Checking validity of predicate move-around in SEL$1 (#1).
PM: PM bypassed: Query block is a set operator pseudo-query block.
PM: Passed validity checks.
JPPD: JPPD bypassed: View not on right-side of outer join

And then, there is not partition pruning. My view definition, was:

CREATE VIEW TEST_VIEW AS
select * from test01
union all
select * from test02;

If I create view without UNION ALL runs ok: push predicate in view and optimizer execute partition pruning.

My question is, why there is not push predicate of LKJ in my UNION ALL?, Can you explain me what does mean:
JPPD: JPPD bypassed: View not on right-side of outer join?


My firstly query was:

Select * from TEST_VIEW a,
LKJ b
WHERE a.PART_ID = b.PART_ID and
b.OTRO = 'primera';


Plan de Ejecución
----------------------------------------------------------
Plan hash value: 1949220219

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28465 | 5281K| 345 (2)| 00:00:05 | | |
|* 1 | HASH JOIN | | 28465 | 5281K| 345 (2)| 00:00:05 | | |
|* 2 | TABLE ACCESS FULL | LKJ | 1 | 10 | 3 (0)| 00:00:01 | | |
| 3 | VIEW | TEST_VIEW | 284K| 48M| 340 (1)| 00:00:05 | | |
| 4 | UNION-ALL | | | | | | | |
| 5 | PARTITION LIST ALL| | 142K| 4453K| 178 (2)| 00:00:03 | 1 | 11 |
| 6 | TABLE ACCESS FULL| TEST01 | 142K| 4453K| 178 (2)| 00:00:03 | 1 | 11 |
| 7 | PARTITION LIST ALL| | 142K| 4441K| 163 (2)| 00:00:02 | 1 | 11 |
| 8 | TABLE ACCESS FULL| TEST02 | 142K| 4441K| 163 (2)| 00:00:02 | 1 | 11 |
---------------------------------------------------------------------------------------------------


I think that my problem is optimizer doesn't push LKJ in union all view. Can you explain me what's
mean JPPD: JPPD bypassed: View not on right-side of outer join?


Tom Kyte
August 22, 2007 - 11:15 am UTC

yes, I am Tom.

I am the only one that answers here. period.


tell you what (if you read around here lots, you already know this)

be concise
put it all together
give a complete example
don't make me put bits and bytes together from many followups
pretend I don't read anything on the page except what you just put here (cause I don't, not enough hours in the day)



and tell me HOW or WHY partition pruning could come into play here please - you have consistently failed to address that question for me.

Pushing Joins

Jonathan Lewis, August 21, 2007 - 5:29 pm UTC

Tom, I think the problem David Pujol is demonstrating is that he can manually create an execution path which, in principle, we might expect the optimizer to be able to produce.

select ... 
from
        driving_table dt,
        partitioned_table pt
where
        dt.colx   = 'const'
and     pt.pt_col = dt.some_col
;

For a simple partitioned table, the optimizer can do subquery pruning - run a preliminary subquery against the driving_table to discover which values of some_col will appear and then do a hash join that visits only the correct partitions of the partitioned table. But if the 'partitioned table' is actually a union all of two equi-partitioned tables then the optimizer does not try this.

However, if he rewrites the query to put the join inside the union all, the optimizer does the subquery pruning twice.

So the question is: why doesn't the optimizer first push the join inside the view (which it can do in some cases) and then do the subquery pruning that appears in his second example.

The answer, I guess, is that it just hasn't been written that way (yet) - things which are visible to the human eye aren't always "visible" to the optimizer.

Regards
Jonathan Lewis

More pruning

David Pujol, August 23, 2007 - 3:52 pm UTC

Tom, Jonathan, thanks for your replies.

I think optimizer cannot push predicates in UNION ALL view (is not a mergeable view), but, in Jonathans' review ..., I suspect that in some cases optimizer can push outer predicate in UNION ALL view. But, my question ..., when I run 10053 trace, I get:

JPPD: JPPD bypassed: View not on right-side of outer join

and I don't understand this comment, what does it mean. I aggree with Jonathan that things which are visible to the human eye aren't always "visible" to the optimizer, but in this case, ..., can you assure me that optimizer will not be able to push outer predicate in UNION ALL view?, I think that probably I'll have to drop UNION ALL view and try to subpart. my table.

Thanks twice.

Regards
David Pujol

UNION ALL pushing

A reader, August 31, 2007 - 11:56 am UTC

Hi again,

I've achieved (in 10.2) to push predicate inside UNION ALL view, but only if I create a UNIQUE KEY (and only if index is unique) in NESTED LOOPS fashion. My production database is in 9.2.0.8 and I'd like to achieve this behavior in my version, do you think is possible?, I've opened a SR too.

SQL> alter table lkj add constraint uk_lkj unique (otro);

SQL> explain plan for
Select * from TEST_VIEW a,
              LKJ b
        WHERE a.PART_ID = b.PART_ID and
              b.OTRO = 'primera';  2    3    4    5

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
Plan hash value: 1514877540

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           | 25490 |  4878K|    33   (4)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                 |           | 25490 |  4878K|    33   (4)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID | LKJ       |     1 |    10 |     1   (0)| 00:00:01 |       |       |
|*  3 |    INDEX UNIQUE SCAN          | UK_LKJ    |     1 |       |     0   (0)| 00:00:01 |       |       |
|   4 |   VIEW                        | TEST_VIEW |  2549 |   463K|    32   (4)| 00:00:01 |       |       |
|   5 |    UNION ALL PUSHED PREDICATE |           |       |       |            |          |       |       |
|   6 |     PARTITION LIST SINGLE     |           | 12753 |   398K|    17   (6)| 00:00:01 |   KEY |   KEY |
|   7 |      TABLE ACCESS FULL        | TEST01    | 12753 |   398K|    17   (6)| 00:00:01 |   KEY |   KEY |
|   8 |     PARTITION LIST SINGLE     |           | 12737 |   398K|    16   (7)| 00:00:01 |   KEY |   KEY |
|   9 |      TABLE ACCESS FULL        | TEST02    | 12737 |   398K|    16   (7)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."OTRO"='primera')

21 rows selected.

Regards

Partition pruning elemination not optimal in some cases

Adrian Angelov, December 23, 2008 - 7:42 am UTC

I would like to warn you on a particular case where partition pruning is not eliminating in an optimal manner.

I've done the test case on 10.2.0.2 and 11.1.0.7.
Important things to look at:

- range partitioning definitions.
- PSTART, PEND
- where clause partition key usage with partition definition boundary values


create table ADRIAN.B
(
VALEUR DATE default SYSDATE not null,
Q_ARC NUMBER(1) default 0 not null
)
partition by range (VALEUR, Q_ARC)
(
partition DE2009 values less than (TO_DATE(' 2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2)
tablespace GBACC_DAT,
partition DE2010 values less than (TO_DATE(' 2011-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2)
tablespace GBACC_DAT,
partition DE2011 values less than (TO_DATE(' 2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2)
tablespace GBACC_DAT
);

create table ADRIAN.C
(
VALEUR DATE default SYSDATE not null,
Q_ARC NUMBER(1) default 0 not null
)
partition by range (VALEUR)
(
partition DE2009 values less than (TO_DATE(' 2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace GBACC_DAT,
partition DE2010 values less than (TO_DATE(' 2011-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace GBACC_DAT,
partition DE2011 values less than (TO_DATE(' 2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
tablespace GBACC_DAT
);

set autotrace on explain

-- test on ADRIAN.B - partition pruning NOT OPTIMAL
SELECT /*+ FULL(pp)*/
COUNT(1)
FROM adrian.b pp
WHERE valeur >= to_date('01/01/2010 00:00:00', 'dd/mm/yyyy hh24:mi:ss');

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ALL| | 1 | 9 | 2 (0)| 00:00:01 | 1 | 3 |
|* 3 | TABLE ACCESS FULL | B | 1 | 9 | 2 (0)| 00:00:01 | 1 | 3 |
---------------------------------------------------------------------------------------------

-- test on ADRIAN.B add one second - partition pruning is optimal
SELECT /*+ FULL(pp)*/
COUNT(1)
FROM adrian.b pp
WHERE valeur >= to_date('01/01/2010 00:00:01', 'dd/mm/yyyy hh24:mi:ss');

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 1 | 9 | 2 (0)| 00:00:01 | 2 | 3 |
|* 3 | TABLE ACCESS FULL | B | 1 | 9 | 2 (0)| 00:00:01 | 2 | 3 |
--------------------------------------------------------------------------------------------------


-- test on ADRIAN.C - partition pruning is optimal
SELECT /*+ FULL(pp)*/
COUNT(1)
FROM adrian.c pp
WHERE valeur >= to_date('01/01/2010 00:00:00', 'dd/mm/yyyy hh24:mi:ss');

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 1 | 9 | 2 (0)| 00:00:01 | 2 | 3 |
| 3 | TABLE ACCESS FULL | C | 1 | 9 | 2 (0)| 00:00:01 | 2 | 3 |
--------------------------------------------------------------------------------------------------

-- test on ADRIAN.C add one second - partition pruning is optimal
SELECT /*+ FULL(pp)*/
COUNT(1)
FROM adrian.c pp
WHERE valeur >= to_date('01/01/2010 00:00:01', 'dd/mm/yyyy hh24:mi:ss');

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 2 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 1 | 9 | 2 (0)| 00:00:01 | 2 | 3 |
|* 3 | TABLE ACCESS FULL | C | 1 | 9 | 2 (0)| 00:00:01 | 2 | 3 |
--------------------------------------------------------------------------------------------------

The test case shows that when we have:

- range partitioning on two columns(one DATE and one NUMBER)

- a statement that contains in its where clause >= comparison with a partition boundary value(2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS' in this case)




Partition pruning is not optimal. It scans partitions 1,2 and 3 when it needs data only from partitions 2 and 3.
Despite of:
The documentation states:

"This clause specifies a non-inclusive upper bound for the partitions. All partitions,
except the first, have an implicit low value specified by the VALUES LESS THAN
literal on the previous partition. Any binary values of the partition key equal
to or higher than this literal are added to the next higher
partition."

Source:
Oracle® Database Data Warehousing Guide 10g Release 2 (10.2) Part Number B14223-02
Chapter 5 Partitioning in Data Warehouses
Range Partitioning part.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/parpart.htm#sthref206


When range partitioning is done only on one column(ADRIAN.C), there is no such problem.

I guess that this is a case that many organizations will suffer, not optimal performance for queries on partitioned tables with more than one partitioned key used in a similar manner.
Tom Kyte
December 29, 2008 - 3:11 pm UTC

You are making a mistake in your understanding of range partitioning with vectors.

Your two queries "non-optimal" and "optimal" return very different results


ops$tkyte%ORA10GR2> create table t
  2  (
  3    VALEUR       DATE default SYSDATE not null,
  4    Q_ARC        NUMBER(1) default 0 not null
  5  )
  6  partition by range (VALEUR, Q_ARC)
  7  (
  8    partition DE2009 values less than (TO_DATE(' 2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2),
  9    partition DE2010 values less than (TO_DATE(' 2011-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2),
 10    partition DE2011 values less than (TO_DATE(' 2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2)
 11  );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values ( to_date( '01/01/2010 00:00:00', 'dd/mm/yyyy hh24:mi:ss' ), 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( to_date( '01/01/2010 00:00:01', 'dd/mm/yyyy hh24:mi:ss' ), 1 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> SELECT * FROM t
  2    WHERE valeur >= to_date('01/01/2010 00:00:00', 'dd/mm/yyyy hh24:mi:ss');

VALEUR         Q_ARC
--------- ----------
01-JAN-10          1
01-JAN-10          1

ops$tkyte%ORA10GR2> SELECT * FROM t
  2    WHERE valeur >= to_date('01/01/2010 00:00:01', 'dd/mm/yyyy hh24:mi:ss');

VALEUR         Q_ARC
--------- ----------
01-JAN-10          1




So, you cannot compare then, that partition DOES need to be scanned. You are misunderstanding how range partitioning on a composite key works...

It's not the result that is important in this case, it's the way Oracle gets it

Adrian Angelov, December 29, 2008 - 4:40 pm UTC

It's not the result that is important in this case, the important thing is what Oracle scans to get the result(in so called non-optimal case).

The first select from your example:

SQL> set autotrace on explain
SQL>  SELECT * FROM t  WHERE valeur >= to_date('01/01/2010 00:00:00', 'dd/mm/yyy
y hh24:mi:ss');

VALEUR               Q_ARC
--------------- ----------
01-JAN-10                1
01-JAN-10                1


Execution Plan
----------------------------------------------------------
Plan hash value: 3557914527

--------------------------------------------------------------------------------

------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pst

art| Pstop |

--------------------------------------------------------------------------------

------------

|   0 | SELECT STATEMENT    |      |     2 |    44 |     3   (0)| 00:00:01 |
   |       |

|   1 |  PARTITION RANGE ALL|      |     2 |    44 |     3   (0)| 00:00:01 |
 1 |     3 |

|*  2 |   TABLE ACCESS FULL | T    |     2 |    44 |     3   (0)| 00:00:01 |
 1 |     3 |

--------------------------------------------------------------------------------

------------


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

   2 - filter("VALEUR">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'

))


Note
-----
   - dynamic sampling used for this statement

SQL>

is not doing what is expected when partition pruning is in use(see pstart=1 and pend=3).

The important thing here is that Oracle scans partitions 1,2 and 3 when it only needs data from partitions 2 and 3.
Why it should skip partition 1(my understanding)? This is because the boundary for partition 1 is not included in it but in the upper next partition which is 2(described in the docs).

Statements with where clause like:

 WHERE valeur >= to_date('01/01/2010 00:00:00', 'dd/mm/yyy
y hh24:mi:ss');

need to scan only 2 and 3 but unfortunately partition 1 is also scanned by Oracle.

Now, imagine that every partition is 10GB(30GB in total for table t in your example). Why wait for a scan of 30GB when Oracle can return the result by scanning only 20GB during execution of:

 SELECT * FROM t  WHERE valeur >= to_date('01/01/2010 00:00:00', 'dd/mm/yyyy hh24:mi:ss');


Anyway, Oracle Support confirmed that this is a bug(bug 7665692, probably not visible by everyone) and it's assigned to Development as of now.

My understanding :),who cares, it's just a point of view, I've wrote how to check, so everyone can confirm it if performance is considered 

- on a partitioned table 
- with more than one partitioning key 
- and partition boundary values are used in the statement's where clause.


Thank You.

Tom Kyte
December 31, 2008 - 8:27 am UTC

umm, it is the result.

You are saying "look, partition elimination isn't working"

I am saying "yes it is, you are misunderstanding where the data will be placed with composite keys in a range partition"

If Oracle eliminated the partition you think it should IT WOULD GET THE WRONG ANSWER.

We are eliminating from consideration every partition we can. When you change your query to get "the optimal set" of partitions in your mind - you are changing the result set.

Therefore, you cannot in any way shape or form compare them!


... My understanding :),who cares, it's just a point of view, ...

it is not a point of view, you are provably "not correct" from a technical perspective here. Please revisit my very simple example.

If two queries return different results - you CANNOT COMPARE THEM - they are utterly different. Your query returns *the wrong answer*.

Unless of course, if your query answers your QUESTION - then the original query was wrong (a bug in your developed code) and would need to be fixed.


That bug is being closed as "not a bug" - the comments in there (that you cannot see) state in part "as per doc we see ...." - Meaning - IT IS NOT a bug.


Please - just look at the simple, the very very simple, two row example. You are not understanding how multi-column partitioning *works*


when you partition by (a,b) - we compare vectors. NOT scalars, vectors.

Your premise is the partition DE2009 would only have 2009 data. It does not, it can have 2010 data as well - by the very definition of range partitioning
ops$tkyte%ORA10GR2> create table t
  2  (
  3    VALEUR       DATE default SYSDATE not null,
  4    Q_ARC        NUMBER(1) default 0 not null
  5  )
  6  partition by range (VALEUR, Q_ARC)
  7  (
  8    partition DE2009 values less than (TO_DATE(' 2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2),
  9    partition DE2010 values less than (TO_DATE(' 2011-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2),
 10    partition DE2011 values less than (TO_DATE(' 2012-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2)
 11  );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( to_date( '01/01/2010 00:00:00', 'dd/mm/yyyy hh24:mi:ss' ), 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( to_date( '01/01/2010 00:00:01', 'dd/mm/yyyy hh24:mi:ss' ), 1 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select * from t partition (de2009);

VALEUR         Q_ARC
--------- ----------
01-JAN-10          1

ops$tkyte%ORA10GR2> select * from t partition (de2010);

VALEUR         Q_ARC
--------- ----------
01-JAN-10          1

ops$tkyte%ORA10GR2> select * from t partition (de2011);

no rows selected



we have to (and do, and only do) scan the first two partitions to answer your original query.

Martin, December 30, 2008 - 2:40 pm UTC

Adrian,

the plan you deem nonoptimal is correct - it needs to scan partition 1 too.

Partition 1 on your table ADRIAN.B contains not only rows where VALEUR < to_date('01/01/2010 00:00:00'), but also rows where (VALEUR = to_date('01/01/2010 00:00:00')) and (Q_ARC<2). These rows accomplish the partition 1 condition

values less than (TO_DATE(' 2010-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 2)
because that is the way comparison of compound keys works.

If the first value in a compound key is equal, the result of the comparison is defined by the following key(s).

Thanks

Adrian Angelov, December 31, 2008 - 2:38 pm UTC

Thanks Tom, Martin.
Now I admit that I'm wrong.
Thanks again for your thorough clarifications.

Partition elimination not happening

Mike, April 01, 2009 - 12:12 am UTC

Tom

I have table partitioned by date range and subpartitioned by list.  Table has 32 partitions and 160 subpartitions. Query against this table is not able to perform partition elimination (always scans 1 -160 partition) which is causing the query running extremely slow.

a) Is there any way to make the optimizer to perform partition elimination

b) Is it mandatory to have partitioned key indexed ?

c) I gathered optimizer statistics at the table level (DBMS_STATS.GATHER_TABLE_STATS).
Do i need to gather stats at partition level. ?

SQL> EXPLAIN PLAN FOR
  2  SELECT COUNT(*)
  3  FROM (       (
  4     SELECT /*+ parallel (a,8)*/
  5        gl_effective_dt,
  6        rein_direction_cd,
  7        SUM(end_asco_financial_am) asco_financial_am
  8     FROM etl.mnthly_rsrvs a
  9     WHERE gl_effective_dt IN (
 10     SELECT gl_effective_dt
 11     FROM etl.gldate)
 12     AND transaction_typ_cd IN (1, 4, 5, 6, 8, 9, 10, 13, 18, 22, 29, 30, 36, 37,
 13     44, 45)
 14     AND mach_nm_orig_cd = 'M'
 15     GROUP BY gl_effective_dt, rein_direction_cd ) MINUS (
 16     SELECT /*+ parallel (a,4)(d,4)(c,4)*/
 17        a.gl_effective_dt,
 18        a.rein_direction_cd,
 19        SUM(NVL(d.asco_financial_am, c.asco_financial_am)) asco_financial_am
 20     FROM src.theader a, src.tpartcpnt_explsn d, src.tsub_header_fncl c
 21     WHERE a.gl_effective_dt IN (
 22     SELECT gl_effective_dt
 23     FROM etl.gldate)
 24     AND mach_nm_orig_cd = 'M'
 25     AND document_status_cd = 'A'
 26     AND a.document_no NOT IN (
 27     SELECT document_no
 28     FROM etl.theader_reject)
 29     AND a.document_no = c.document_no
 30     AND c.document_no = d.document_no(+)
 31     AND c.sub_header_no = d.sub_header_no(+)
 32     AND c.rdps_maj_class_no = d.rdps_maj_class_no(+)
 33     AND c.rdps_bus_type_cd = d.rdps_bus_type_cd(+)
 34     AND c.transaction_typ_cd = d.transaction_typ_cd(+)
 35     AND c.trans_sub_typ_cd = d.trans_sub_typ_cd(+)
 36     AND NVL(d.transaction_typ_cd, c.transaction_typ_cd) IN (1, 4, 5, 6, 8, 9, 10
 37     , 13, 18, 22, 29, 30, 36, 37, 44, 45)
 38     GROUP BY a.gl_effective_dt, a.rein_direction_cd ) )
 39  /

Explained.

SQL> 
SQL> select * from TABLE(DBMS_XPLAN.DISPLAY) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1138566440

-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ      |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                  |     1 |       |   256K(-346| 00:59:58 |       |       |    |      |            |
|   1 |  SORT AGGREGATE                             |                  |     1 |       |            |          |       |       |    |      |            |
|   2 |   PX COORDINATOR                            |                  |       |       |            |          |       |       |    |      |            |
|   3 |    PX SEND QC (RANDOM)                      | :TQ10007         |     1 |       |            |          |       |       |  Q1,07 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                          |                  |     1 |       |            |          |       |       |  Q1,07 | PCWP |        |
|   5 |      VIEW                                   |                  |    32 |       |   256K(-346| 00:59:58 |       |       |  Q1,07 | PCWP |        |
|   6 |       MINUS                                 |                  |       |       |            |          |       |       |  Q1,07 | PCWP |        |
|   7 |        SORT UNIQUE                          |                  |    32 |   960 |   131K (22)| 00:30:48 |       |       |  Q1,07 | PCWP |        |
|   8 |         PX RECEIVE                          |                  |    32 |   960 |   131K (22)| 00:30:48 |       |       |  Q1,07 | PCWP |        |
|   9 |          PX SEND HASH                       | :TQ10005         |    32 |   960 |   131K (22)| 00:30:48 |       |       |  Q1,05 | P->P | HASH           |
|  10 |           HASH GROUP BY                     |                  |    32 |   960 |   131K (22)| 00:30:48 |       |       |  Q1,05 | PCWP |        |
|  11 |            PX RECEIVE                       |                  |    32 |   960 |   131K (22)| 00:30:48 |       |       |  Q1,05 | PCWP |        |
|  12 |             PX SEND HASH                    | :TQ10003         |    32 |   960 |   131K (22)| 00:30:48 |       |       |  Q1,03 | P->P | HASH           |
|  13 |              HASH GROUP BY                  |                  |    32 |   960 |   131K (22)| 00:30:48 |       |       |  Q1,03 | PCWP |        |
|* 14 |               HASH JOIN                     |                  |    59M|  1705M|   122K (16)| 00:28:42 |       |       |  Q1,03 | PCWP |        |
|  15 |                BUFFER SORT                  |                  |       |       |            |          |       |       |  Q1,03 | PCWC |        |
|  16 |                 PX RECEIVE                  |                  |    15 |   120 |     1   (0)| 00:00:01 |       |       |  Q1,03 | PCWP |        |
|  17 |                  PX SEND BROADCAST LOCAL    | :TQ10000         |    15 |   120 |     1   (0)| 00:00:01 |       |       |    | S->P | BCST LOCAL |
|  18 |                   INDEX FULL SCAN           | SYS_C0080134     |    15 |   120 |     1   (0)| 00:00:01 |       |       |    |      |            |
|  19 |                PX BLOCK ITERATOR            |                  |   103M|  2167M|   122K (15)| 00:28:32 |     1 |     5 |  Q1,03 | PCWC |        |
|* 20 |                 TABLE ACCESS FULL           | MNTHLY_RSRVS     |   103M|  2167M|   122K (15)| 00:28:32 |     1 |   160 |  Q1,03 | PCWP |        |
|  21 |        SORT UNIQUE                          |                  |    32 |  2976 |   124K (11)| 00:29:10 |       |       |  Q1,07 | PCWP |        |
|  22 |         PX RECEIVE                          |                  |    32 |  2976 |   124K (11)| 00:29:10 |       |       |  Q1,07 | PCWP |        |
|  23 |          PX SEND HASH                       | :TQ10006         |    32 |  2976 |   124K (11)| 00:29:10 |       |       |  Q1,06 | P->P | HASH           |
|  24 |           HASH GROUP BY                     |                  |    32 |  2976 |   124K (11)| 00:29:10 |       |       |  Q1,06 | PCWP |        |
|  25 |            PX RECEIVE                       |                  |    32 |  2976 |   124K (11)| 00:29:10 |       |       |  Q1,06 | PCWP |        |
|  26 |             PX SEND HASH                    | :TQ10004         |    32 |  2976 |   124K (11)| 00:29:10 |       |       |  Q1,04 | P->P | HASH           |
|  27 |              HASH GROUP BY                  |                  |    32 |  2976 |   124K (11)| 00:29:10 |       |       |  Q1,04 | PCWP |        |
|* 28 |               FILTER                        |                  |       |       |            |          |       |       |  Q1,04 | PCWC |        |
|  29 |                NESTED LOOPS OUTER           |                  |  4759 |   432K|   124K (11)| 00:29:10 |       |       |  Q1,04 | PCWP |        |
|  30 |                 NESTED LOOPS                |                  |  1518 | 98670 |   123K (12)| 00:28:51 |       |       |  Q1,04 | PCWP |        |
|* 31 |                  HASH JOIN ANTI             |                  |   730 | 27010 |   122K (12)| 00:28:42 |       |       |  Q1,04 | PCWP |        |
|* 32 |                   HASH JOIN                 |                  |  1794K|    51M|   122K (12)| 00:28:30 |       |       |  Q1,04 | PCWP |        |
|  33 |                    BUFFER SORT              |                  |       |       |            |          |       |       |  Q1,04 | PCWC |        |
|  34 |                     PX RECEIVE              |                  |    15 |   120 |     1   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |        |
|  35 |                      PX SEND BROADCAST      | :TQ10001         |    15 |   120 |     1   (0)| 00:00:01 |       |       |    | S->P | BROADCAST  |
|  36 |                       INDEX FULL SCAN       | SYS_C0080134     |    15 |   120 |     1   (0)| 00:00:01 |       |       |    |      |            |
|  37 |                    PX BLOCK ITERATOR        |                  |    35M|   750M|   121K (11)| 00:28:24 |       |       |  Q1,04 | PCWC |        |
|* 38 |                     TABLE ACCESS FULL       | THEADER          |    35M|   750M|   121K (11)| 00:28:24 |       |       |  Q1,04 | PCWP |        |
|  39 |                   BUFFER SORT               |                  |       |       |            |          |       |       |  Q1,04 | PCWC |        |
|  40 |                    PX RECEIVE               |                  |  1114K|  7619K|   816  (11)| 00:00:12 |       |       |  Q1,04 | PCWP |        |
|  41 |                     PX SEND BROADCAST       | :TQ10002         |  1114K|  7619K|   816  (11)| 00:00:12 |       |       |    | S->P | BROADCAST  |
|  42 |                      INDEX FAST FULL SCAN   | DOC_REJ_IDX      |  1114K|  7619K|   816  (11)| 00:00:12 |       |       |    |      |            |
|  43 |                  TABLE ACCESS BY INDEX ROWID| TSUB_HEADER_FNCL |     2 |    56 |     3   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |        |
|* 44 |                   INDEX RANGE SCAN          | PSUB_HEADER_FNCL |     2 |       |     2   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |        |
|  45 |                 TABLE ACCESS BY INDEX ROWID | TPARTCPNT_EXPLSN |     3 |    84 |     4   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |        |
|* 46 |                  INDEX RANGE SCAN           | PPARTCPNT_EXPLSN |     1 |       |     3   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |        |
-------------------------------------------------------------------------------------------------------------------------------------------------------------

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

  14 - access("GL_EFFECTIVE_DT"="GL_EFFECTIVE_DT")
  20 - filter("MACH_NM_ORIG_CD"='M' AND ("TRANSACTION_TYP_CD"=1 OR "TRANSACTION_TYP_CD"=4 OR "TRANSACTION_TYP_CD"=5 OR "TRANSACTION_TYP_CD"=6 OR
              "TRANSACTION_TYP_CD"=8 OR "TRANSACTION_TYP_CD"=9 OR "TRANSACTION_TYP_CD"=10 OR "TRANSACTION_TYP_CD"=13 OR "TRANSACTION_TYP_CD"=18 OR
              "TRANSACTION_TYP_CD"=22 OR "TRANSACTION_TYP_CD"=29 OR "TRANSACTION_TYP_CD"=30 OR "TRANSACTION_TYP_CD"=36 OR "TRANSACTION_TYP_CD"=37 OR
              "TRANSACTION_TYP_CD"=44 OR "TRANSACTION_TYP_CD"=45))
  28 - filter(NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=1 OR NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=4 OR
              NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=5 OR NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=6 OR
              NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=8 OR NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=9 OR
              NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=10 OR NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=13 OR
              NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=18 OR NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=22 OR
              NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=29 OR NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=30 OR
              NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=36 OR NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=37 OR
              NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=44 OR NVL("D"."TRANSACTION_TYP_CD","C"."TRANSACTION_TYP_CD")=45)
  31 - access("A"."DOCUMENT_NO"="DOCUMENT_NO")
  32 - access("A"."GL_EFFECTIVE_DT"="GL_EFFECTIVE_DT")
  38 - filter("DOCUMENT_STATUS_CD"='A' AND "MACH_NM_ORIG_CD"='M')
  44 - access("A"."DOCUMENT_NO"="C"."DOCUMENT_NO")
  46 - access("C"."DOCUMENT_NO"="D"."DOCUMENT_NO"(+) AND "C"."SUB_HEADER_NO"="D"."SUB_HEADER_NO"(+) AND
              "C"."RDPS_MAJ_CLASS_NO"="D"."RDPS_MAJ_CLASS_NO"(+) AND "C"."RDPS_BUS_TYPE_CD"="D"."RDPS_BUS_TYPE_CD"(+) AND
              "C"."TRANSACTION_TYP_CD"="D"."TRANSACTION_TYP_CD"(+) AND "C"."TRANS_SUB_TYP_CD"="D"."TRANS_SUB_TYP_CD"(+))

77 rows selected.


Tom Kyte
April 01, 2009 - 9:12 am UTC

well, given I haven't any clue as to what is actually being used to partition or how the partitioning scheme works (eg: I don't know a thing about your schema). I guess the table in question is MNTHLY_RSRVS, but only because I see a 160 in the plan.

and that I cannot really read the plan (make it skinnier if you want anyone to look so we can read it)

and you don't tell us what you think should happen (eg: describe what partitions you think it should hit and why the others should be ignored)


and are you really doing a count(*)??? if not, do not use count(*) for 'testing', when you count(*) we know "you don't need columns" so plans change dramatically in most cases.

that is, the plan of attack for

YOUR_COMPLEX_QUERY

versus

select count(*) from (YOUR_COMPLEX_QUERY)

is usually very very different - using that count(*) 'trick' is a horrible approach. leads to misleading things...

Partition pruning

Mike, April 01, 2009 - 12:38 pm UTC

Thanks Tom.
Sorry for the improper aligment in explain plan.
I tried to paste the content in smaller font size, still it appears larger on the screen. I could not paste the explain plain again.

The given query before should be hitting only 15 partitions. 
Here is the monthly_rsrvs table DDL and indexes on it.
Please provide your suggestions on how to perform partition pruning.

Is there any better way to rewrite this query which has count(*) ?


== here is the date range to scan partitions. so there are 15 partitions (monthly)  ==
 1* select * from ETL.GLDATE
SQL> /

GL_EFFECT
---------
01-JAN-08
01-FEB-08
01-MAR-08
01-APR-08
01-MAY-08
01-JUN-08
01-JUL-08
01-AUG-08
01-SEP-08
01-OCT-08
01-NOV-08
01-DEC-08
01-JAN-09
01-FEB-09
01-MAR-09

15 rows selected.

SQL> l
  1  select a.index_name, b.index_type, a.column_name from user_ind_columns a, user_indexes b
  2  where a.index_name=b.index_name
  3  and   a.table_name=b.table_name
  4* and  a.table_name='MNTHLY_RSRVS'
SQL> /

INDEX_NAME                     INDEX_TYPE                  COLUMN_NAME
------------------------------ --------------------------- ------------------------------
I_MNTHLY_RSRVS_01              BITMAP                      REIN_DIRECTION_CD
I_MNTHLY_RSRVS_02              BITMAP                      REIN_PROC_CTGRY_CD
I_MNTHLY_RSRVS_03              BITMAP                      DOCUMENT_TYPE_CD
I_MNTHLY_RSRVS_04              BITMAP                      DOCUMENT_STATUS_CD
I_MNTHLY_RSRVS_05              NORMAL                      TRANSACTION_TYP_CD
I_MNTHLY_RSRVS_05              NORMAL                      TRANS_SUB_TYP_CD

6 rows selected.
CREATE TABLE ETL.MNTHLY_RSRVS
(
    GL_EFFECTIVE_DT            DATE         NOT NULL,
    ACCTNG_PERD_BEG_DT         DATE         NOT NULL,
    ACCTNG_PERD_END_DT         DATE         NOT NULL,
    DOCUMENT_STATUS_CD         VARCHAR2(1)      NULL,
    DOCUMENT_TYPE_CD           VARCHAR2(3)      NULL,
    ORPH_RJCT_IND              VARCHAR2(3)      NULL,
    RNDRD_UNRD_IND             VARCHAR2(1)      NULL,
    PREMIUM_LOSS_CD            VARCHAR2(1)      NULL,
    NM_CONTRACT_NO             NUMBER(10)       NULL,
    ORPHAN_STATUS_CD           VARCHAR2(1)      NULL,
    INCOMPLT_TREATY_IN         VARCHAR2(1)      NULL,
    MACH_NM_ORIG_CD            VARCHAR2(1)  NOT NULL,
    MIS013_COUNTRY_CD          NUMBER(5)        NULL,
    KIND_CD                    VARCHAR2(2)  NOT NULL,
    HOLD_IN                    VARCHAR2(1)      NULL,
    REIN_PROC_CTGRY_CD         VARCHAR2(3)  NOT NULL,
    REIN_DIRECTION_CD          VARCHAR2(3)  NOT NULL,
    REIN_RNDRG_FREQ_CD         NUMBER(22)       NULL,
    REIN_NO                    VARCHAR2(20)     NULL,
    REIN_UW_YR_NO              NUMBER(5)        NULL,
    REIN_EFCTV_DT              DATE             NULL,
    REIN_KEY_CD                VARCHAR2(6)      NULL,
    FAC_CERTIFICATE_NO         VARCHAR2(20)     NULL,
    XTRNL_REIN_REF_NO          VARCHAR2(50)     NULL,
    CAPTIVE_IN                 VARCHAR2(1)  NOT NULL,
    ASCO_MIS012_CUR_CD         NUMBER(22)   NOT NULL,
    NM_ADJUSTMENT_CD           NUMBER(5)        NULL,
    SOURCE_SYSTEM_ID           VARCHAR2(25)     NULL,
    MIS780_ASCO_CODE           VARCHAR2(2)  NOT NULL,
    POLICY_XPIRTN_DT           DATE             NULL,
    INSURED_NM                 VARCHAR2(90)     NULL,
    POLICY_NO                  VARCHAR2(10) NOT NULL,
    POLICY_EFCTV_DT            DATE         NOT NULL,
    MIS780_COMP_CODE           NUMBER(22)       NULL,
    MACH_NM                    VARCHAR2(2)      NULL,
    REIN_NO_LAYER_NO           VARCHAR2(2)      NULL,
    REIN_NO_SECTION_NO         VARCHAR2(2)      NULL,
    REIN_NO_FOREIGN_DOM_CD     VARCHAR2(1)      NULL,
    RELTNSHP_TYP_CD            VARCHAR2(3)      NULL,
    SOURCE_REIN_NO             VARCHAR2(20)     NULL,
    SRC_REIN_EFCTV_DT          DATE             NULL,
    SRC_REIN_UW_YR_NO          NUMBER(5)        NULL,
    SRC_REIN_KEY_CD            VARCHAR2(6)      NULL,
    SRC_REIN_NO_LAYER_NO       VARCHAR2(2)      NULL,
    SRC_REIN_NO_SECTION_NO     VARCHAR2(2)      NULL,
    SRC_REIN_NO_FOREIGN_DOM_CD VARCHAR2(1)      NULL,
    PUC_DSP_DIV                NUMBER(5)    NOT NULL,
    PUC_DSP_PUC                NUMBER(5)        NULL,
    PUC_DSP_SEC                NUMBER(5)        NULL,
    LOSS_DT                    DATE             NULL,
    DEP_DDSP_DEPT              NUMBER(5)        NULL,
    TERM_CONDITION_CD          VARCHAR2(3)      NULL,
    MATRIX_CD                  VARCHAR2(3)      NULL,
    MIS404_TAX_ST_CD           NUMBER(22)       NULL,
    CREDITED_BRANCH_NO         NUMBER(22)       NULL,
    WORKING_BRANCH_NO          NUMBER(22)       NULL,
    MIS020_CATAS_CD            NUMBER(5)        NULL,
    MIS020_CATAS_YR_NO         NUMBER(5)        NULL,
    CLAIM_BRANCH_NO            NUMBER(5)        NULL,
    CLAIM_CASE_NO              NUMBER(10)       NULL,
    KEY_BRANCH_NO              VARCHAR2(3)      NULL,
    KEY_CASE_NO                VARCHAR2(6)      NULL,
    CLA_ID                     VARCHAR2(22)     NULL,
    MIS028_MAJ_CLASS           NUMBER(5)    NOT NULL,
    MIS028_STMT_LINE           VARCHAR2(3)      NULL,
    TRANSACTION_TYP_CD         NUMBER(5)    NOT NULL,
    TRANS_SUB_TYP_CD           NUMBER(5)    NOT NULL,
    GEN_LEDGER_ACCT_NO         NUMBER(10)   NOT NULL,
    REINSURER_NO               VARCHAR2(6)  NOT NULL,
    REINSURER_BRNCH_CD         VARCHAR2(3)  NOT NULL,
    INTERMEDIARY_NO            VARCHAR2(6)      NULL,
    INTMDRY_BRNCH_CD           VARCHAR2(3)      NULL,
    PARTICIPANT_TYP_CD         VARCHAR2(3)      NULL,
    BALSHT_GL_ACCT_NO          NUMBER(22)   NOT NULL,
    MACH_NM_ORIG_TS            TIMESTAMP(6)     NULL,
    COUNT_KEYS                 NUMBER(22)       NULL,
    BEG_ASCO_FINANCIAL_AM      NUMBER(20,4)     NULL,
    END_ASCO_FINANCIAL_AM      NUMBER(20,4)     NULL,
    PROP_GL_DT                 DATE             NULL,
    REAL_IN                    VARCHAR2(2)      NULL,
    LOAD_DATE                  DATE             NULL
)
TABLESPACE WIP_DATA
NOLOGGING
PCTFREE 10
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE(INITIAL 80K
        MINEXTENTS 1
        MAXEXTENTS UNLIMITED
        BUFFER_POOL DEFAULT)
PARALLEL(DEGREE 4 INSTANCES 1)
NOCACHE
PARTITION BY RANGE(GL_EFFECTIVE_DT)
SUBPARTITION BY LIST(SOURCE_SYSTEM_ID)
SUBPARTITION TEMPLATE
    (SUBPARTITION CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA,
     SUBPARTITION CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA,
     SUBPARTITION CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA,
     SUBPARTITION CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA,
     SUBPARTITION OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA) 
(PARTITION MR_MINVAL VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG_MINVAL
    (SUBPARTITION MR_MINVAL_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MINVAL,
     SUBPARTITION MR_MINVAL_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MINVAL,
     SUBPARTITION MR_MINVAL_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MINVAL,
     SUBPARTITION MR_MINVAL_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MINVAL,
     SUBPARTITION MR_MINVAL_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MINVAL),
 PARTITION MR_200701 VALUES LESS THAN (TO_DATE(' 2007-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200701
    (SUBPARTITION MR_200701_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200701,
     SUBPARTITION MR_200701_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200701,
     SUBPARTITION MR_200701_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200701,
     SUBPARTITION MR_200701_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200701,
     SUBPARTITION MR_200701_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200701),
 PARTITION MR_200702 VALUES LESS THAN (TO_DATE(' 2007-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200702
    (SUBPARTITION MR_200702_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200702,
     SUBPARTITION MR_200702_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200702,
     SUBPARTITION MR_200702_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200702,
     SUBPARTITION MR_200702_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200702,
     SUBPARTITION MR_200702_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200702),
 PARTITION MR_200703 VALUES LESS THAN (TO_DATE(' 2007-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200703
    (SUBPARTITION MR_200703_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200703,
     SUBPARTITION MR_200703_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200703,
     SUBPARTITION MR_200703_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200703,
     SUBPARTITION MR_200703_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200703,
     SUBPARTITION MR_200703_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200703),
 PARTITION MR_200704 VALUES LESS THAN (TO_DATE(' 2007-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200704
    (SUBPARTITION MR_200704_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200704,
     SUBPARTITION MR_200704_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200704,
     SUBPARTITION MR_200704_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200704,
     SUBPARTITION MR_200704_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200704,
     SUBPARTITION MR_200704_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200704),
 PARTITION MR_200705 VALUES LESS THAN (TO_DATE(' 2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200705
    (SUBPARTITION MR_200705_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200705,
     SUBPARTITION MR_200705_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200705,
     SUBPARTITION MR_200705_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200705,
     SUBPARTITION MR_200705_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200705,
     SUBPARTITION MR_200705_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200705),
 PARTITION MR_200706 VALUES LESS THAN (TO_DATE(' 2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200706
    (SUBPARTITION MR_200706_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200706,
     SUBPARTITION MR_200706_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200706,
     SUBPARTITION MR_200706_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200706,
     SUBPARTITION MR_200706_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200706,
     SUBPARTITION MR_200706_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200706),
 PARTITION MR_200707 VALUES LESS THAN (TO_DATE(' 2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200707
    (SUBPARTITION MR_200707_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200707,
     SUBPARTITION MR_200707_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200707,
     SUBPARTITION MR_200707_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200707,
     SUBPARTITION MR_200707_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200707,
     SUBPARTITION MR_200707_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200707),
 PARTITION MR_200708 VALUES LESS THAN (TO_DATE(' 2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200708
    (SUBPARTITION MR_200708_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200708,
     SUBPARTITION MR_200708_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200708,
     SUBPARTITION MR_200708_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200708,
     SUBPARTITION MR_200708_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200708,
     SUBPARTITION MR_200708_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200708),
 PARTITION MR_200709 VALUES LESS THAN (TO_DATE(' 2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200709
    (SUBPARTITION MR_200709_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200709,
     SUBPARTITION MR_200709_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200709,
     SUBPARTITION MR_200709_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200709,
     SUBPARTITION MR_200709_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200709,
     SUBPARTITION MR_200709_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200709),
 PARTITION MR_200710 VALUES LESS THAN (TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200710
    (SUBPARTITION MR_200710_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200710,
     SUBPARTITION MR_200710_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200710,
     SUBPARTITION MR_200710_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200710,
     SUBPARTITION MR_200710_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200710,
     SUBPARTITION MR_200710_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200710),
 PARTITION MR_200711 VALUES LESS THAN (TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200711
    (SUBPARTITION MR_200711_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200711,
     SUBPARTITION MR_200711_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200711,
     SUBPARTITION MR_200711_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200711,
     SUBPARTITION MR_200711_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200711,
     SUBPARTITION MR_200711_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200711),
 PARTITION MR_200712 VALUES LESS THAN (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200712
    (SUBPARTITION MR_200712_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200712,
     SUBPARTITION MR_200712_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200712,
     SUBPARTITION MR_200712_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200712,
     SUBPARTITION MR_200712_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200712,
     SUBPARTITION MR_200712_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200712),
 PARTITION MR_200801 VALUES LESS THAN (TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200801
    (SUBPARTITION MR_200801_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200801,
     SUBPARTITION MR_200801_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200801,
     SUBPARTITION MR_200801_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200801,
     SUBPARTITION MR_200801_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200801,
     SUBPARTITION MR_200801_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200801),
 PARTITION MR_200802 VALUES LESS THAN (TO_DATE(' 2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200802
    (SUBPARTITION MR_200802_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200802,
     SUBPARTITION MR_200802_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200802,
     SUBPARTITION MR_200802_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200802,
     SUBPARTITION MR_200802_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200802,
     SUBPARTITION MR_200802_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200802),
 PARTITION MR_200803 VALUES LESS THAN (TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200803
    (SUBPARTITION MR_200803_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200803,
     SUBPARTITION MR_200803_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200803,
     SUBPARTITION MR_200803_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200803,
     SUBPARTITION MR_200803_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200803,
     SUBPARTITION MR_200803_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200803),
 PARTITION MR_200804 VALUES LESS THAN (TO_DATE(' 2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200804
    (SUBPARTITION MR_200804_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200804,
     SUBPARTITION MR_200804_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200804,
     SUBPARTITION MR_200804_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200804,
     SUBPARTITION MR_200804_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200804,
     SUBPARTITION MR_200804_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200804),
 PARTITION MR_200805 VALUES LESS THAN (TO_DATE(' 2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200805
    (SUBPARTITION MR_200805_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200805,
     SUBPARTITION MR_200805_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200805,
     SUBPARTITION MR_200805_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200805,
     SUBPARTITION MR_200805_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200805,
     SUBPARTITION MR_200805_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200805),
 PARTITION MR_200806 VALUES LESS THAN (TO_DATE(' 2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG200806
    (SUBPARTITION MR_200806_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG200806,
     SUBPARTITION MR_200806_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG200806,
     SUBPARTITION MR_200806_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG200806,
     SUBPARTITION MR_200806_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG200806,
     SUBPARTITION MR_200806_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG200806),
 PARTITION MR_200807 VALUES LESS THAN (TO_DATE(' 2008-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG_MAXVAL
    (SUBPARTITION MR_200807_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200807_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200807_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200807_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200807_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
 PARTITION MR_200808 VALUES LESS THAN (TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG_MAXVAL
    (SUBPARTITION MR_200808_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200808_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200808_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200808_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200808_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
 PARTITION MR_200809 VALUES LESS THAN (TO_DATE(' 2008-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG_MAXVAL
    (SUBPARTITION MR_200809_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200809_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200809_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200809_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200809_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
 PARTITION MR_200810 VALUES LESS THAN (TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG_MAXVAL
    (SUBPARTITION MR_200810_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200810_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200810_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200810_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200810_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
 PARTITION MR_200811 VALUES LESS THAN (TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG_MAXVAL
    (SUBPARTITION MR_200811_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200811_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200811_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200811_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200811_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
 PARTITION MR_200812 VALUES LESS THAN (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG_MAXVAL
    (SUBPARTITION MR_200812_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200812_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200812_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200812_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_200812_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL),
 PARTITION MR_200901 VALUES LESS THAN (TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PARTG_MAXVAL
    (SUBPARTITION MR_200901_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200901_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200901_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200901_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200901_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q1),
 PARTITION MR_200902 VALUES LESS THAN (TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PART2009Q1
    (SUBPARTITION MR_200902_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200902_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200902_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200902_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200902_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q1),
 PARTITION MR_200903 VALUES LESS THAN (TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PART2009Q1
    (SUBPARTITION MR_200903_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200903_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200903_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200903_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q1,
     SUBPARTITION MR_200903_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q1),
 PARTITION MR_200904 VALUES LESS THAN (TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PART2009Q2
    (SUBPARTITION MR_200904_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200904_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200904_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200904_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200904_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q2),
 PARTITION MR_200905 VALUES LESS THAN (TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PART2009Q2
    (SUBPARTITION MR_200905_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200905_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200905_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200905_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200905_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q2),
 PARTITION MR_200906 VALUES LESS THAN (TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    TABLESPACE WIP_DATA_PART2009Q2
    (SUBPARTITION MR_200906_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200906_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200906_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200906_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PART2009Q2,
     SUBPARTITION MR_200906_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PART2009Q2),
 PARTITION MR_MAXVAL VALUES LESS THAN (MAXVALUE)
    TABLESPACE WIP_DATA_PARTG_MAXVAL
    (SUBPARTITION MR_MAXVAL_CEDED1 VALUES('UGC', 'UPS', 'RDPS', 'AIU', 'WIL', 'PPB', 'HSB', 'GPU', 'AIPSO', 'LMS', 'MGA') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_MAXVAL_CEDED2 VALUES('AH1', 'LR1', 'PMS', 'SUR', 'ESP', 'DMS', 'AIQ', 'NHG', ' ') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_MAXVAL_CEDED3 VALUES('PDE', 'EXS', 'PPS', 'AIV') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_MAXVAL_CEDED4 VALUES('AH2', 'TBK', 'TPR') TABLESPACE WIP_DATA_PARTG_MAXVAL,
     SUBPARTITION MR_MAXVAL_OTHERS VALUES(DEFAULT) TABLESPACE WIP_DATA_PARTG_MAXVAL))

Tom Kyte
April 01, 2009 - 4:43 pm UTC

sorry, this is not going to work, I cannot see the entire plan, I cannot see the entire table (32k limit, anything bigger than that - I don't really want, too much information)


basically, since gldate (the table) isn't even visible in the above plan, the optimizer must have decided to defer the inlist processing till later. Look at the estimated card=value on that table in your plan, is it even close to reality?


and it would be 15 partitions - you have 160 subpartitions, how many of the subpartitions would you be hitting?

the partitions do not really exist, they are logical containers - how many of the 160 subpartitions are you going to hit with this.

Partition pruning predicate

Peter Tran, July 22, 2009 - 1:55 pm UTC

Hello Tom,

Is one of the requirement for the optimizer to apply partition pruning is the partition column must be in the predicate and used directly with a value rather than part of a join condition?

Take for example, I have a really big fact table. It's partitioned monthly on a date column across 3 years (2007, 2008, 2009), so we have 36 partitions.

Now I want to find the records for a given quarter, say Q1/2008. Technically, I would only need to scan 3 of out 36 partitions to get to the data I need.

However, I noticed that Oracle will not partition prune if I join to another table and this other table applies my filtering condition for Q1/2008.

The following predicate will apply the partition pruning:
WHERE FACT.DATE_ID IN ( <list every single day in Q1/2008> )

The following will NOT apply partition pruning:

WHERE FACT.DATE_ID = DIMENSION.DATE_ID
AND DIMENSION.QUARTER = 'Q1/2008';

Note, DATE_ID is a DATE data type in both tables (FACT, DIMENSION).

Thanks,
-Peter
Tom Kyte
July 26, 2009 - 6:48 am UTC

no creates, no example, no look

Can it partition eliminate? Sure - you might need a dimension (read about create dimension) to describe the relationships - but you give NOTHING to work with

Sorry...

Peter Tran, July 28, 2009 - 11:58 pm UTC


Hi Tom,

Sorry, I was just looking for a quick yes or no. I didn't want you to spend too much time looking at the details yet.

Oracle support asked me to put together a test case to prove to them that Oracle can do partition pruning with a join. They don't think it's possible.

I'm putting together a test case now.

I'll post the example once I get it working. Thanks for the quick answer.

Best regards,
-Peter
Tom Kyte
July 29, 2009 - 12:40 am UTC

well, my first response would be "describe to me what you think a partition wise join might be doing then"


ops$tkyte%ORA11GR1> CREATE TABLE t1
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  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%ORA11GR1> create table t2
  2  ( x int primary key,
  3    y date
  4  )
  5  /

Table created.

ops$tkyte%ORA11GR1> set linesize 1000
ops$tkyte%ORA11GR1> set autotrace traceonly explain
ops$tkyte%ORA11GR1> select * from t1, t2 where t1.dt = t2.y and t2.x = 5;

Execution Plan
----------------------------------------------------------
Plan hash value: 3094189031

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    61 |     2   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                |              |     1 |    61 |     2   (0)| 00:00:01 |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2           |     1 |    22 |     1   (0)| 00:00:01 |       |       |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0018547 |     1 |       |     1   (0)| 00:00:01 |       |       |
|   4 |   PARTITION RANGE ITERATOR   |              |     1 |    39 |     1   (0)| 00:00:01 |   KEY |   KEY |
|*  5 |    TABLE ACCESS FULL         | T1           |     1 |    39 |     1   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------

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

   3 - access("T2"."X"=5)
   5 - filter("T1"."DT"="T2"."Y")

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA11GR1> set autotrace off



there is an example of a join doing partition elimination - see the key key in there - that is partition elimination. It says "we'll eliminate partitions at runtime - we don't know right now what partition we'll hit - hence the key/key - but we will eliminate at runtime"


Why oracle is not using Subpartition stats

koshal, June 02, 2010 - 4:22 pm UTC

17:17:50 xxx.WORLD> SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS
FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_owner='SS_MASTER' AND SUBPARTITION_NAME='PCFS_P08_SP08'
17:17:50 2 /

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ -------
PKT_COLT_FNCL_ST PCFS_P08 PCFS_P08_SP08 1635762

Elapsed: 00:00:00.40
17:17:52 xxx.WORLD> SET AUTOTRACE ON
17:18:16 xxx.WORLD> SELECT COUNT(*),COUNT(DISTINCT CREN_DT) FROM PKT_COLT_FNCL_s
T SUBPARTITION (PCFS_P08_SP08);

COUNT(*) COUNT(DISTINCTCREN_DT)
---------- ----------------------
1635762 90

Elapsed: 00:00:10.06

Execution Plan
----------------------------------------------------------
Plan hash value: 388292166

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 190K (2)| 00:29:36 | | |
| 1 | SORT GROUP BY | | 1 | 8 | | | | |
| 2 | PARTITION COMBINED ITERATOR| | 27M| 208M| 190K (2)| 00:29:36 | KEY | KEY |
| 3 | TABLE ACCESS FULL | PKT_COLT_FNCL_ST | 27M| 208M| 190K (2)| 00:29:36 | 120
----------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
48108 consistent gets
47974 physical reads
0 redo size
275 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

In the explain plan why is it expecting 27Million. At Partition level we have 27 Million at subpartition level 1.6 Million.

Partition Pruning

A reader, June 05, 2010 - 5:09 pm UTC

Hi Tom,
If a table is range partitioned on a month key, and data may be selected based upon month key and other keys - account and customer. Will creating local index on month key, account and customer keys (in sequence) be beneficial? as oracle will do partition pruning on month key.
Thanks

Tom Kyte
June 09, 2010 - 8:11 am UTC

if the month key is constant in the partition (eg: it is always 01-jan-2010 for a given partition), then including it in a local index would not be beneficial in most all cases.

a query:

select * from t where month_key = ? and account = ? and customer = ?;

would just need a locally partitioned index on (account,customer). We can do partition pruning on this index because we know it is equi-partitioned with the table and we know that month_key = ? will result in a single partition.


However, a query such as :


select month_key, account, customer
from t
where month_key between ? and ? and account = ? and customer = ?

would benefit from an index on (account, customer, month_key) - just so we can avoid going to the table at all (note that month_key is last on purpose).

We can partition eliminate on that local non-prefixed index for the between bit - and by having the month key in the index, we can avoid the table altogether and get the answer from the index alone.



but in most cases, month_key would not need to be in the index if month_key is referenced in the predicate - we'll be able to partition eliminate regardless.

Full Partition-Wise Joins

Rajeshwaran, Jeyabal, January 26, 2011 - 6:46 am UTC

Tom:

I am reading about Full Partition-Wise joins from product documentation.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/parpart.htm#sthref222


rajesh@10GR2> create table customers
  2  (
  3     CUST_ID                                                 ,
  4     CUST_FIRST_NAME                                 ,
  5     CUST_LAST_NAME                                  ,
  6     CUST_GENDER                                             ,
  7     CUST_DATE                                               ,
  8     CUST_POSTAL_CODE                                ,
  9     CUST_CITY                                               ,
 10     CUST_TOTAL
 11  )
 12  partition by range(CUST_DATE)
 13  subpartition by hash(cust_id)
 14  subpartition template
 15  (
 16     subpartition sp1,
 17     subpartition sp2,
 18     subpartition sp3,
 19     subpartition sp4
 20  )
 21  (
 22     partition p_q1_2005  values less than ( to_date('01-apr-2005','dd-mon-yyyy') ),
 23     partition p_q2_2005  values less than ( to_date('01-jul-2005','dd-mon-yyyy') ),
 24     partition p_q3_2005  values less than ( to_date('01-oct-2005','dd-mon-yyyy') ),
 25     partition p_q4_2005  values less than ( to_date('01-jan-2006','dd-mon-yyyy') ),
 26     partition p_q1_2006  values less than ( to_date('01-apr-2006','dd-mon-yyyy') ),
 27     partition p_q2_2006  values less than ( to_date('01-jul-2006','dd-mon-yyyy') ),
 28     partition p_q3_2006  values less than ( to_date('01-oct-2006','dd-mon-yyyy') ),
 29     partition p_q4_2006  values less than ( to_date('01-jan-2007','dd-mon-yyyy') ),
 30     partition p_q1_2007  values less than ( to_date('01-apr-2007','dd-mon-yyyy') ),
 31     partition p_q2_2007  values less than ( to_date('01-jul-2007','dd-mon-yyyy') ),
 32     partition p_q3_2007  values less than ( to_date('01-oct-2007','dd-mon-yyyy') ),
 33     partition p_q4_2007  values less than ( to_date('01-jan-2008','dd-mon-yyyy') ),
 34     partition p_q1_2008  values less than ( to_date('01-apr-2008','dd-mon-yyyy') ),
 35     partition p_q2_2008  values less than ( to_date('01-jul-2008','dd-mon-yyyy') ),
 36     partition p_q3_2008  values less than ( to_date('01-oct-2008','dd-mon-yyyy') ),
 37     partition p_q4_2008  values less than ( to_date('01-jan-2009','dd-mon-yyyy') ),
 38     partition p_q1_2009  values less than ( to_date('01-apr-2009','dd-mon-yyyy') ),
 39     partition p_q2_2009  values less than ( to_date('01-jul-2009','dd-mon-yyyy') ),
 40     partition p_q3_2009  values less than ( to_date('01-oct-2009','dd-mon-yyyy') ),
 41     partition p_q4_2009  values less than ( to_date('01-jan-2010','dd-mon-yyyy') ),
 42     partition p_q1_2010  values less than ( to_date('01-apr-2010','dd-mon-yyyy') ),
 43     partition p_q2_2010  values less than ( to_date('01-jul-2010','dd-mon-yyyy') ),
 44     partition p_q3_2010  values less than ( to_date('01-oct-2010','dd-mon-yyyy') ),
 45     partition p_q4_2010  values less than ( to_date('01-jan-2011','dd-mon-yyyy') ),
 46     partition p_q1_2011  values less than ( to_date('01-apr-2011','dd-mon-yyyy') ),
 47     partition p_q2_2011  values less than ( to_date('01-jul-2011','dd-mon-yyyy') ),
 48     partition p_q3_2011  values less than ( to_date('01-oct-2011','dd-mon-yyyy') ),
 49     partition p_q4_2011  values less than ( to_date('01-jan-2012','dd-mon-yyyy') )
 50  )
 51  nologging
 52  as
 53  select     CUST_ID                 ,
 54             CUST_FIRST_NAME ,
 55             CUST_LAST_NAME  ,
 56             CUST_GENDER             ,
 57             CUST_DATE               ,
 58             CUST_POSTAL_CODE ,
 59             CUST_CITY               ,
 60             CUST_TOTAL
 61  from customers_data;

Table created.

Elapsed: 00:00:16.24
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'CUSTOMERS');

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.64
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> create table sales
  2  (
  3     SALES_ID                ,
  4     CUST_ID                 ,
  5     QUANTITY_SOLD   ,
  6     AMOUNT_SOLD             ,
  7     CUST_DATE
  8  )
  9  partition by range(CUST_DATE)
 10  subpartition by hash(cust_id)
 11  subpartition template
 12  (
 13     subpartition sp1,
 14     subpartition sp2,
 15     subpartition sp3,
 16     subpartition sp4
 17  )
 18  (
 19     partition p_q1_2005  values less than ( to_date('01-apr-2005','dd-mon-yyyy') ),
 20     partition p_q2_2005  values less than ( to_date('01-jul-2005','dd-mon-yyyy') ),
 21     partition p_q3_2005  values less than ( to_date('01-oct-2005','dd-mon-yyyy') ),
 22     partition p_q4_2005  values less than ( to_date('01-jan-2006','dd-mon-yyyy') ),
 23     partition p_q1_2006  values less than ( to_date('01-apr-2006','dd-mon-yyyy') ),
 24     partition p_q2_2006  values less than ( to_date('01-jul-2006','dd-mon-yyyy') ),
 25     partition p_q3_2006  values less than ( to_date('01-oct-2006','dd-mon-yyyy') ),
 26     partition p_q4_2006  values less than ( to_date('01-jan-2007','dd-mon-yyyy') ),
 27     partition p_q1_2007  values less than ( to_date('01-apr-2007','dd-mon-yyyy') ),
 28     partition p_q2_2007  values less than ( to_date('01-jul-2007','dd-mon-yyyy') ),
 29     partition p_q3_2007  values less than ( to_date('01-oct-2007','dd-mon-yyyy') ),
 30     partition p_q4_2007  values less than ( to_date('01-jan-2008','dd-mon-yyyy') ),
 31     partition p_q1_2008  values less than ( to_date('01-apr-2008','dd-mon-yyyy') ),
 32     partition p_q2_2008  values less than ( to_date('01-jul-2008','dd-mon-yyyy') ),
 33     partition p_q3_2008  values less than ( to_date('01-oct-2008','dd-mon-yyyy') ),
 34     partition p_q4_2008  values less than ( to_date('01-jan-2009','dd-mon-yyyy') ),
 35     partition p_q1_2009  values less than ( to_date('01-apr-2009','dd-mon-yyyy') ),
 36     partition p_q2_2009  values less than ( to_date('01-jul-2009','dd-mon-yyyy') ),
 37     partition p_q3_2009  values less than ( to_date('01-oct-2009','dd-mon-yyyy') ),
 38     partition p_q4_2009  values less than ( to_date('01-jan-2010','dd-mon-yyyy') ),
 39     partition p_q1_2010  values less than ( to_date('01-apr-2010','dd-mon-yyyy') ),
 40     partition p_q2_2010  values less than ( to_date('01-jul-2010','dd-mon-yyyy') ),
 41     partition p_q3_2010  values less than ( to_date('01-oct-2010','dd-mon-yyyy') ),
 42     partition p_q4_2010  values less than ( to_date('01-jan-2011','dd-mon-yyyy') ),
 43     partition p_q1_2011  values less than ( to_date('01-apr-2011','dd-mon-yyyy') ),
 44     partition p_q2_2011  values less than ( to_date('01-jul-2011','dd-mon-yyyy') ),
 45     partition p_q3_2011  values less than ( to_date('01-oct-2011','dd-mon-yyyy') ),
 46     partition p_q4_2011  values less than ( to_date('01-jan-2012','dd-mon-yyyy') )
 47  )
 48  nologging
 49  as
 50  select     SALES_ID                ,
 51             CUST_ID                 ,
 52             QUANTITY_SOLD   ,
 53             AMOUNT_SOLD             ,
 54             CUST_DATE
 55  from sales_data;

Table created.

Elapsed: 00:07:03.90

rajesh@10GR2> alter table customers add constraint cust_pk primary key(cust_id);

Table altered.

Elapsed: 00:00:11.01
rajesh@10GR2> alter table sales add constraint sales_fk foreign key(cust_id) references customers;

Table altered.

Elapsed: 00:00:19.17



rajesh@10GR2> SELECT c.* , s.*
  2  from customers c, sales s
  3  where c.cust_id = s.cust_id
  4  and c.cust_date between  to_date('01-jul-2005','dd-mon-yyyy')
  5  and to_date('30-sep-2005','dd-mon-yyyy');
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3704956898

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |    11M|   907M|       | 39198   (2)| 00:07:51 |       |       |
|   1 |  PARTITION HASH ALL      |           |    11M|   907M|       | 39198   (2)| 00:07:51 |     1 |     4 |
|*  2 |   HASH JOIN              |           |    11M|   907M|    15M| 39198   (2)| 00:07:51 |       |       |
|   3 |    PARTITION RANGE SINGLE|           |   929K|    51M|       |  1875   (2)| 00:00:23 |     3 |     3 |
|*  4 |     TABLE ACCESS FULL    | CUSTOMERS |   929K|    51M|       |  1875   (2)| 00:00:23 |     9 |    12 |
|   5 |    PARTITION RANGE ALL   |           |    11M|   306M|       | 12125   (2)| 00:02:26 |     1 |    28 |
|   6 |     TABLE ACCESS FULL    | SALES     |    11M|   306M|       | 12125   (2)| 00:02:26 |     1 |   112 |
--------------------------------------------------------------------------------------------------------------

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

   2 - access("C"."CUST_ID"="S"."CUST_ID")
   4 - filter("C"."CUST_DATE"<=TO_DATE('2005-09-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

rajesh@10GR2>



Questions

1) All HASH Partition in SALES table is scanned (this is shown in plan as Pstart = 1 and Pstop = 112 at Id=6)

2) Only 4 Hash Partition in CUSTOMERS table corresponding to 2005, Q3 is scanned (this is shown in plan as Pstart = 9 and Pstop = 12 at Id=4)

3) Does, the Access path PARTITION HASH ALL from plan says

a) only the 4 Hash Partition from CUSTOMERS table is joined (Full Partition-Wise Joins) with 4 Hash Partition from sales table (this is shown in plan as Pstart = 1 and Pstop = 4 at Id=1)

(or)

b)only the 4 Hash Partition from CUSTOMERS table is joined (Full Partition-Wise Joins) with 112 Hash Partitions in Sales tables (as shown in plan at Id=6)?


Please correct me if my understanding is wrong. ( But somewhat confused with question (3), looking for your answers )

Full Partition-Wise Joins

Rajeshwaran, Jeyabal, February 02, 2011 - 2:36 pm UTC

Tom:

I Will try to keep my examples short and simple( to fit in a page). Can you please answer to the above question's (3)?
Tom Kyte
February 03, 2011 - 2:12 pm UTC

each of the 4 partitions from the customer table must be joined to 28 hash partitions in sales.

It is not 4 partitions in sales - it is one hash partition from each of the 28 range partitions they would have to be joined to.

Full Partition-Wise Joins

Rajeshwaran, Jeyabal, February 04, 2011 - 1:49 am UTC

Tom:

each of the 4 partitions from the customer table
This is very clear, since plan at id=4 says Pstart =9 and Pstop=12

It is not 4 partitions in sales - it is one hash partition from each of the 28 range partitions they would have to be joined to.
How do you say from the above plan?



Tom Kyte
February 04, 2011 - 9:31 am UTC

we are hitting every single partition in sales, that you pointed out.

And you know that there are 28 range partitions, each of which have four hash partitions (just like your customers table) and all 28 range partitions have to be hit.

Full Partition-Wise Joins

Rajeshwaran, Jeyabal, February 05, 2011 - 2:20 am UTC

Tom:

Sorry to bother you, I think i am still not clear with your answers.

you said it is one hash partition from each of the 28 range partitions they would have to be joined to

How do you confidently say this one hash partition?
a) I see at id = 6 there are 112 partitions scanned ( 28 Range * 4 Hash per range)
b) I see at id = 5 there are 28 Range partitions scanned ( by the access path PARTITION RANGE ALL, which means its scanned all RANGE Partitions on SALES table)
c) I see at id = 4 there are 4 Hash partitions scanned( only one RANGE partitions and all 4 Hash partition to that from CUSTOMERS table)

How do you confidently say this one hash partition ? which step in Explain plan shows you this (How did you identified this from the above plan) ?

Please help me !

Partition Pruning involving non-partition key column as a predicate

A reader, February 06, 2011 - 10:01 am UTC

Dear Tom,

Apropos review http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2259517028934#1838721200346459669,I'm looking at exactly the same issue in our DW system where so-called 'Dynamic Pruning' at run-time is happening and the partitioning strategy is thus:

1. Dimensions(SCD) and Facts are partitioned on time surrogate keys (sk)

2. Facts are joined to Time dimension via surrogate keys.

And all of our queries are of the format

Select sum(fact1.metric1),dim1.col1,timedim.month
from
fact1,dim1,timedim
where
dim1.sk = fact1.dim1sk
and dim1.timesk = fact.timesk
and fact1.timesk = timedim.sk
and timedim.month between ('June-2009') and ('Nov-2010)


Our DBAs have deemed this "inefficient' and want us to implement static pruning whereby they want us to partition facts on actual months (we'll have to add month column to facts) rather than time surrogate key and use the filter on the month column in fact (and not the one in Time dim).

I'm not quite sure this is the correct way to go about it as it still leaves the Dimensions still open to dynamic pruning via join conditions as in the query above.Obviously,we cannot have the month column in Dimensions as well since it will be very cumbersome for end-users (there is a reporting application sitting on top of our DW where users build ad-hoc queries)to include filters both for fact and dimensions.

I'd be grateful if you could suggest an appropriate solution option and/or comment on this situation.
Tom Kyte
February 06, 2011 - 12:35 pm UTC

tell them to provide science behind "inefficient" and suggestions as to how to repartition the data to be efficient. That is what I would do - otherwise you can just say to them "your backups are inefficient - make them better". You'd be even then wouldn't you?


However, I'm not a fan of unnecessary surrogate keys. 1-jan-2010 will always be 1-jan-2010, it will never change to 2-feb-2011. Therefore, your time dimensions never needed to have a surrogate, they should just be "the date". Then your partitioning scheme would be easier (easier to range partition on a date, then on some artificial number you have to figure out). Also, you'll sometimes (repeat SOMETIMES) be able to achieve static partition elimination (but not always - as you already know. If the user queries using an attribute of the time dimension that is not the primary key - like your example - it will still have to do the lookup first at runtime to figure out what the partition keys are)

Date Dimension

A reader, February 10, 2011 - 3:21 pm UTC

So for dates in a data warehouse, you think a date dimension table is still valid, but you would just join on the actual date value and make it the PK?

I like your idea because it makes the partitioning and queries very understandable, but I feel like everyone makes a date table out of habit.
Tom Kyte
February 14, 2011 - 7:04 am UTC

... but you would just join on the actual date value and make it the PK? ...

sure, 01-jan-2010 is going to be 01-jan-2010 - it is rather immutable.

... but I feel like everyone makes a date table out of habit. ...

you mean "everyone uses a surrogate - sometimes unnecessarily - out of habit". We'll still have a date table after all :)

Oracle database for DWH

A reader, February 14, 2011 - 6:09 am UTC

Hi Tom,

I think we need storage indexes in oracle database without oracle exadata.

Full Partition-Wise Joins

Rajeshwaran, Jeyabal, February 16, 2011 - 9:52 am UTC

Tom Kyte
February 16, 2011 - 11:27 am UTC

I closed my eyes and envisioned the layout of the data and said what it had to do at a minimum, that was my process on that one.

Draw it out on a piece of paper - you'll see what they *have* to hit at a minimum.


KEY - KEY

Vikas, March 21, 2011 - 1:13 am UTC

Hi tom,

When the explain plan states KEY-KEY as Pstart and Pstop columns,which essentially means Oracle's optimizer was not having enough information at comppile time what partitions do it need to scan.

Now when someone executes the query, how will he check whether Oracle is doing Partition pruning or not as per desired expectations.

Any V$ table can help in this.? Is ROW_WAIT_OBJ# the right column from gv$session to look forward to, but I have doubts on that.

Please confirm.

Thanks
Tom Kyte
March 21, 2011 - 9:50 am UTC

... Now when someone executes the query, how will he check whether Oracle is doing
Partition pruning or not as per desired expectations. ...

they already know it is doing partition pruning based on the data provided in the plan. If it were not pruning - you would see 1 and N in the start/stop where N was the number of total partitions.

In ISOLATION (single user mode), you could use v$segment_statistics to see which segments had IO performed against them.

KEY - KEY showing in the plan

Vikas, March 21, 2011 - 10:17 am UTC

Hi Tom,

Thanks for the answer. However the idea was to check what Oracle is doing behind the scenes when dynamic pruning is being performed.

Here the join to the FACT with the dimension is happening with dim.date_desc as one predicate and the join happens between the date_key. With Month being passed by the User input the plan shows KEY - KEY with PX BLOCK ITERATOR but in the filter clause it mentions the Min(date_Key) and MAx(date_key) from the dimension.

I am just curious to find how to find what partitions oracle is scanning to carry out a HJ. We can see them in the gv$session_longops but eventually it just notifies the table_name but not the partition_name.

I am feeling based on the data we have, Oracle somehow is not doing the Partition elimination and we are scanning the complete FACT to report the results.

Fact : (MC Partitioning) : DATE_KEY + PROPERTY_KEY
Dimension : Date (Date_Key) being the Join key.

That was the reason I asked can you check at run time what partitions oracle is scanning.

thanks
Tom Kyte
March 21, 2011 - 10:32 am UTC

... Oracle somehow is not doing the
Partition elimination and we are scanning the complete FACT to report the
results. ...

that would only be true if you are running a query that causes us to hit all partitions - which can happen with key-key plans - but it would be because your inputs made us do it.


11g - sql monitoring would be enlightening.

or as I said, in your test system - in isolation - just snapshot v$segment_statistics, run your query and dump out the differences in logical IO's against the segments in question.

Pruning simulation

Luiz Noronha, June 06, 2011 - 1:50 pm UTC

Tom,

How can I query partitions by its high_value?
I mean, I have several tables partitioned by date range and I would like to write query that selects max and min position values for a given date range, quite similar to what Oracle does when it does the pruning (PSTART and PSTOP).

Thank you very much.

Tom Kyte
June 06, 2011 - 3:04 pm UTC

I don't know what you mean - are you saying "given a date range, report all of the partitions that would fall into that range - or actually just the start and stop partitions in that range"?

I'm not sure what max/min position values means

Partition pruning

Luiz Noronha, June 06, 2011 - 3:46 pm UTC

I'm sorry if I was not clear..

Let's assume that I have a table that is range partitioned in a date field sales_date and I need to know which partitions stores data from January/2010 to January/2011.

I know that the high_value (DBA_TAB_PARTITIONS) field stores this kind of information and my issue here is that I dont't know how to filter it because of its format.. That's why I mentioned the pruning, because Oracle "knows" the partition position range it has to scan based on the WHERE clause...

How can I query the DBA_TAB_PARTITIONS in order to obtain this information?

Tom Kyte
June 06, 2011 - 5:27 pm UTC

you can use a user defined function to coerce the type appropriately:

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  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%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace function get_high_value_date
  2  ( p_owner in all_tab_partitions.table_owner%type,
  3    p_tname in all_tab_partitions.table_name%type,
  4    p_pname in all_tab_partitions.partition_name%type
  5  )
  6  return date
  7  as
  8          l_high_value long;
  9          l_date date;
 10  begin
 11          begin
 12          select high_value
 13            into l_high_value
 14            from all_tab_partitions
 15           where table_owner = p_owner
 16             and table_name = p_tname
 17             and partition_name = p_pname;
 18          exception
 19          when no_data_found
 20          then
 21                  raise_application_error( -20001, 'no data found' );
 22          end;
 23  
 24          if (l_high_value <> 'MAXVALUE')
 25          then
 26                  execute immediate 'select ' || l_high_value || ' from dual ' into l_date;
 27          else
 28                  l_date := to_date( '01-jan-9999', 'dd-mon-yyyy' );
 29          end if;
 30  
 31          return l_date;
 32  end;
 33  /

Function created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select table_name, partition_name, get_high_value_date( user, table_name, partition_name ) hv
  2    from user_tab_partitions
  3   where table_name = 'T'
  4  /

TABLE_NAME                     PARTITION_NAME                 HV
------------------------------ ------------------------------ --------------------
T                              JUNK                           01-jan-9999 00:00:00
T                              PART1                          13-mar-2003 00:00:00
T                              PART2                          14-mar-2003 00:00:00


Thank you Tom!

Luiz Noronha, June 07, 2011 - 7:58 am UTC

It worked perfectly!

Partition key and local index

A reader, June 30, 2011 - 8:00 am UTC

For a list partition table, should partition key be included in local index? Is there any different consideration if the partition key field in where clause uses literal or bind variable?

Thanks.
Tom Kyte
July 01, 2011 - 8:47 am UTC

It need not be in the local index.

if you query:

where partition_key = value|? and indexed_column <operator> <value>


We'll be able to partition eliminate all but one local index partition to perform the indexed_column <operator> <value> bit on.



Partition pruning and full partition scan

A reader, July 28, 2011 - 10:26 am UTC

Tom,
I have a table that is partitioned and has local indexes and if I use the right conditions ,I can do effective partition pruning.
However, if I add a parallel and full hint to speed up the query (some cases I am getting more that 10% of rows from the table), it looks like the query is not doing partition pruning anymore(from explain plan). Is that a true statement?
Is there any way to ensure that I do an full access on the partitions after doing partition pruning (so that I access less rows).
I am running oracle 10g2 on AIX

thanks for your help

Tom Kyte
July 28, 2011 - 7:34 pm UTC

Is that a true
statement?


without seeing the plan and the query and understanding the partitioning scheme, I cannot comment.

However, look at the plan - it should have a pstart/pstop (partition start stop). If it has 1 and N where N = number of partitions - no pruning. If it has anything else (including key/key) it is pruning.

Range partitioned scenario between vs equal

Prashant, December 06, 2011 - 4:40 am UTC

I noticed a strange behaviour (high LIOs / poor performance) when querying a range partitioned table. I used 2 types of joins.
1) Partitioned table doing an equality Join with Time to query a month data - High LIOs (28 times more. The number 28 is number of dates in TIME for that month)
2) Partitioned table doing an between Join with Month hierarchy to query the same data (Hits the exact buffers for the partition)

I read on some documentation that for effective partition pruning on range partitioned tables we should be passing literals directly to partitioned tables or using between clause. Do you agree? Is there any other approach to reduce the LIOs


My tests below

1. Table P_TRANS range partitioned on payment_dt (monthly)
2. Table P_TIME with list of dates and date attributes (month, quarter, year) (granularity: date)
3. Table P_PERIOD_MONTH Dimension Hierarchy from Time and attributes quarter, year (granularity: month)

Scenario A: Transaction and Time (Large number of buffers 28 * 3294 = 92232)
----------------------------------------------------------------------------

select distinct payment_dt from p_trans a, p_time b where a.payment_dt = b.cal_dt and b.quarter = '2011 Q 3' and
b.year = 2011 and b.month = '2011 / 08'


-------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows | A-Time |Buffers|Reads|
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 28 |00:00:13.68| 92238|92176|
| 1| SORT UNIQUE NOSORT | | 1| 2644| 28 |00:00:13.68| 92238|92176|
| 2| NESTED LOOPS | | 1| 12059| 211K|00:00:13.50| 92238|92176|
|*3| TABLE ACCESS BY INDEX ROWID|P_TIME | 1| 3| 28 |00:00:00.01| 6| 0|
|*4| INDEX RANGE SCAN |P_TIME_IND_02| 1| 1| 28 |00:00:00.01| 2| 0|
| 5| PARTITION RANGE ITERATOR | | 28| 3727| 211K|00:00:13.19| 92232|92176|
|*6| TABLE ACCESS FULL |P_TRANS | 28| 3727| 211K|00:00:12.89| 92232|92176|
-------------------------------------------------------------------------------------------------

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

3 - filter("B"."YEAR"=2011)
4 - access("B"."MONTH"='2011 / 08' AND "B"."QUARTER"='2011 Q 3')


Scenario B: Transaction and Month Hierarchy
----------------------------------------------------------------------------

select distinct payment_dt from p_trans a, p_period_month b where a.payment_dt between b.m_snap_start_date and
b.m_snap_end_date and quarter = '2011 Q 3' and year = '2011' and month = '2011 / 08'


-------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows| A-Time |Buffers|Reads|
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 28 |00:00:01.45| 3296| 3292|
| 1| HASH UNIQUE | | 1| 2644 | 28 |00:00:01.45| 3296| 3292|
| 2| NESTED LOOPS | | 1| 2159K| 211K|00:00:01.26| 3296| 3292|
|*3| TABLE ACCESS BY INDEX ROWID|P_PERIOD_MONTH| 1| 1 | 1 |00:00:00.01| 2| 0|
|*4| INDEX UNIQUE SCAN |P_MONTH_IND_01| 1| 1 | 1 |00:00:00.01| 1| 0|
| 5| PARTITION RANGE ITERATOR | | 1| 2159K| 211K|00:00:00.96| 3294| 3292|
|*6| TABLE ACCESS FULL |P_TRANS | 1| 2159K| 211K|00:00:00.64| 3294| 3292|
-------------------------------------------------------------------------------------------------

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

3 - filter(("QUARTER"='2011 Q 3' AND "YEAR"=2011))
4 - access("MONTH"='2011 / 08')
6 - filter(("A"."PAYMENT_DT">="B"."M_SNAP_START_DATE" AND "A"."PAYMENT_DT"<="B"."M_SNAP_END_DATE"))

Tom Kyte
December 06, 2011 - 11:26 am UTC

... I read on some documentation that for effective partition pruning on range
partitioned tables we should be passing literals directly to partitioned tables
or using between clause. Do you agree? ...

absolutely not, you get the identical amount of pruning regardless. The 'advantage' of a literal would be that the optimizer would know what partitions where being hit at optimize time and *might* sometimes be able to come up with a better plan.


... (28 times more. ..

28 times more than WHAT? I don't see anything to compare here????

Buffers compare

Prashant Shenava, December 06, 2011 - 9:33 pm UTC

Hi Tom

Please look at the buffers on Line 6 in both scenarios. In one case its 3294 (the exact number of buffers for that partition) while in another cases it 3294 * 28 = 92232. I don't understand why would it scan so many buffers when the partition that is being hit is only one?

Thanks
Prashant
Tom Kyte
December 07, 2011 - 1:14 pm UTC

well, one of them retrieved 28 rows in the nested loop and the other retrieved 1.

so, one of them looped ONCE, the other looped 28 times.

A nested loops is done sort of like procedural code, so

| 2|  NESTED LOOPS                |              |     1| 2159K|  211K|00:00:01.26|   3296| 3292| 
|*3|   TABLE ACCESS BY INDEX ROWID|P_PERIOD_MONTH|     1|    1 |    1 |00:00:00.01|      2|    0| 
|*4|    INDEX UNIQUE SCAN         |P_MONTH_IND_01|     1|    1 |    1 |00:00:00.01|      1|    0| 
| 5|   PARTITION RANGE ITERATOR   |              |     1| 2159K|  211K|00:00:00.96|   3294| 3292| 
|*6|    TABLE ACCESS FULL         |P_TRANS       |     1| 2159K|  211K|00:00:00.64|   3294| 3292| 


is done sort of like:

for x in (do lines 3 and 4)
loop
     do lines 5&6 - full scan
end loop


"the identical amount of pruning"

Sokrates, December 07, 2011 - 7:11 am UTC


you get the identical amount of pruning regardless

not in all circumstances, there are pitfalls:

create table pruningtest
(
  i int,
  d date,
  s int,
  data varchar2(1000),
  primary key(i, d)
)
partition by range( d )
(
  partition p1 values less than (date '2010-01-01'),
  partition p2 values less than (date '2011-01-01'),
  partition p3 values less than (date '2012-01-01'),
  partition p4 values less than (maxvalue)
)
/

exec dbms_stats.set_table_stats(user, 'PRUNINGTEST', numrows => 1e11)

create index i on pruningtest(s) local;

create view literal
as
select * from pruningtest
where d >= date '2011-12-01'
and s >= to_number(sys_context('search_s', 'start'));


create view notliteral
as
select * from pruningtest
where d >= sysdate-7
and s >= to_number(sys_context('search_s', 'start'));

set autotr traceonly explain

sokrates@11.2 > select * from literal;

Execution Plan
----------------------------------------------------------
Plan hash value: 2110551900

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |   250M|    23G|     0   (0)| 00:00:01 |    |  |
|   1 |  PARTITION RANGE ITERATOR          |             |   250M|    23G|     0   (0)| 00:00:01 |     3 |     4 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PRUNINGTEST |   250M|    23G|     0   (0)| 00:00:01 |     3 |     4 |
|*  3 |    INDEX RANGE SCAN                | I           |     1 |       |     0   (0)| 00:00:01 |     3 |     4 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("D">=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("S">=TO_NUMBER(SYS_CONTEXT('search_s','start')))


pruning at compile time

sokrates@11.2 > select * from notliteral;

Execution Plan
----------------------------------------------------------
Plan hash value: 2110551900

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |   250M|    23G|     0   (0)| 00:00:01 |    |  |
|   1 |  PARTITION RANGE ITERATOR          |             |   250M|    23G|     0   (0)| 00:00:01 |   KEY |     4 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PRUNINGTEST |   250M|    23G|     0   (0)| 00:00:01 |   KEY |     4 |
|*  3 |    INDEX RANGE SCAN                | I           |     1 |       |     0   (0)| 00:00:01 |   KEY |     4 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("D">=SYSDATE@!-7)
   3 - access("S">=TO_NUMBER(SYS_CONTEXT('search_s','start')))



identical amount of pruning at run time

Assume we have a job which regularily (every 5 seconds ) runs a
select * from notliteral


Now, we notice we want to do some partition maintenance ( for example "move compress" of very old partitions ).
For the time of the maintenance, these index partitions get unusable.

sokrates@11.2 > alter index i modify partition p1 unusable;

Index altered.


sokrates@11.2 > select * from literal;

Execution Plan
----------------------------------------------------------
Plan hash value: 2110551900

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |             |   250M|    23G|     0   (0)| 00:00:01 |    |  |
|   1 |  PARTITION RANGE ITERATOR          |             |   250M|    23G|     0   (0)| 00:00:01 |     3 |     4 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PRUNINGTEST |   250M|    23G|     0   (0)| 00:00:01 |     3 |     4 |
|*  3 |    INDEX RANGE SCAN                | I           |     1 |       |     0   (0)| 00:00:01 |     3 |     4 |
------------------------------------------------------------------------------------------------------------------

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

   2 - filter("D">=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("S">=TO_NUMBER(SYS_CONTEXT('search_s','start')))


Pruning as before, we are not affected by the maintenance, but

sokrates@11.2 > select * from notliteral;

Execution Plan
----------------------------------------------------------
Plan hash value: 2348254854

--------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |   250M|    23G|    12M(100)| 00:08:12 |       |       |
|   1 |  PARTITION RANGE ITERATOR|             |   250M|    23G|    12M(100)| 00:08:12 |   KEY |     4 |
|*  2 |   TABLE ACCESS FULL      | PRUNINGTEST |   250M|    23G|    12M(100)| 00:08:12 |   KEY |     4 |
--------------------------------------------------------------------------------------------------------

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

   2 - filter("S">=TO_NUMBER(SYS_CONTEXT('search_s','start')) AND "D">=SYSDATE@!-7)



no pruning anymore.

Is there another way to avoid that beside using literals ?
Tom Kyte
December 07, 2011 - 1:37 pm UTC

I'd call that sort of an edge case, I did say:


absolutely not, you get the identical amount of pruning regardless. The 'advantage' of a literal would be that the optimizer would know what partitions where being hit at optimize time and*might* sometimes be able to come up with a better plan.


the optimizer would have to know what partitions would actually be hit currently. Maybe some day it would be able to use the logic it uses with partially indexed tables with binds... where it comes up with two plans for different parts of the table..

Buffers compare followup

Prashant, December 07, 2011 - 10:42 pm UTC

Hi Tom

Thanks for your response that cleared up my understanding.
So with a scenario wherein TIME returns 28 dates all from a single month and we have month based fact partition (range partitioned by date). Is there any other execution path that will reduce the LIOs and force the optimizer to read the month partition only once?

Thanks
Prashant
Tom Kyte
December 08, 2011 - 12:46 pm UTC

what happens if you run this with dynamic sampling set up a bit higher?

http://www.oracle.com/technetwork/issue-archive/2009/09-jan/o19asktom-086775.html


what version of the database?

Dynamic Sampling results

Prashant, December 09, 2011 - 5:57 am UTC

Hi Tom

Version is 11.2.0.2.0

I tested with different levels of sampling but I didn't see any difference till I reached 8 and even that doesn't seem much different

dynamic sample = 2
-------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows| A-Time |Buffers|Reads |
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 28 |00:00:09.98| 92238| 92182|
| 1| SORT UNIQUE NOSORT | | 1| 2644| 28 |00:00:09.98| 92238| 92182|
| 2| NESTED LOOPS | | 1| 12059| 211K|00:00:09.79| 92238| 92182|
|*3| TABLE ACCESS BY INDEX ROWID|P_TIME | 1| 3| 28 |00:00:00.02| 6| 5|
|*4| INDEX RANGE SCAN |P_TIME_IND_02| 1| 1| 28 |00:00:00.01| 2| 2|
| 5| PARTITION RANGE ITERATOR | | 28| 3727| 211K|00:00:09.44| 92232| 92177|
|*6| TABLE ACCESS FULL |P_TRANS | 28| 3727| 211K|00:00:09.12| 92232| 92177|

dynamic sample = 8
-------------------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows| A-Time |Buffers|Reads |
-------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 28 |00:00:06.88| 92239| 92180|
| 1| HASH UNIQUE | | 1| 2644 | 28 |00:00:06.88| 92239| 92180|
| 2| NESTED LOOPS | | 1| 104K| 211K|00:00:06.67| 92239| 92180|
|*3| TABLE ACCESS BY INDEX ROWID|P_TIME | 1| 28 | 28 |00:00:00.01| 7| 4|
|*4| INDEX RANGE SCAN |P_TIME_IND_03| 1| 54 | 91 |00:00:00.01| 2| 2|
| 5| PARTITION RANGE ITERATOR | | 28| 3727 | 211K|00:00:06.33| 92232| 92176|
|*6| TABLE ACCESS FULL |P_TRANS | 28| 3727 | 211K|00:00:06.00| 92232| 92176|
-------------------------------------------------------------------------------------------------

I also see additional memory information for line 1 (dynamic sample = 8). I have pasted below since it spoils the formatting. Don't think it makes a difference to my original question but I thought of letting you know.
|OMem|1Mem|Used-Mem|
|728K|728K|1232K(0)|
Tom Kyte
December 11, 2011 - 2:40 pm UTC

give me a tiny schema to look at - create tables - just enough of the create to get the gist. If you get generate some data into these tables using some very simple insert as selects from all objects - that'd be great too - so I can reproduce your plan and play around. It looks like, on the face of things, it is doing the right thing already.

Scripts

Prashant, December 12, 2011 - 1:23 am UTC

CREATE TABLE p_trans
(
payment_dt DATE,
payment_name VARCHAR2(30),
pad VARCHAR2(400)
)
PARTITION BY RANGE
(
payment_dt
)
(
PARTITION P_TRANS_PART_MINVAL VALUES LESS THAN (TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION P_TRANS_PART_01 VALUES LESS THAN (TO_DATE(' 2011-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P_TRANS_PART_02 VALUES LESS THAN (TO_DATE(' 2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P_TRANS_PART_03 VALUES LESS THAN (TO_DATE(' 2011-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P_TRANS_PART_04 VALUES LESS THAN (TO_DATE(' 2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P_TRANS_PART_05 VALUES LESS THAN (TO_DATE(' 2011-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P_TRANS_PART_06 VALUES LESS THAN (TO_DATE(' 2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P_TRANS_PART_07 VALUES LESS THAN (TO_DATE(' 2011-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P_TRANS_PART_08 VALUES LESS THAN (TO_DATE(' 2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION P_TRANS_PART_09 VALUES LESS THAN (TO_DATE(' 2011-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION P_TRANS_PART_10 VALUES LESS THAN (TO_DATE(' 2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION P_TRANS_PART_11 VALUES LESS THAN (TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION P_TRANS_PART_12 VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) ,
PARTITION P_TRANS_PART_MAXVAL VALUES LESS THAN (MAXVALUE)
);

create table p_date (cal_dt date, month varchar2(10), quarter varchar2(10), year number);

insert into p_date
SELECT
cal_dt,
to_char (cal_dt,'YYYY') || ' / ' || to_char (cal_dt,'MM') month,
to_char (cal_dt,'YYYY') || ' Q ' || to_char (cal_dt,'Q') quarter,
to_char(cal_dt,'YYYY') year
FROM
(
SELECT level n,
TO_DATE('31/12/2008','DD/MM/YYYY') + NUMTODSINTERVAL(level,'day') cal_dt
FROM dual
CONNECT BY level <= 1095
);
--1095 rows created.
commit;

insert into p_trans select cal_dt, object_name,lpad(owner,400,'Z') from p_date, (select * from all_objects where rownum<20000) where year =2011;
--7299635 rows created.
commit;

select distinct payment_dt from p_trans a, p_date b where a.payment_dt = b.cal_dt and b.quarter = '2011 Q 2' and b.year = 2011 and b.month = '2011 / 04' ;

----------------------------------------------------------------------------------------
|Id|Operation |Name |Starts|E-Rows|A-Rows | A-Time |Buffers|Reads|
---------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| | 30 |00:00:23.05| 274K| 274K|
| 1| HASH UNIQUE | | 1| 365| 30 |00:00:23.05| 274K| 274K|
| 2| NESTED LOOPS | | 1| 16874| 599K|00:00:22.40| 274K| 274K|
|*3| TABLE ACCESS FULL |P_DATE | 1| 1| 30 |00:00:00.01| 4 | 0 |
| 4| PARTITION RANGE ITERATOR| | 30| 19999| 599K|00:00:21.45| 274K| 274K|
|*5| TABLE ACCESS FULL |P_TRANS| 30| 19999| 599K|00:00:20.49| 274K| 274K|
----------------------------------------------------------------------------------------

OMem | 1Mem | Used-Mem |
728K| 728K| 1129K (0)|

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

3 - filter(("B"."MONTH"='2011 / 04' AND "B"."QUARTER"='2011 Q 2' AND "B"."YEAR"=2011))
5 - filter("A"."PAYMENT_DT"="B"."CAL_DT")


---Additional information
select name,value from v$parameter where name = 'db_block_size';

NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
db_block_size 32768


select partition_name,num_rows,blocks,avg_row_len from user_tab_partitions where table_name ='P_TRANS';

PARTITION_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ ---------- ---------- -----------
P_TRANS_PART_01 619969 9412 432
P_TRANS_PART_02 559972 8647 432
P_TRANS_PART_03 619969 9412 432
P_TRANS_PART_04 599970 9157 432
P_TRANS_PART_05 619969 9412 432
P_TRANS_PART_06 599970 9157 432
P_TRANS_PART_07 619969 9412 432
P_TRANS_PART_08 619969 9412 432
P_TRANS_PART_09 599970 9157 432
P_TRANS_PART_10 619969 9412 432
P_TRANS_PART_11 599970 9157 432
P_TRANS_PART_12 619969 9412 432
P_TRANS_PART_MAXVAL 0 0 0
P_TRANS_PART_MINVAL 0 0 0

Followup

Prashant, December 18, 2011 - 11:55 pm UTC

Hi Tom

You requested for sample schema scripts to reproduce the behavior. I have attached it. Please let me know if there is anything pending from my end.

Thanks
Prashant

Full Partition wise joins

Rajeshwaran, Jeyabal, March 16, 2012 - 5:14 am UTC

Tom:

1) When i query with id in(1,2,3) i see Full partition Wise join happening, but when i did the same using id in (select id from t1 where id in (1,2,3)) i dont see Full partition wise join happening. Can you explain why?

In the second explain plan why don't Oracle understand that the subquery is going to return 1,2,3 value and why not it should go for full partition wise joins?
rajesh@ORA10GR2> select *
  2  from t2, t3
  3  where t2.id = t3.id
  4  and t2.id in (1,2,3)
  5  /
Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3544871464

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   106K|    17M|    68   (5)| 00:00:01 |       |       |
|   1 |  PARTITION LIST INLIST|      |   106K|    17M|    68   (5)| 00:00:01 |KEY(I) |KEY(I) |
|*  2 |   HASH JOIN           |      |   106K|    17M|    68   (5)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS FULL  | T3   |    62 |  4898 |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   4 |    TABLE ACCESS FULL  | T2   | 17177 |  1627K|    62   (2)| 00:00:01 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

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

   2 - access("T2"."ID"="T3"."ID")

rajesh@ORA10GR2> select *
  2  from t2, t3
  3  where t2.id = t3.id
  4  and t2.id in (select id from t1 where id in (1,2,3))
  5  /
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1189762870

-------------------------------------------------------------------------------------------------
| Id  | Operation               | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |       | 10650 |  1861K|    68   (3)| 00:00:01 |       |       |
|*  1 |  HASH JOIN              |       | 10650 |  1861K|    68   (3)| 00:00:01 |       |       |
|   2 |   MERGE JOIN CARTESIAN  |       |   124 | 10168 |     5   (0)| 00:00:01 |       |       |
|   3 |    PARTITION LIST INLIST|       |    62 |  4898 |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   4 |     TABLE ACCESS FULL   | T3    |    62 |  4898 |     4   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   5 |    BUFFER SORT          |       |     2 |     6 |     1   (0)| 00:00:01 |       |       |
|   6 |     INLIST ITERATOR     |       |       |       |            |          |       |       |
|*  7 |      INDEX RANGE SCAN   | T1_PK |     2 |     6 |     1   (0)| 00:00:01 |       |       |
|   8 |   PARTITION LIST INLIST |       | 17177 |  1627K|    62   (2)| 00:00:01 |KEY(I) |KEY(I) |
|   9 |    TABLE ACCESS FULL    | T2    | 17177 |  1627K|    62   (2)| 00:00:01 |KEY(I) |KEY(I) |
-------------------------------------------------------------------------------------------------

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

   1 - access("T2"."ID"="T3"."ID" AND "T2"."ID"="ID")
   7 - access("ID"=1 OR "ID"=2 OR "ID"=3)

rajesh@ORA10GR2>


Here is the script for Table creation.

drop table t2 purge;
drop table t3 purge;
drop table t1 purge;

create table t1
as
select mod(rownum,10)+1 as id, sysdate + 1 as dt
from all_objects
where rownum <=10
/

create table t2
partition by list(id)
(
 partition p1 values (1),
 partition p2 values (2),
 partition p3 values (3),
 partition p4 values (4),
 partition p5 values (5),
 partition p6 values (6),
 partition p7 values (7),
 partition p8 values (8),
 partition p9 values (9),
 partition p10 values (10),
 partition pmax values(default)
)
as
select a.*, mod(rownum,10)+1 as id
from all_objects a
/

create table t3
partition by list(id)
(
 partition p1 values (1),
 partition p2 values (2),
 partition p3 values (3),
 partition p4 values (4),
 partition p5 values (5),
 partition p6 values (6),
 partition p7 values (7),
 partition p8 values (8),
 partition p9 values (9),
 partition p10 values (10),
 partition pmax values(default)
)
as
select a.*, mod(rownum,10)+1 as id
from user_objects a
/

alter table t1 add constraint t1_pk primary key(id);
alter table t2 add constraint t2_fk foreign key(id) references t1;
alter table t3 add constraint t3_fk foreign key(id) references t1;

begin
 dbms_stats.gather_table_stats
 (ownname=>user,
  tabname=>'T1',
  estimate_percent=>100,
  method_opt=>'for all columns size 254',
  cascade=>true);
end;
/
  
begin
 dbms_stats.gather_table_stats
 (ownname=>user,
  tabname=>'T2',
  estimate_percent=>100,
  method_opt=>'for all columns size 254',
  cascade=>true);
end;
/

begin
 dbms_stats.gather_table_stats
 (ownname=>user,
  tabname=>'T3',
  estimate_percent=>100,
  method_opt=>'for all columns size 254',
  cascade=>true);
end;
/

Tom Kyte
March 16, 2012 - 8:41 am UTC

1) When i query with id in(1,2,3) i see Full partition Wise join happening, but when i did the same using id in (select id from t1 where id in (1,2,3)) i dont see Full partition wise join happening. Can you explain why?

because you and I see that your two predicates are equivalent but apparently the optimizer does not. It doesn't have the leap of faith coded into it yet that allows it to determine "where id in (1,2,3)" is the same as "select id from t1 where id in (1,2,3)"

Full partition wise joins

Rajeshwaran, Jeyabal, March 16, 2012 - 9:36 am UTC

It doesn't have the leap of faith coded into it yet that allows it to determine "where id in (1,2,3)" is the same as "select id from t1 where id in (1,2,3)"
- Tom 11g optimizer was very smart and we can get the same plan (Full partition wise join)for both the queries.


1) Is there is any option in 10g database to tell optimizer to use Full partition wise join for query#2?

rajesh@ORA11GR2> select *
  2  from t2, t3
  3  where t2.id = t3.id
  4  and t2.id in (1,2,3)
  5  /
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3544871464

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 23504 |  4200K|   103   (2)| 00:00:02 |       |       |
|   1 |  PARTITION LIST INLIST|      | 23504 |  4200K|   103   (2)| 00:00:02 |KEY(I) |KEY(I) |
|*  2 |   HASH JOIN           |      | 23504 |  4200K|   103   (2)| 00:00:02 |       |       |
|   3 |    TABLE ACCESS FULL  | T3   |    11 |   913 |     5   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   4 |    TABLE ACCESS FULL  | T2   | 21367 |  2086K|    97   (2)| 00:00:02 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

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

   2 - access("T2"."ID"="T3"."ID")

rajesh@ORA11GR2>
rajesh@ORA11GR2> select *
  2  from t2, t3
  3  where t2.id = t3.id
  4  and t2.id in (select id from t1 where id in (1,2,3))
  5  /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3544871464

----------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 23504 |  4200K|   103   (2)| 00:00:02 |       |       |
|   1 |  PARTITION LIST INLIST|      | 23504 |  4200K|   103   (2)| 00:00:02 |KEY(I) |KEY(I) |
|*  2 |   HASH JOIN           |      | 23504 |  4200K|   103   (2)| 00:00:02 |       |       |
|   3 |    TABLE ACCESS FULL  | T3   |    11 |   913 |     5   (0)| 00:00:01 |KEY(I) |KEY(I) |
|   4 |    TABLE ACCESS FULL  | T2   | 21367 |  2086K|    97   (2)| 00:00:02 |KEY(I) |KEY(I) |
----------------------------------------------------------------------------------------------

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

   2 - access("T2"."ID"="T3"."ID")

rajesh@ORA11GR2>

Tom Kyte
March 16, 2012 - 9:50 am UTC

The optimizer gets smarter with each release. It does not get retroactively smarter in most cases.

there are things the 11g optimizer does that the 10g optimizer just has no ability to do. And when version 12 comes out, the 11g optimizer will look less smart than it is.

and so on.

Full partition wise joins

Rajeshwaran, Jeyabal, March 16, 2012 - 10:37 am UTC

Tom,

The application would send the list of ID's to database, when i use pipelined table function to use that values in the where clause i dont get that Full partition wise join.

Can you please help me or provide some directions, how can i achieve this Full partition wise joins in 10gR2.

rajesh@ORA10GR2> select *
  2  from t2, t3
  3  where t2.id = t3.id
  4  and t2.id in (select * from table(sys.odcinumberlist(1,2,3)))
  5  /
Elapsed: 00:00:00.23

Execution Plan
----------------------------------------------------------
Plan hash value: 1490624913

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |      |   116K|    19M|   238   (3)| 00:00:03 |       |       |
|*  1 |  HASH JOIN                              |      |   116K|    19M|   238   (3)| 00:00:03 |       |       |
|   2 |   PARTITION LIST ALL                    |      |   204 | 16116 |    10   (0)| 00:00:01 |     1 |    11 |
|   3 |    TABLE ACCESS FULL                    | T3   |   204 | 16116 |    10   (0)| 00:00:01 |     1 |    11 |
|*  4 |   HASH JOIN RIGHT SEMI                  |      |  5726 |   553K|   226   (2)| 00:00:03 |       |       |
|   5 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|      |       |       |            |          |       |       |
|   6 |    PARTITION LIST ALL                   |      | 57258 |  5423K|   201   (1)| 00:00:03 |     1 |    11 |
|   7 |     TABLE ACCESS FULL                   | T2   | 57258 |  5423K|   201   (1)| 00:00:03 |     1 |    11 |
----------------------------------------------------------------------------------------------------------------

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

   1 - access("T2"."ID"="T3"."ID")
   4 - access("T2"."ID"=VALUE(KOKBF$))


partition pruning with bind variables

Nenad Noveljic, August 22, 2012 - 9:25 am UTC

Dear Tom,

How does CBO estimate the number of partitions to prune, if the bind variables are used?

Following example shows, that the bind variable peeking is not used during the hard parse to estimate the number of partitions that will be pruned.

var P4090_MONAT varchar2(8) ;
exec :P4090_MONAT := '20120601' ;

select
EXE.EXECUTION_ID
from
dil_stex_execution exe
left join dil_bewegung bew2 on EXE.EXECUTION_ID = BEW2.GVF_ID and BEW2.BUCHUNGS_DAT between
to_date( :P4090_MONAT , 'YYYYMMDD')
and to_date(:P4090_MONAT , 'YYYYMMDD') +40
;

dil_bewegung.BUCHUNGS_DAT is the range partition key. Based on the value of :P4090_MONAT only two partitions should be pruned, but the estimate is 52 (10053 trace):

------------------------
Table Stats::
Table: DIL_BEWEGUNG Alias: BEW2 (Using composite stats)
(making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 467254767 #Blks: 13106817 AvgRowLen: 200.00 ChainCnt: 0.00
PARTITIONS::
PRUNED: 52
ANALYZED: 52 UNANALYZED: 0
#Rows: 467254767 #Blks: 13106824 AvgRowLen: 200.00 ChainCnt: 0.00
------------------------

If the bind variable is replaced by the literal '20120601', it can be seen that only two partitions will be pruned:

select
EXE.EXECUTION_ID
from
dil_stex_execution exe
left join dil_bewegung bew2 on EXE.EXECUTION_ID = BEW2.GVF_ID and BEW2.BUCHUNGS_DAT between
to_date( '20120601' , 'YYYYMMDD')
and to_date('20120601' , 'YYYYMMDD') +40
;


------------------------
Table: DIL_BEWEGUNG Alias: BEW2 (Using composite stats)
(making adjustments for partition skews)
ORIGINAL VALUES:: #Rows: 467254767 #Blks: 13106817 AvgRowLen: 200.00 ChainCnt: 0.00
PARTITIONS::
PRUNED: 2
ANALYZED: 2 UNANALYZED: 0
#Rows: 467254767 #Blks: 423664 AvgRowLen: 200.00 ChainCnt: 0.00
------------------------

Regards,

Nenad Noveljic

Tom Kyte
August 29, 2012 - 11:16 am UTC




How does CBO estimate the number of partitions to prune, if the bind variables
are used?


it depends, if you do something like:

where partition_key = :x

it *knows* it will hit exactly one (or zero) partition and will bind peek at optimization time to see which partition and use the local partition statistics from that partition (if available) to optimize.

If you do something like:

where partition_key > :x

then the optimizer knows that it will hit zero, one or more partitions and will use global stats to optimize the query. You'll see KEY KEY in the pstart/pstop columns of the plan.


your example proves nothing, it only shows that all partitions MIGHT be accessed, not that all partitions WERE accessed!!!!!


ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  -- subpartition by hash(x)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) tablespace users,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) tablespace example,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t select to_date( '12-mar-2003','dd-mon-yyyy')+mod(rownum,3), user_id, username from all_users;

38 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable dt1 varchar2(30)
ops$tkyte%ORA11GR2> variable dt2 varchar2(30)
ops$tkyte%ORA11GR2> exec :dt1 := '12-mar-2003'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec :dt2 := '13-mar-2003'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter tablespace example offline;

Tablespace altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select count(*) from t where dt between to_date( :dt1, 'dd-mon-yyyy') and to_date( :dt2, 'dd-mon-yyyy') -1/24/60/60;

  COUNT(*)
----------
        12

<b>given a runtime set of inputs that do not hit the example tablespace - we run OK, note how in the following that the pstart/pstop are KEY/KEY - it will be figured out at RUNTIME what partitions will be hit - because the binds could be *anything* at runtime</b>

Execution Plan
----------------------------------------------------------
Plan hash value: 2640844081

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE            |      |     1 |     9 |            |          |       |       |
|*  2 |   FILTER                   |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |    33 |   297 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |     TABLE ACCESS FULL      | T    |    33 |   297 |     3   (0)| 00:00:01 |   KEY |   KEY |
---------------------------------------------------------------------------------------------------

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

   2 - filter(TO_DATE(:DT1,'dd-mon-yyyy')<=TO_DATE(:DT2,'dd-mon-yyyy')-.0000115740740740740
              7407407407407407407407408)
   4 - filter("DT"<=TO_DATE(:DT2,'dd-mon-yyyy')-.000011574074074074074074074074074074074074
              08 AND "DT">=TO_DATE(:DT1,'dd-mon-yyyy'))

ops$tkyte%ORA11GR2> select count(*) from t where dt between to_date( :dt1, 'dd-mon-yyyy') and to_date( :dt2, 'dd-mon-yyyy');
select count(*) from t where dt between to_date( :dt1, 'dd-mon-yyyy') and to_date( :dt2, 'dd-mon-yyyy')
                     *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5: '/home/ora11gr2/app/ora11gr2/oradata/ora11gr2/example01.dbf'

<b>now we fail because we tried to read that second partition</b>


ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter tablespace example online
  2  /

Tablespace altered.



so that shows that pruning took place, you can see bind peeking this way:

ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T', partname => 'PART1', numrows => 100000000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'T', partname => 'PART2', numrows => 0 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2          l_dt1 date := to_date( :dt1, 'dd-mon-yyyy');
  3          l_dt2 date := to_date( :dt2, 'dd-mon-yyyy');
  4  begin
  5          for x in (select /*+query1*/ * from t where dt = l_dt1 )
  6          loop
  7                  null;
  8          end loop;
  9  
 10          for x in (select plan_table_output txt from table(dbms_xplan.display_cursor()))
 11          loop
 12                  dbms_output.put_line( '.'||x.txt );
 13          end loop;
 14  end;
 15  /
.SQL_ID  cwyj15afjdkb0, child number 0
.-------------------------------------
.SELECT /*+query1*/ * FROM T WHERE DT = :B1
.
.Plan hash value: 1413293367
.
.-----------------------------------------------------------------------------------------------
.| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
.-----------------------------------------------------------------------------------------------
.|   0 | SELECT STATEMENT       |      |       |       |   864 (100)|          |       |       |
.|   1 |  PARTITION RANGE SINGLE|      |  1000K|    37M|   864  (97)| 00:00:11 |   KEY |   KEY |
.|*  2 |   TABLE ACCESS FULL    | T    |  1000K|    37M|   864  (97)| 00:00:11 |   KEY |   KEY |
.-----------------------------------------------------------------------------------------------
.
.Predicate Information (identified by operation id):
.---------------------------------------------------
.
.   2 - filter("DT"=:B1)
.

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> declare
  2          l_dt1 date := to_date( :dt1, 'dd-mon-yyyy');
  3          l_dt2 date := to_date( :dt2, 'dd-mon-yyyy');
  4  begin
  5          for x in (select /*+query2*/ * from t where dt = l_dt2 )
  6          loop
  7                  null;
  8          end loop;
  9  
 10          for x in (select plan_table_output txt from table(dbms_xplan.display_cursor()))
 11          loop
 12                  dbms_output.put_line( '.'||x.txt );
 13          end loop;
 14  end;
 15  /
.SQL_ID  9w0r0f714k7kh, child number 0
.-------------------------------------
.SELECT /*+query2*/ * FROM T WHERE DT = :B1
.
.Plan hash value: 1413293367
.
.-----------------------------------------------------------------------------------------------
.| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
.-----------------------------------------------------------------------------------------------
.|   0 | SELECT STATEMENT       |      |       |       |    29 (100)|          |       |       |
.|   1 |  PARTITION RANGE SINGLE|      |     1 |    39 |    29   (0)| 00:00:01 |   KEY |   KEY |
.|*  2 |   TABLE ACCESS FULL    | T    |     1 |    39 |    29   (0)| 00:00:01 |   KEY |   KEY |
.-----------------------------------------------------------------------------------------------
.
.Predicate Information (identified by operation id):
.---------------------------------------------------
.
.   2 - filter("DT"=:B1)
.

PL/SQL procedure successfully completed.



you can see by the different estimated cardinalities for the same query (just different in the comment) we must have bind peeked to pick up the local statistics to optimize each one.



Partition pruning on non-equal condition

Rajeshwaran Jeyabal, April 18, 2013 - 4:19 pm UTC

Tom,

I was reading through docs on VLDB guide.Its clearly mentioned that
<quote>
Oracle Database prunes partitions when you use range, LIKE, equality, and IN-list predicates on the range or list partitioning columns
</quote>

I was thinking why does oracle cannot perform partition pruning on non-equal conditions? In the below test case why don't oracle eliminate partition p1 by looking at the supplied metadata? (Hope I have provided you the very small possible test case to replicate)

drop table t purge;
create table t
( x int,
  y varchar2(30) )
partition by list(y)
( partition p1 values ('SYS') ,
  partition p2 values (default) ) ;

exec dbms_stats.gather_table_stats(user,'T');

rajesh@ORA11G> select * from t where y <> 'SYS' ;
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3845649146

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    30 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST ALL|      |     1 |    30 |     2   (0)| 00:00:01 |     1 |     2 |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    30 |     2   (0)| 00:00:01 |     1 |     2 |
-------------------------------------------------------------------------------------------

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

   2 - filter("Y"<>'SYS')

http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#BABJIFFF
Tom Kyte
April 22, 2013 - 7:58 pm UTC

it is just an optimization we currently do not have in the database. Technically it could eliminate partition 1, it just does not.

Partition Pruning Issue

Dillip Kumar Sahoo, May 10, 2013 - 10:37 am UTC

Hi Tom,
I have table with 5 range partition with date range and hash subparititon scheme specifying country names. It stores data based on year and from which country the data originated.

Assumptions
-----------
vTable Name: T1
Partition Names(partition based on year of Order_date): T1_2010,T1_2011,T1_2012,T1_2013
Subpartition Names(subpartition based on REGION): T1_2010_US,T1_2011_US,T1_2013_UK,etc....

This table is having millions of records for each subpartition with all possible columns having BITMAP indexes.
The table statistics are always kept up-to-date.


When we specify a "select for update" query with subpartiton clause , the query returns results in 2-3mins.
Query 1:
select * from t1 subpartition(t1_2013_US) where order_date not between :a and :b
for update

But the same "select for update" query runs for 40-50 mins, somtimes more than an hour, if only partition name is specified.

Query 2:
select * from t1 partition(t1_2013) where region='US' and order_date not between :a and :b
for update

Both query returns exactly same resultset. We use the result out of query to delete all invalid orders out of specificied date range.

Could you please explain , second query runs for ever and first query pulls data withing few minutes. If you could elaborate on possible situations, when this can happen?
Tom Kyte
May 10, 2013 - 12:44 pm UTC

hash partitioning on a column with few unique values like country isn't going to work very well. You need an attribute with a LOT of distinct values to get the data to hash evenly.


you'd need to post plans used by each query to get a comment. the first thing I can think of is that perhaps the second query is accidentally using an index to retrieve the data and the first was using a nice fast full scan.

so, get plans (using dbms_xplan.display_cursor on the sqlid's in the shared pool - NOT explain plans) and if at all possible, get sql_trace's and tkprofs. After you get those, you might not even need to post it as the reason could well be staring you in the face at that point ;)

Partition pruning on Interval partition

Rajeshwaran, August 13, 2013 - 9:08 am UTC

Tom,

Can you help me how the optimizer get the value for pstart and pstop as 8027? where this information is available in metadata?

create table t
( x int, y int, z varchar2(40) )
partition by range(x) interval(1)
subpartition by hash(y)
subpartition template
(
 subpartition sp1,
 subpartition sp2,
 subpartition sp3,
 subpartition sp4
)
(
 partition p0 values less than(1)
) ;

insert into t values(2006,1,'a');
insert into t values(2007,2,'b');
commit;
exec dbms_stats.gather_table_stats(user,'T');

rajesh@ORA11GR2> set autotrace traceonly explain ;
rajesh@ORA11GR2> select * from t where x = 2006
  2  and y = 1041;
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 3551997908

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     9 |     6   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |     9 |     6   (0)| 00:00:01 |  2007 |  2007 |
|   2 |   PARTITION HASH SINGLE|      |     1 |     9 |     6   (0)| 00:00:01 |     3 |     3 |
|*  3 |    TABLE ACCESS FULL   | T    |     1 |     9 |     6   (0)| 00:00:01 |  8027 |  8027 |
-----------------------------------------------------------------------------------------------

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

   3 - filter("X"=2006 AND "Y"=1041)

rajesh@ORA11GR2> set autotrace off;

Tom Kyte
August 13, 2013 - 7:19 pm UTC

well, if you:

ops$tkyte%ORA11GR2> select * from t where x = 2006;

Execution Plan
----------------------------------------------------------
Plan hash value: 2673072502

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |     9 |    14   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     1 |     9 |    14   (0)| 00:00:01 |  2007 |  2007 |
|   2 |   PARTITION HASH ALL   |      |     1 |     9 |    14   (0)| 00:00:01 |     1 |     4 |
|*  3 |    TABLE ACCESS FULL   | T    |     1 |     9 |    14   (0)| 00:00:01 |  8025 |  8028 |
-----------------------------------------------------------------------------------------------

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

   3 - filter("X"=2006)



you can see that "x=2006" are partitions 8025..8028.

2006*4 = 8024, plus the first partition = 8025.


and y=1041 must have hashed to the 3rd hash partition.


not really stored in the metadata - you have to do the math for the X dimension, and you'd have to know that y=1041 would be partition "3"

Partition pruning on Interval partition

Rajeshwaran, August 13, 2013 - 1:38 pm UTC

Tom,

I think I got the answer for my question.

when x = 2006 then It should be in partition 2005. the no of partition between P0 and P2005 is 2006 and each partition has 4 subpartitions. so I total I have 2006*4 = 8024. since I am hitting 3rd subpartition for the above query it is 8024+3 = 8027.


A reader, August 28, 2013 - 5:30 pm UTC

Shouldn't y=1041 be in SubPartition 1.
I assumed - 1041 / 4 gives a remainder of 1, which should go into Sub Partition 1 and not 3.
Tom Kyte
September 04, 2013 - 5:31 pm UTC

see above, it was partition 8027, that was the 3rd partition.

hashing is not mod(), you are thinking mod()

think "hash function" hash(x) will return a number before 1 and 4. It won't do a simple mod()

A reader, September 05, 2013 - 2:34 pm UTC

Hi tom,

1 | PARTITION RANGE SINGLE| | 1 | 9 | 14 (0)| 00:00:01 | 2007 | 2007 |


why the step 1 (partition range single) in the plan has pstart and pstop as 2007?

Thanks

Tom Kyte
September 09, 2013 - 11:12 am UTC

because x = 2006 is the 2007'th partition, then there are all of the subpartitions to consider.

so, it is hitting subpartitions inside of a "partition", the partition doesn't really exist - it isn't a segment, it is a container.

but x=2006 would be the 2007'th partition (everything less than one is partition 1, 1's are partition 2, 2's are partition 3 and so on)

Pstart and Pstop on Interval Partitioned Tables

Rajeshwaran Jeyabal, August 23, 2016 - 1:11 pm UTC

Team,

Pstart and Pstop from explain plan shows up the starting and ending positions of partitions.

but that doesn't work properly in case of interval partitioned tables.

demo@ORA11G> create table t
  2  partition by range(x) interval(1)
  3  ( partition p0 values less than (1) )
  4  as
  5  select a.*, mod(rownum,5)+1 x
  6  from all_objects a;

Table created.

demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA11G> column partition_name format a10
demo@ORA11G> column high_value format a10
demo@ORA11G> select partition_name,partition_position,high_value
  2  from user_tab_partitions
  3  where table_name ='T'
  4  order by 2;

PARTITION_ PARTITION_POSITION HIGH_VALUE
---------- ------------------ ----------
P0                          1 1
SYS_P11970                  2 2
SYS_P11966                  3 3
SYS_P11967                  4 4
SYS_P11968                  5 5
SYS_P11969                  6 6

6 rows selected.

demo@ORA11G> alter table t drop partition SYS_P11967;

Table altered.

demo@ORA11G>
demo@ORA11G> select partition_name,partition_position,high_value
  2  from user_tab_partitions
  3  where table_name ='T'
  4  order by 2;

PARTITION_ PARTITION_POSITION HIGH_VALUE
---------- ------------------ ----------
P0                          1 1
SYS_P11970                  2 2
SYS_P11966                  3 3
SYS_P11968                  4 5
SYS_P11969                  5 6

5 rows selected.

demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select * from t partition(SYS_P11968);

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      | 16973 |  1674K|   141   (3)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      | 16973 |  1674K|   141   (3)| 00:00:01 |     5 |     5 |
|   2 |   TABLE ACCESS FULL    | T    | 16973 |  1674K|   141   (3)| 00:00:01 |     5 |     5 |
-----------------------------------------------------------------------------------------------

demo@ORA11G> set autotrace off
demo@ORA11G>

When interval got turned off, Pstart and Pstop got correct information.
demo@ORA11G> alter table t set interval();

Table altered.

demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select * from t partition(SYS_P11968);

Execution Plan
----------------------------------------------------------
Plan hash value: 2931986080

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      | 16973 |  1674K|   141   (3)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      | 16973 |  1674K|   141   (3)| 00:00:01 |     4 |     4 |
|   2 |   TABLE ACCESS FULL    | T    | 16973 |  1674K|   141   (3)| 00:00:01 |     4 |     4 |
-----------------------------------------------------------------------------------------------

demo@ORA11G> set autotrace off
demo@ORA11G>

Any workaround/solution available for Interval partitioned tables?

PS: This behavior is still reproducible in 12c(12.1.0.2) database.
Connor McDonald
August 24, 2016 - 12:54 am UTC


That is by design for a couple of reasons.

1) An interval partition is not "complete", ie there are gaps in the partition ranges. If I drop a partition, that partition is still "virtually" there, ie, if I insert data, it will *not* go into the next partition, a new interval partition will be created. My best graphics :-) below

Range before drop:
|----------|----------|----------|----------|----------|
Range after drop:
|----------|---------------------|----------|----------|

Interval before drop:
|----------|----------|----------|----------|----------|
Interval after drop:
|----------|          |----------|----------|----------|


You could perhaps make an argument that PARTITION_POSITION should reflect this in the dictionary, which leads onto point 2 below.

2) PARTITION_POSITION used to be a "real" attribute in the dictionary, ie, a value stored in the dictionary. But when people then split, merge, etc, dropped partitions etc we would then need to update potentially thousands of dictionary rows to reset the partition number for all of the remaining partitions. That's not a scalable operation.

So if you look at the definition nowadays, its an on-the-fly analytic function using ROW_NUMBER() to count the partitions.

I dont *think* there is anywhere specifically mentioned in the documentation that says PSTART/PSTOP are bound to PARTITION_POSITION.

But I do agree - it could be cause for confusion, so I'll certainly blog about it Perhaps log it with Support and see how you go.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.