Partition pruning

From: LS Cheng <exriscer_at_gmail.com>
Date: Thu, 30 Apr 2009 14:26:35 +0200
Message-ID: <6e9345580904300526h24542539yde0ed626b3c235d2_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 30 2009 - 07:26:35 CDT