how to partition pruning when joining tables

Hi,

I found it that you need to hard-code the partition key values in the where clause for oracle to do partition pruning. If you join tables together and one of the table has the partition key values in it, oracle somehow won't prune partitions. Example:

• will prune --pmt_cyl_st_id is the partition key

select /*+ PARALLEL(lfs 16) */

   fncl_ast_id,ln_lpi_dt
from ln_fncl_st lfs
where lfs.pmt_cyl_st_id in (300638318, 300638330) ;

• won't prune insert into ids(id) values (300638318); insert into ids(id) values (300638330); commit; select /*+ PARALLEL(lfs 16) */ fncl_ast_id,ln_lpi_dt from ln_fncl_st lfs, ids where lfs.pmt_cyl_st_id = ids.id ;

Do you ever experience the same? How can you make Oracle to prune partition when the values are in a table?

Thanks,
Linda Received on Wed Jun 15 2005 - 10:22:33 CDT