Re: select on partition

"JONL" <jon.m.landenburger_at_verizon.com> wrote in message news:1127842032.713346.267310_at_g43g2000cwa.googlegroups.com...
> We have a large range partitioned table with the partion id being the
> index_bill_ref. When we query it like
> SELECT *
> FROM BILL_INVOICE_DETAIL BID, BILL_INVOICE BI
> WHERE to_char(BI.to_date, 'MON-YYYY') = 'SEP-2005'
> and BID.index_bill_ref = BI.index_bill_ref ;
>
> We get a plan like
> SELECT STATEMENT 42M 17G 598789
> HASH JOIN 42M 17G 598789
> TABLE ACCESS FULL BILL_INVOICE 72 17K 18
> PARTITION RANGE ALL
> TABLE ACCESS FULL BILL_INVOICE_DETAIL 688M 116G 575124
>
> If instead we select directly from the partition:
> FROM BILL_INVOICE_DETAIL partition (BID_DATA_P57) "BID",
> Bill_invoice BI
> we get a plan a much better plan
> SELECT STATEMENT 1 397 40
> TABLE ACCESS BY LOCAL INDEX ROWID BILL_INVOICE_DETAIL 1 147 1
> NESTED LOOPS 1 397 40
> TABLE ACCESS FULL BILL_INVOICE 72 17K 18
> INDEX RANGE SCAN BILL_INVOICE_DETAIL_PK 950 1
>
> I would think tha Oracle would figure t+he partion out right quickly so
> why the big difference in plans?
>

You haven't mention the version of Oracle you are using. It looks like you are having trouble with Oracle failing to do a pre-run test known as "subquery pruning".

If you have access to MetaLink, check note 209070.1 which explains your problem, and give you one workaround if the optimizer calculations are going wrong.

The issue is that Oracle cannot detect which partitions you will need until it has selected the index_bill_refs from the first table. So there is a mechanism for a 'pre-query' query to identify the partitions you will need by querying the BID table for all the index_bill_ref identified by your main predicate, and working out which partition they come from in the BI table.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005