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