Re: select on partition

"JONL" <jon.m.landenburger_at_verizon.com> wrote in message news:1127848200.131754.143480_at_g47g2000cwa.googlegroups.com...
> thanks for the response. WE are using 9i cbo after being in 8 using
> rule. I dont have access to metalink but I will work that out with
> our dba. Without sounding too ignorrant by "a 'pre-query' query to
> identify the partitions you will need " do you mean to use multiple
> queries instead of a singe join. Something like
>
> get index_bill_refs from Bill_invoice
> foreach index_bill_ref
> query Bill_invoice_detail
> or
> get index_bill_refs from Bill_invoice
> discern index_bill_ref (from tab_partitions)
> get data from partition
>
> This in fact is what we are finding to be very helpful. Just did'nt we
> would have to resort to that.
>

You don't have to drive the thing manually, the optimizer will decide to run a query with the following structure automatically as part of its optimization.

rem SELECT distinct TBL$OR$IDX$PART$NUM("T1", 0, 1, 0, "ID") rem FROM (
rem SELECT "D"."ID" "ID"
rem FROM "DRIVER" "D"
rem WHERE "D"."YEAR"=2003
rem ) ORDER BY 1
rem

The TBL$OR$IDX$PART$NUM("T1", 0, 1, 0, "ID") function allows it to build a list of all the partitions in the target table that it will have to visit.

The decision to do this pre-query is driven by three hidden parameters, defaulting to:

rem  _subquery_pruning_enabled = true
rem  _subquery_pruning_cost_factor = 20
rem  _subquery_pruning_reduction = 50

You can force the pre-query to take place by telling the optimizer that it's a really good idea and bound to save resources by setting

_subquery_pruning_cost_factor = 1
_subquery_pruning_reduction = 100

Of course, you shouldn't set hidden parameters without checking with Oracle support first - so if you have a support contract, raise an iTAR to ask them if this is okay. But use a test database to see if it works with your data.

Looking at your original query, I think Oracle should have done the pruning under the default values of these parameters - so perhaps you need to check the state of your statistics.

-- 
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