Partition pruning in INDEX access to partitioned table

Few days ago I have been asked to look at the specific problem, related to combinations of several factors.
There is the big table “T” (about 100 mln rows and 400 Gb size) what is partitioned by list on column “Y”. There is the global index on column “T”(“X”) with acceptable selectivity factor. There is a staging table “T_S” what is used as a “golden source” of “X” column values. We need to get data from table “T” what have “X” values from “T_S” list and “Y” column equal to ‘C’. The case is that such data are just a tiny part of all rows corresponding to (“T”.”X” = “T_S”.”X”) condition. Let me illustrate the scenario:

SQL> CREATE TABLE t (x int, big_column char(200), y char(1))
  2  PARTITION BY LIST (y) (
  3   PARTITION P_A VALUES('A'),
  4   PARTITION P_B VALUES('B'),
  5   PARTITION P_C VALUES('C'),
  6   PARTITION P_D VALUES('D'),
  7   PARTITION P_E VALUES('E')
  8  )
  9  /

Table created.

SQL> 
SQL> INSERT INTO t
  2  SELECT id,stub,pkey FROM
  3  (SELECT rownum id,
  4  'x' stub
  5  FROM dual
  6  CONNECT BY LEVEL <=100000
  7  ),
  8  (
  9  SELECT decode(rownum,1,'A',2,'B',3,'C',4,'D',5,'E') pkey
 10  FROM dual
 11  CONNECT BY LEVEL  
SQL> COMMIT
  2  /

Commit complete.

SQL> 
SQL> CREATE TABLE t_lead(x int)
  2  /

Table created.

SQL> 
SQL> INSERT INTO t_lead
  2  SELECT rownum*1000 FROM dual CONNECT BY LEVEL  
SQL> COMMIT
  2  /

Commit complete.

SQL> 
SQL> CREATE INDEX t_i ON t(x)
  2  /

Index created.

SQL> 
SQL> BEGIN
  2   dbms_stats.gather_table_stats(
  3  	 ownname=>user,
  4  	 tabname=>'T',
  5  	 cascade=>true
  6  	 );
  7   dbms_stats.gather_table_stats(
  8  	 ownname=>user,
  9  	 tabname=>'T_LEAD'
 10  	 );
 11  END;
 12  /

PL/SQL procedure successfully completed.

Now let’s look at the query and it’s plan:

SQL> SET AUTOTRACE TRACEONLY
SQL> SET PAGESIZE 2000
SQL> SET LINESIZE 120
SQL> 
SQL> 
SQL> SELECT t.x FROM t_lead, t
  2  WHERE t_lead.x = t.x
  3  AND t.big_column = 'x'
  4  AND t.y = 'C'
  5  /

50 rows selected.

Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 3015557880                                                                                             

--------------------------------------------------------------------------------------------------------------          
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |          
--------------------------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT                    |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   1 |  NESTED LOOPS                       |        |       |       |            |          |       |       |          
|   2 |   NESTED LOOPS                      |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   3 |    TABLE ACCESS FULL                | T_LEAD |    50 |   200 |     3   (0)| 00:00:01 |       |       |          
|*  4 |    INDEX RANGE SCAN                 | T_I    |     5 |       |     2   (0)| 00:00:01 |       |       |          
|*  5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T      |     1 |   208 |     7   (0)| 00:00:01 |     3 |     3 |          
--------------------------------------------------------------------------------------------------------------          

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

   4 - access("T_LEAD"."X"="T"."X")                                                                                     
   5 - filter("T"."BIG_COLUMN"='x' AND "T"."Y"='C')                                                                     

Statistics
----------------------------------------------------------                                                              
          0  recursive calls                                                                                            
          0  db block gets                                                                                              
        170  consistent gets                                                                                            
          0  physical reads                                                                                             
          0  redo size                                                                                                  
       1415  bytes sent via SQL*Net to client                                                                           
        557  bytes received via SQL*Net from client                                                                     
          5  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
         50  rows processed

OK, we should notice Pstart and Pstop columns in TABLE ACCESS BY GLOBAL INDEX ROWID (5th row in plan) have value 3 what (in accordance to Oracle documentation) means partition pruning. E.g. Oracle accesses just one – required – partition and does not try to reach other ones when reaches table blocks.
We may reach the same using PARTITION() clause in FROM part of the query:

SQL> SELECT t.* FROM t_lead, t partition(p_c) t
  2  WHERE t_lead.x = t.x
  3  AND t.big_column = 'x'
  4  /

50 rows selected.

Execution Plan
----------------------------------------------------------                                                              
Plan hash value: 3015557880                                                                                             

--------------------------------------------------------------------------------------------------------------          
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |          
--------------------------------------------------------------------------------------------------------------          
|   0 | SELECT STATEMENT                    |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   1 |  NESTED LOOPS                       |        |       |       |            |          |       |       |          
|   2 |   NESTED LOOPS                      |        |    50 | 10600 |   353   (0)| 00:00:05 |       |       |          
|   3 |    TABLE ACCESS FULL                | T_LEAD |    50 |   200 |     3   (0)| 00:00:01 |       |       |          
|*  4 |    INDEX RANGE SCAN                 | T_I    |     5 |       |     2   (0)| 00:00:01 |       |       |          
|*  5 |   TABLE ACCESS BY GLOBAL INDEX ROWID| T      |     1 |   208 |     7   (0)| 00:00:01 |     3 |     3 |          
--------------------------------------------------------------------------------------------------------------          

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

   4 - access("T_LEAD"."X"="T"."X")                                                                                     
       filter(TBL$OR$IDX$PART$NUM("T",0,1,0,ROWID)=3)                                                                   
   5 - filter("T"."BIG_COLUMN"='x')                                                                                     

Statistics
----------------------------------------------------------                                                              
          0  recursive calls                                                                                            
          0  db block gets                                                                                              
        170  consistent gets                                                                                            
          0  physical reads                                                                                             
          0  redo size                                                                                                  
       2083  bytes sent via SQL*Net to client                                                                           
        557  bytes received via SQL*Net from client                                                                     
          5  SQL*Net roundtrips to/from client                                                                          
          0  sorts (memory)                                                                                             
          0  sorts (disk)                                                                                               
         50  rows processed

We can notice the difference between two plans despite they look almost alike and costs/rows estimations are the same. The second plan involves additional filtering: TBL$OR$IDX$PART$NUM(“T”,0,1,0,ROWID)=3 and this step seems being executed at the level of the index scan. E.g. Oracle gets rid of ROWIDs what don’t fit to particular partition.
We may easily notice this in the tracefile (I executed 10046 event level 12 for both ones):

PARSING IN CURSOR #2 len=83 dep=0 uid=84 oct=3 lid=84 tim=199354375853 hv=1590834553 ad='7ffb3dfa4a8' sqlid='984xy4jgd4dbt'
SELECT t.x FROM t_lead, t
WHERE t_lead.x = t.x
AND t.big_column = 'x'
AND t.y = 'C'
END OF STMT
PARSE #2:c=0,e=125,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354375852
EXEC #2:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354376052
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354376132
WAIT #2: nam='Disk file operations I/O' ela= 863 FileOperation=2 fileno=4 filetype=2 obj#=-1 tim=199354377098
FETCH #2:c=0,e=1140,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=3015557880,tim=199354377321
WAIT #2: nam='SQL*Net message from client' ela= 163 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354377555
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354377720
FETCH #2:c=0,e=513,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354378141
WAIT #2: nam='SQL*Net message from client' ela= 5458 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354383664
WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354383822
FETCH #2:c=0,e=412,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354384178
WAIT #2: nam='SQL*Net message from client' ela= 4449 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354388682
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354388782
FETCH #2:c=0,e=435,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354389164
WAIT #2: nam='SQL*Net message from client' ela= 4593 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354393810
WAIT #2: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354393958
FETCH #2:c=0,e=208,p=0,cr=19,cu=0,mis=0,r=4,dep=0,og=1,plh=3015557880,tim=199354394109
STAT #2 id=1 cnt=50 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=170 pr=0 pw=0 time=0 us)'
STAT #2 id=2 cnt=250 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=120 pr=0 pw=0 time=498 us cost=353 size=10600 card=50)'
STAT #2 id=3 cnt=50 pid=2 pos=1 obj=94055 op='TABLE ACCESS FULL T_LEAD (cr=11 pr=0 pw=0 time=441 us cost=3 size=200 card=50)'
STAT #2 id=4 cnt=250 pid=2 pos=2 obj=94056 op='INDEX RANGE SCAN T_I (cr=109 pr=0 pw=0 time=90 us cost=2 size=0 card=5)'
STAT #2 id=5 cnt=50 pid=1 pos=2 obj=94049 op='TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: 3 3 (cr=50 pr=0 pw=0 time=0 us cost=7 size=208 card=1)'
WAIT #2: nam='SQL*Net message from client' ela= 6502 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354400849
CLOSE #2:c=0,e=17,dep=0,type=0,tim=199354400917
=====================
PARSING IN CURSOR #3 len=86 dep=0 uid=84 oct=3 lid=84 tim=199354401047 hv=1072898565 ad='7ffb3dd24f0' sqlid='bd34s5szz68h5'
SELECT t.* FROM t_lead, t partition(p_c) t
WHERE t_lead.x = t.x
AND t.big_column = 'x'
END OF STMT
PARSE #3:c=0,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354401047
EXEC #3:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3015557880,tim=199354401292
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354401372
FETCH #3:c=0,e=95,p=0,cr=7,cu=0,mis=0,r=1,dep=0,og=1,plh=3015557880,tim=199354401512
WAIT #3: nam='SQL*Net message from client' ela= 182 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354401755
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354401855
FETCH #3:c=0,e=377,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354402176
WAIT #3: nam='SQL*Net message from client' ela= 72296 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354474524
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354474808
FETCH #3:c=0,e=481,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354475173
WAIT #3: nam='SQL*Net message from client' ela= 22029 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354497275
WAIT #3: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354497514
FETCH #3:c=0,e=482,p=0,cr=48,cu=0,mis=0,r=15,dep=0,og=1,plh=3015557880,tim=199354497892
WAIT #3: nam='SQL*Net message from client' ela= 21923 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354519880
WAIT #3: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354520063
FETCH #3:c=0,e=230,p=0,cr=19,cu=0,mis=0,r=4,dep=0,og=1,plh=3015557880,tim=199354520220
STAT #3 id=1 cnt=50 pid=0 pos=1 obj=0 op='NESTED LOOPS  (cr=170 pr=0 pw=0 time=0 us)'
STAT #3 id=2 cnt=50 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=120 pr=0 pw=0 time=1911 us cost=353 size=10600 card=50)'
STAT #3 id=3 cnt=50 pid=2 pos=1 obj=94055 op='TABLE ACCESS FULL T_LEAD (cr=11 pr=0 pw=0 time=245 us cost=3 size=200 card=50)'
STAT #3 id=4 cnt=50 pid=2 pos=2 obj=94056 op='INDEX RANGE SCAN T_I (cr=109 pr=0 pw=0 time=0 us cost=2 size=0 card=5)'
STAT #3 id=5 cnt=50 pid=1 pos=2 obj=94049 op='TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: 3 3 (cr=50 pr=0 pw=0 time=0 us cost=7 size=208 card=1)'
WAIT #3: nam='SQL*Net message from client' ela= 10514 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=199354530980
CLOSE #3:c=0,e=20,dep=0,type=0,tim=199354531107

Bold font stresses what in the first operator index scan operation reports 250 rows while in the second one – just 50.

About dnikiforov

I'm writing here about everything what is in my interest - Java, Oracle, MySQL and other things. These are just remarks about things I'm working with, and if this helps you, guys, in your work - this makes me happy. ;)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s