Re: Problem with understanding Optimization methods.

Check the costs of the two queries (autotrace will be sufficient).

In this example, rule based uses the index because it exists and will return the right answer.

Cost based works out that the scan and sort is cheaper.

The cost of an index full scan is approximately

    blevel + leaf_blocks (columns from user_indexes).

The cost of the tablescan is

    blocks / 'adjusted db_file_multiblock_read_count' If your dbfmbrc is 8, use 6.59
If it's 16 use 10
If it's 32 use 16.4

The cost of the sort (which seems to be wrong in 8.1.7.4) is likely to be about the same as the cost of the tablescan.

So, as an example, pretend your dbfmbrc is 16, then if the index is larger than 1/5th of the size of the table, the scan and sort will work out cheaper than the index full scan.

I am a little surprised, though, that you don't have a path that is "index FAST full scan". This suggests that your index is actually bigger than your table. Maybe it's got
lots of holes in it.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___November

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

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, January 07, 2004 5:29 AM

> Hello Listers,
>
> A normal sql query from a data warehouse tool called Sagent.
> SELECT COL1, COL2, COL3
> FROM TABLE
> ORDER BY 3;
>
> The table has approximately 2 mil records.
> table has 22 indexes.
>
> The database is set up optimizer CHOOSE.
> I run DBMS_Stats.Gather_Schema_Stats('SchemaName') regularly.
> OS is Win2k
> ORACLE 81741
>
> OK, when doing a explain plan on the above sql, I get the following...
> SELECT STATEMENT Optimizer Mode=CHOOSE
> SORT ORDER BY
> TABLE ACCESS FULL TABLENAME -- Very slow and takes
> hours!
>
> When adding the hint /*+RULE*/ for example I get
> SELECT STATEMENT Optimizer Mode=Hint:RULE
> TABLE ACCESS BY INDEX ROWID TABLENAME
> INDEX FULL SCAN TABLE_INDEX --
> Much faster!!!
>
> Have I given enough info that anyone can explain why the CHOOSE mode
insists
> on doing a TABLE ACCESS FULL?
> Is there anything I can do to improve performance? Please remember that
this
> query comes from a Data Warehouse tool and hence does not appear to accept
> hints.
>
> Any help will be much appreciated!
> Denham
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).