Re: select on partition

"JONL" <jon.m.landenburger@xxxxxxxxxxx> wrote in message
news:1127842032.713346.267310@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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



.

Relevant Pages

  • Re: Table function parameters dont seem to be working
    ... I have a query I'm trying to write using a table function. ... Explain Plan: ... Partition range iterator ... PARTITION RANGE ITERATOR ...
    (comp.databases.oracle.server)
  • Re: [opensuse] Use entire disk, and not a partition
    ... We are using Oracle. ... Oracle uses filesystems to create datafiles ... use the entire device, not just a partition. ... Are there file systems that access multiple partitions? ...
    (SuSE)
  • Re: row migaration
    ... The rowid remains the same as Oracle keeps a pointer to ... > means an extra IO is required to fetch the row when the fetch is via ... Like wise changing of a column that is part of the partion key ... > partition to the correct partition for its partition key. ...
    (comp.databases.oracle.server)
  • Re: Licensing question
    ... Each box was physically fitted with x number of processors, but the customer was only allowed to use a subset of them until the additional "cores" had been licensed with IBM. ... I wasn't party as to how the Oracle licences were negotiated for the particular database server LPARs, but doubtless it would have been entertaining. ... So if you have a 4 core CPU, and partition in 2 partitions with 2 cores each, you can license one partition for Oracle, so pay for only two cores. ...
    (comp.databases.oracle.server)
  • Re: Table Partitioning Question
    ... They also only have global indexes on these tables. ... I know from using an explain plan on a query that even with a date specified ... in the query that Oracle will go through every partition instead of only the ... Does this Oracle installation have a version number? ...
    (comp.databases.oracle.server)