Order by question - newbie
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 = CHOOSE1181163782456700 SORT ORDER BY1181163782456700 TABLE ACCESS FULLADMINMyTable69163782456700 How can I make the index work? Thank you in advance Zsolt Csillag Hungary
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 118116378 2456700 SORT ORDER BY 118116378 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).
RE: Order by question - newbie
Title: Message If your grabing all rows, and your calling all columns (some in order, some display) why wouldn't it do a fts?There is no reason to use the index. Now if you overloaded the index, it may opt to use the index, but to what avail? It is is the same size of the table. In overloading, I refere to puting PersonalCode at the end of the index you created, this way all teh data will be available from the index, there will be no need to go to the table, altough the table will be the same size as the index, but the index will already be sorted. Perhaps you may want to look at IOT, if this isn't high write table. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message-From: Csillag Zsolt [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 28, 2001 3:37 PMTo: Multiple recipients of list ORACLE-LSubject: Order by question - newbie 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,Namedoesn't use the SpecialOrder,Name index at all. If I watch it in Explain Plan window I can see that :SELECT STATEMENT, GOAL = CHOOSE1181163782456700SORT ORDER BY1181163782456700 TABLE ACCESS FULLADMINMyTable69163782456700How can I make the index work? Thank you in advance Zsolt CsillagHungary
