Re: Order by question - newbie

Csillag Zsolt wrote:
>
> Hi,
>
> I have a table with 3 colums : PersonalCode, Name and SpecialOrder.
>
> The PersonalCode is the primary index and I have also an index on
> SpecialOrder,Name columns.
>
> However the following select:
>
> Select PersonalCode From MyTable
> Order By SpecialOrder,Name
>
> doesn't use the SpecialOrder,Name index at all.
> If I watch it in Explain Plan window I can see that :
>
> SELECT STATEMENT, GOAL =
> CHOOSE 1181 16378 2456700
> SORT ORDER BY 1181 16378 2456700
> TABLE ACCESS FULL ADMIN MyTable 69 16378 2456700
>
> How can I make the index work?
>
> Thank you in advance
>
> Zsolt Csillag
> Hungary

Using the index may not be the best of ideas. All right, everything is ordered in your index (let's totally forget about 'NLS sort', sorting 'logically' for a language which uses accentuated letters as opposed to the numerical ordering of ASCII (or EBCDIC) codes). You need to fully scan the index (since you have no WHERE condition) and for each row get its address (rowid), then fetch the relevant block in the table since the PersonalCode info IS NOT in the index. It is not at all obvious that it will be any better than a full scan, and obviously the optimizer thinks that it is not. You can force the index to be used with hints (use SET AUTOTRACE TRACEONLY under SQL*Plus, run the query without any hint, then run it with the appropriate hint - the best choice is the one with the smallest db block gets + consistent gets).   I don't know your data, but what could SpecialOrder + Name be considered as an alternative PK? If this could be made the 'official' PK, by creating a unique constraint on PersonalCode you could keep all your FKs on it, and you could consider an index-organized table. Really depends on what you want to do.

-- 
Regards,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:    +44  (0) 7050-696-449 
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).