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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
