Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index Range Scans....

Re: Index Range Scans....

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 3 Mar 2006 22:09:16 +0000 (UTC)
Message-ID: <duaeqc$qbl$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"Miggins" <mtproc_at_yahoo.co.uk> wrote in message news:1141403933.447534.247520_at_i39g2000cwa.googlegroups.com...
> Gents am running 9.2 on windows. The statement is defined as a cursor
> in a packaged procedure and the 2 bind variables are taken from
> variables stored elsewhere in the program.
>
> Mailing Runs table has around 10,400,00 rows the Mailing Run Details
> table has slightly less.
>
> Mailing run details PK consists of
>
> mrd_mailing_log_id
> mrd_mld_item_seq
> mrd_mru_item_seq
>
> select mr.mru_person_id,
> mr.mru_org_type,
> mr.mru_org_code,
> mrd.mrd_jsu_item_seq
> from mailing_runs mr,
> mailing_run_details mrd
> where mr.mru_mailing_log_id = :BIND1
> and mrd.mrd_mailing_log_id = mr.mru_mailing_log_id
> and mrd.mrd_mld_item_seq = :BIND2
> and mrd.mrd_mru_item_seq = mr.mru_item_seq
>
> Query is taking about 20 mins to run and getting slower and slower as
> the tables grow.
>

The query you've just sent us ISN'T the
same as the query you sent. The real thing is using bind variables, not literals, but more significantly:

    the join is on the first and third columns,     not the second column of the index, and     you only have two constants, not the three     that you claimed.

Your original example should have been more like:

select

     /*+ all_rows ordered use_hash(l) */
     L.f1, L.f2, L.f3, LD.f1, LD.f2, LD.f3
from
     LOG_DETAILS LD,
     LOGS L
where
         L.f1 = to_number(:b1)
and     LD.f1 = L.F1
and     LD.f2 = to_number(:b2)
and     LD.f3 = L.F3

/

This produces the plan:



| Id | Operation | Name | Rows | Bytes | Cost |
|   0 | SELECT STATEMENT     |                  |     1 |    66 |     3 |
|*  1 |  HASH JOIN           |                  |     1 |    66 |     3 |
|*  2 |   INDEX RANGE SCAN   | LOG_DETAILS_PK  |     1 |    33 |     2 |
|*  3 |   INDEX RANGE SCAN   | LOG_PK           |     1 |    33 |     2 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):


   1 - access("LD"."F3"="L"."F3")
   2 - access("LD"."F1"=TO_NUMBER(:Z) AND "LD"."F2"=TO_NUMBER(:Z))
   3 - access("L"."F1"=TO_NUMBER(:Z))


If you take out the hints, I get the plan



| Id | Operation | Name | Rows | Bytes | Cost |
|   0 | SELECT STATEMENT     |                  |       |       |       |
|   1 |  NESTED LOOPS        |                  |       |       |       |
|*  2 |   INDEX RANGE SCAN   | LOG_PK           |       |       |       |
|*  3 |   INDEX UNIQUE SCAN  | LOG_DETIAILS_PK  |       |       |       |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):


   2 - access("L"."F1"=TO_NUMBER(:Z))
   3 - access("LD"."F1"="L"."F1" AND "LD"."F2"=TO_NUMBER(:Z) AND
              "LD"."F3"="L"."F3")

The switch id dependent on volume and scatter of the data in the two tables. But if the optimizer decides to drive off the log_details table with a hash join, it can't use all the available information for early elimination.

You probably have a problem with your statistics - possibly relating to clustering_factor, or uneven distribution of master to detail rows, or possibly a column dependency. As a dirty fix you could simply put in an /*+ ordered use_nl(ld) */ hint, having put the tables in the order

    log, log_details
in the from clause.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Received on Fri Mar 03 2006 - 16:09:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US