Partition pruning

Hi

I have a question regarding partition pruning, there are three tables,
d_time, d_client and f_order.

f_order is subpartitioned by range. Partition pruning should happen when
d_time joins with f_order.

The test case is as follows:


*create table d_time
(
i_date date,
i_number number
);

insert into d_time
select sysdate+rownum, rownum num
from dba_objects
where rownum < 65

create table d_client
(
i_client number
);

insert into d_client
select mod(rownum, 10)
from dba_objects
where rownum < 11

create table f_order
(
    i_number number,
    i_segment number,
    i_client number
)
partition by range (i_number)
subpartition by hash (i_segment)
SUBPARTITIONS 4
(
    partition f_order_p1 values less than (10),
    partition f_order_p2 values less than (20),
    partition f_order_p3 values less than (30),
    partition f_order_p4 values less than (40),
    partition f_order_p5 values less than (50),
    partition f_order_p6 values less than (60),
    partition f_order_p7 values less than (70)
);

insert into f_order
select mod(rownum, 30), rownum, mod(rownum, 10)
from dual
connect by level <= 200000;

commit;*

Then this query is run

select a.*, c.*, b.*
 from d_time a, f_order b, d_client c
  where a.i_number = b.i_number
and c.i_client = b.i_client
and c.i_client  in (0, 1, 2, 3)
and a.i_date >= to_date('2009-04-29', 'yyyy-mm-dd')
and a.i_date <  to_date('2009-06-01', 'yyyy-mm-dd')

execution plan:

---------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                   | Name    | Rows  | Bytes | Cost  |
Time      | Pstart| Pstop |
----------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT            |         |       |       |   152
|           |       |       |
| 1   |  HASH JOIN                  |         |   31K |  716K |   152 |
00:00:02 |       |       |
| 2   |   TABLE ACCESS FULL         | D_TIME  |    31 |   341 |     3 |
00:00:01 |       |       |
| 3   |   HASH JOIN                 |         |   31K |  374K |   148 |
00:00:02 |       |       |
| 4   |    TABLE ACCESS FULL        | D_CLIENT|     4 |     8 |     3 |
00:00:01 |       |       |
*| 5   |    PARTITION RANGE SUBQUERY |         |   78K |  779K |   144 |
00:00:02 | KEY(SUBQUERY)| KEY(SUBQUERY)|*
| 6   |     PARTITION HASH ALL      |         |   78K |  779K |   144 |
00:00:02 | 1     | 4     |
| 7   |      TABLE ACCESS FULL      | F_ORDER |   78K |  779K |   144 |
00:00:02 | KEY   | KEY   |
----------------------------------------------+-----------------------------------+---------------+

10053 join order
***********************
Join order[2]:  D_CLIENT[C]#0  F_ORDER[B]#2  D_TIME[A]#1
***************


I wonder how can partition pruning can happen when d_client joins with
f_order when the partitioning key is pointing to d_time?


Thanks

--
LSC

Other related posts: