Partition Wise prune table to index?

[cmmrfrds]

Oracle 11G 2.

Will Oracle do "partition wise" pruning from a table partition to an index partition?

Scenario.

Table A
Partition Range a_date
Subpartition Hash (a_id) -- primary key

Table B
Partition Hash (a_id) -- primary key same as table A.

Table C
Partition Range c_date
Subpartition Hash (c_id) -- primary key

Table D
Partition Hash (c_id) -- primary key

Try.
Global partitioned index on Table C
Partition Hash(a_id)

We have tested this between table, but not index.

Table A joined to Table B
Where Clause a_date between x and y.

Oracle partition prunes on a_date and "partition wise" prunes a_id, and the same thing happens between Table C and Table D.

Question.
If I put the same hash partition on an index on Table C, will Oracle "partition wise" prune between Table A and Table C?

There are times when we will
Table A joined to Table C
Where Clause a_date between x and y.

The hope is we can get the benefits of "partition wise" pruning through the index partitions in this later case.

Anybody?

 

[Thargy]

All I can suggest is to build the tables in question, and populate them with some sample data.

Then put a session into trace, run the query, and see what happens.

Regards

T

 

[cmmrfrds]

Since I asked the question, I will share my test results. I will try to be as concise as possible. Oracle 11G with Parallelism on the tables.

Scenario is to get "partition wise" pruning between the joined tables. Problem is there are 2 partitioning models and how to cross over between models. Solution is the global partitioned index.

Model 1.
table order_proc
range partition (ordering_date)
hash subpartition 16 (order_proc_id)
-- test needed here.
global index on (pat_enc_csn_id)
hash partition 16 (pat_enc_csn_id)

table order_proc_2
hash partition 16 (order_proc_id

Model 2.
table pat_enc
range partition (contact_date)
hash subpartition 16 (pat_enc_csn_id)
-- other tables in this model.

Test Query.
SELECT
C.order_proc_id,
C.pat_enc_csn_id,
C2.prov_id,
pe.pat_id
FROM imcust.ORDER_PROC C
INNER JOIN imcust.ORDER_PROC_2 C2
ON C.ORDER_PROC_ID = C2.ORDER_PROC_ID
INNER JOIN IMCUST.PAT_ENC pe
on pe.pat_enc_csn_id = c.pat_enc_csn_id
WHERE C.ORDERING_DATE >= TO_DATE('07/01/2009','MM/DD/YYYY') AND C.ORDERING_DATE < TO_DATE('01/01/2010','MM/DD/YYYY')

Explain Plan from Toad. I see "join filter pruning" on both the tables if I am reading the plan correctly. So, in the next couple of weeks we will put this into production and monitor the results.

Plan
SELECT STATEMENT ALL_ROWS Cost: 41 Bytes: 65,841 Cardinality: 1,291 CPU Cost: 4,120,893 IO Cost: 40
16 PX COORDINATOR
15 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10002 :Q1002 Cost: 41 Bytes: 65,841 Cardinality: 1,291 Distribution: QC (RANDOM) CPU Cost: 4,120,893 IO Cost: 40
14 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1002 Access Predicates: "C"."ORDER_PROC_ID"="C2"."ORDER_PROC_ID" Cost: 41 Bytes: 65,841 Cardinality: 1,291 CPU Cost: 4,120,893 IO Cost: 40
11 PART JOIN FILTER CREATE PARALLEL_COMBINED_WITH_PARENT SYS.:BF0000 :Q1002 Cost: 39 Bytes: 41,616 Cardinality: 1,224 CPU Cost: 1,969,136 IO Cost: 38
10 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1002 Cost: 39 Bytes: 41,616 Cardinality: 1,224 CPU Cost: 1,969,136 IO Cost: 38
9 PX SEND BROADCAST LOCAL PARALLEL_TO_PARALLEL SYS.:TQ10001 :Q1001 Cost: 39 Bytes: 41,616 Cardinality: 1,224 Distribution: BROADCAST LOCAL CPU Cost: 1,969,136 IO Cost: 38
8 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1001 Access Predicates: "PE"."PAT_ENC_CSN_ID"="C"."PAT_ENC_CSN_ID" Cost: 39 Bytes: 41,616 Cardinality: 1,224 CPU Cost: 1,969,136 IO Cost: 38
5 PART JOIN FILTER CREATE PARALLEL_COMBINED_WITH_PARENT SYS.:BF0001 :Q1001 Cost: 2 Bytes: 24,480 Cardinality: 1,224 CPU Cost: 43,657 IO Cost: 2
4 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1001 Cost: 2 Bytes: 24,480 Cardinality: 1,224 CPU Cost: 43,657 IO Cost: 2
3 PX SEND BROADCAST LOCAL PARALLEL_TO_PARALLEL SYS.:TQ10000 :Q1000 Cost: 2 Bytes: 24,480 Cardinality: 1,224 Distribution: BROADCAST LOCAL CPU Cost: 43,657 IO Cost: 2
2 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1000 Cost: 2 Bytes: 24,480 Cardinality: 1,224 CPU Cost: 43,657 IO Cost: 2 Partition #: 11 Partitions accessed #1 - #8
1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT IMCUST.ORDER_PROC :Q1000 Object Instance: 1 Filter Predicates: "C"."ORDERING_DATE">=TO_DATE(' 2009-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') Cost: 2 Bytes: 24,480 Cardinality: 1,224 CPU Cost: 43,657 IO Cost: 2 Partition #: 11 Partitions accessed #33 - #40
7 PX PARTITION HASH JOIN-FILTER PARALLEL_COMBINED_WITH_CHILD :Q1001 Cost: 36 Bytes: 259,210 Cardinality: 18,515 CPU Cost: 122,148 IO Cost: 36 Partition #: 13 Partitions accessed #:BF0001
6 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT IMCUST.PAT_ENC :Q1001 Object Instance: 4 Filter Predicates: SYS_OP_BLOOM_FILTERBF0002,"PE"."PAT_ENC_CSN_ID") Cost: 36 Bytes: 259,210 Cardinality: 18,515 CPU Cost: 122,148 IO Cost: 36 Partition #: 13 Partitions accessed #1 - #464
13 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1002 Cost: 2 Bytes: 765,000 Cardinality: 45,000 CPU Cost: 265,625 IO Cost: 2 Partition #: 15 Partitions accessed #:BF0000
12 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT IMCUST.ORDER_PROC_2 :Q1002 Object Instance: 2 Cost: 2 Bytes: 765,000 Cardinality: 45,000 CPU Cost: 265,625 IO Cost: 2 Partition #: 15 Partitions accessed #:BF0000