Re: how to partition pruning when joining tables

"linda" <linglipeng_at_yahoo.com> wrote in message news:1118848953.570160.78770_at_g49g2000cwa.googlegroups.com...
> 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
>

The behaviour is driven by:

    version of Oracle
    data sizing (particularly of driving table)     execution path.

Oracle could prune on a nested-loop join, but that's may be blocked by your use of the parallel 6 hint.

Search metalink for the following parameters (shown with their default parameters) and you will find an explanation of how Oracle can run a query against the driving table in a hash join to determine which partitions have to be accessed in the second table.

    _subquery_pruning_enabled = true
    _subquery_pruning_cost_factor = 20
    _subquery_pruning_reduction = 50

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005