Partition pruning and surrogate key

[avjoshi]

I've often observed that Oracle's partition pruning is not very effective when a query doesn't refer to the partition key in the query.

for example

select d.attribute1, sum(m.measure)
from dimension d, fact m
where d.id = m.d_id
and m.d_id = 12345;

Or,

select d.attribute1, sum(m.measure)
from dimension d, fact m
where d.id = m.d_id
and d.id = 12345;

these queries will do partition pruning very effectively but a query like,

select d.attribute1, sum(m.measure)
from dimension d, fact m
where d.id = m.d_id;

will typically result in full table scan for the fact table.

As it happens in the data warehousing world the primary keys are often surrogate keys and are useless to the users. The id value of 12345 doesn't mean anything to them and hence is typically not exposed to the end user query tool.

What are the options to achieve partition pruning without direct reference to the partition key?

Anand

 

[avjoshi]

Oh, and I forgot to add that in the scenario that i describe the fact table is partitioned on d_id.

Anand