SQL & PL/SQL

Hi there

I have a query that I was expecting to execute having performed partition pruning on two partioned tables.

select dly.* , jny.*
from delay dly, journey jny, glyn
where dly.journey_date = glyn.a
and jny.journey_date = glyn.a
and dly.journey_id = jny.journey_id
and dly.virtual_journey_id = jny.virtual_journey_id
and dly.virtual_journey_type = jny.virtual_journey_type
and dly.journey_date = jny.journey_date;

The delay and journey tables are both range partitioned on journey date.

The glyn table is a non partitioned table with 28 rows with a primary key on column A.

The Glyn.A column holds values that relate to two partitions on the journey and delay tables.

I was hoping that the optimiser would partition prune both the Journey and Delay tables before joining them to form the result set.

Howver the execution plan below is used instead.

SELECT STATEMENT ALL_ROWSCost: 13,359 Bytes: 8,211 Cardinality: 17
10 HASH JOIN Cost: 13,359 Bytes: 8,211 Cardinality: 17
7 PART JOIN FILTER CREATE SYS.:BF0000
6 NESTED LOOPS
4 NESTED LOOPS Cost: 1,122 Bytes: 3,894,375 Cardinality: 19,375
1 INDEX FULL SCAN INDEX (UNIQUE) GLYN.PK_GLYN Cost: 1 Bytes: 196 Cardinality: 28
3 PARTITION RANGE ITERATOR Cost: 4 Cardinality: 692
2 INDEX RANGE SCAN INDEX (UNIQUE) GLYN.PK_JOURNEY Cost: 4 Cardinality: 692
5 TABLE ACCESS BY LOCAL INDEX ROWID TABLE GLYN.JOURNEY Cost: 40 Bytes: 134,248 Cardinality: 692
9 PARTITION RANGE JOIN-FILTER Cost: 5,764 Bytes: 127,252,500 Cardinality: 451,250
8 TABLE ACCESS FULL TABLE GLYN.DELAY Cost: 5,764 Bytes: 127,252,500 Cardinality: 451,250

The cost of operation 8 (5764) is the cost of doing a full table scan on the delay table, which I was hoping to avoid.

Any help on this problem really would be appreciated.

Kind Regards

Glyn.