select on partition

From: JONL <jon.m.landenburger_at_verizon.com>
Date: 27 Sep 2005 10:27:12 -0700
Message-ID: <1127842032.713346.267310@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? Received on Tue Sep 27 2005 - 12:27:12 CDT