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: Why Sort...?

Re: Why Sort...?

From: Richard A Foote <richard.foote_at_tbigpond.nospam.com>
Date: Fri, 30 Apr 2004 13:25:09 GMT
Message-ID: <Vsskc.5310$TT.3459@news-server.bigpond.net.au>


"Matt" <mccmx_at_hotmail.com> wrote in message news:cfee5bcf.0404300321.42bf0ee9_at_posting.google.com...
> I have created a composite index on a table which includes 3
> descending columns...
>
> create index ps_job_desc on ps_job(emplid, empl_rcd, effdt desc,
> effseq desc)
>
> Why does the following query perform a SORT operation..?
>
> 1 select emplid, empl_rcd, effdt, effseq from ps_job
> 2* order by emplid, empl_rcd, effdt desc, effseq desc
> SQL> /
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=651 Card=162094 Byte
> s=2917692)
>
> 1 0 SORT (ORDER BY) (Cost=651 Card=162094 Bytes=2917692)
> 2 1 INDEX (FAST FULL SCAN) OF 'PS_JOB_DESC' (NON-UNIQUE) (Co
> st=4 Card=162094 Bytes=2917692)
>
> It uses my index but it still performs a sort despite the fact that
> Oracle has stored the data in the order I have requested it....
>
> Version 8.1.7.4.1 on W2K
>

Hi Mat

As usual, it's a matter of comparative costs. The cost of reading the index "efficiently" via an Index Fast Full Scan (via multiblock reads) + cost of sort *is less than* the cost of reading the entire index "inefficiently" (via single block reads navigating the index structure) without the need for the sort.

Note that a IFFS does not read the blocks in the logical order of the index structure and simply performs multiblock reads up to the point of the index high water mark, discarding branch blocks along the way and requiring a sort as a consequence. However, depending on the db_file_multiblock_read_count parameter, this can be viewed a being relatively inexpensive by the CBO (note that it only has a cost of 4 suggesting the d_f_m_r_c is quite high). Add the cost of the sort (dependent on the sort_area_size) and we have our first cost.

By using the index as you suggest, Oracle needs to use the branch blocks to navigate to the first leaf block (one logical I/O for each level) and then read each and every leaf block, again one LIO at a time. Although it avoids the sort, the additional "costs" of reading each block one LIO is resulting in the CBO not selecting this plan as this second cost is higher than the one above.

Based on the raw statistics in front of me, it suggests you have either a 2K or 4K blocksize making the number of LIOs greater than 651 for a full index scan, you have a really high d_f_m_b_r_c as your IFFS costing is so low and a default value for the OPTIMIZER_INDEX_COST_ADJ parameter.

Adjusting the above as appropriate (and possibly the S_A_S) may impact the CBO to change it's execution plan accordingly.

Cheers

Richard Received on Fri Apr 30 2004 - 08:25:09 CDT

Original text of this message

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