| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: query order clause and indices
Gut feel would say that even though it doesn't appear to use the index perhaps it actually is at execution time - there is no other way the presence of an index can change the result set.
Also, you already have a hint to use an index on the "item0_" table at the start of the statement - this is quite possibly preventing it from using the index you expect. You could modify the hint to your preferred index or consider re-adding the order by clause...
Without an "order by" clause you are essentially relying on luck to order the results - what happens if the index is dropped or even just renamed slightly different (if referred to in a hint)? You need to decide if you can accept that risk or not. Also, there are scenario's where Oracle can see an order by clause and recognise that a particular index has already ordered the results that way - in this scenario is doesn't always need to perform the sort operation. The beauty of this approach is that it works efficiently normally but even if something happens to the index then your result set is still valid - it will just take longer.
Plenty of places rely on hints (our application has many instances of this due to some very large data sets where we just want the first 10, 100, 1000, whatever) - you just need to be aware of the risk and understand what will happen when something goes wrong.
Regards,
Mark.
PS: Right now your query is doing a full index scan of the PK_ITEM_ID index since there is no selection criteria and using the subquery to effect a join is perhaps an overly complicated approach. With such a small number of rows being returned perhaps just simplifying the query and removing the index hint may be enough - a table scan isn't always a bad way to go about things.
|---------+----------------------------->
| | "David" |
| | <thump_at_cosmiccoole|
| | r.org> |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 09/12/2004 08:54 |
| | Please respond to |
| | thump |
|---------+-----------------------------> >----------------------------------------------------------------------------------------------------------------------| | | | To: "Oracle Mailing List" <oracle-l_at_freelists.org> | | cc: | | Subject: query order clause and indices | >----------------------------------------------------------------------------------------------------------------------|
I have a query:
select * from ( select /*+ INDEX (item0_, PK_ITEM_ID) */ item0_.ITEM_ID as
ITEM_ID, item0_.ICON as ICON, item0_.WEIGHT as WEIGHT, item0_.DELAY as
DELAY, item0_.RANGE as RANGE, item0_.MIN_MASTERY_DAMAGE as MIN_MAST6_,
item0_.MAX_MASTERY_DAMAGE as MAX_MAST7_, item0_.AVAILABLE_SLOTS as
AVAILABL8_, item0_.WEIGHT_REDUCTION as
WEIGHT_R9_,item0_.MASTERY_SKILL_LEVEL as MASTERY10_, item0_.
MINIMUM_SKILL_LEVEL as MINIMUM11_, item0_.IS_NO_RENT as IS_NO_RE
NT, item0_.IS_NO_DROP as IS_NO_DROP, item0_.IS_NO_TRADE as IS_NO _T14_, item0_.IS_LORE as IS_LORE, item0_.IS_NO_ZONE as IS_NO_ZON E, item0_.IS_NO_VALUE as IS_NO_V17_, item0_.IS_ARTIFACT as IS_ARTI18_, item0_.IS_ATTUNABLE as IS_ATTU19_, item0_.ITEM_CLASS_NAME as ITEM_CL20_, item0_.MASTERY_ARMOR_CLASS as MASTERY21_, item0_ .MASTERY_SHIELD_FACTOR as MASTERY22_, item0_.MINIMUM_ARMOR_CLASS as MINIMUM23_, item0_.MINIMUM_SHIELD_FACTOR as MINIMUM24_, item 0_.WIELD_STYLE_NAME as WIELD_S25_, item0_.KNOWLEDGE_NAME as KNOW LED26_, item0_.SPELL_CAST_TIME as SPELL_C27_, item0_.SPELL_CONCE NTRATION_COST as SPELL_C28_, item0_.SPELL_HEALTH_COST as SPELL_H 29_, item0_.SPELL_MANA_COST as SPELL_M30_, item0_.SPELL_RECAST_T IME as SPELL_R31_, item0_.SPELL_RECOVERY_TIME as SPELL_R32_ from rep.ITEMS item0_ where ('0' in(select slot2_.SLOT_ID from REP.ITEM_SLOTS slots1_, rep.SLOTS slot2_ where item0_.ITEM_ID=slots1_.ITEM_ID and slots1_.SLOT_ID=slot2_.SLOT_ID)) order
It does this with almost 50,000 LIO's:
| Operation | PHV/Object Name | Rows | Bytes|Cost |
|SELECT STATEMENT |----- 4034042622 ----| | |
154 |
|COUNT STOPKEY | | | |
|
| VIEW | | 686 | 306K|
154 |
| SORT ORDER BY STOPKEY | | 686 | 65K|
154 |
| TABLE ACCESS BY INDEX ROWID |ITEMS | 686 | 65K|
126 |
| INDEX FULL SCAN |PK_ITEM_ID | 686 | |
46 |
| NESTED LOOPS | | 1 | 12 |
3 |
| INDEX UNIQUE SCAN |PK_SLOT_ID | 1 | 3 |
|
| INDEX UNIQUE SCAN |PK_ITEM_SLOTS | 1 | 9 |
|
I removed the order by clause and instead added: AND item0_.mastery_skill_level > 0
I also added an index on that column.
Now the query performs with 1200 LIO's, however I do not see the index
being used in the explain plan. It does a FTS on ITEMS. Without the
index though the data returns in the incorrect order, but with the index
the data returns in the correct order regardless of index being in explain
plan or not. I even tried hinting to use the index on mastery_skill_level
and thwe order of the returning data is the same, but LIO's slightly
higher.
With the index not showing up in the explain plan, I can't understand how it is affecting order and giving me the desired results. IF I drop the index, then the order changea dn order by clause is needed....
Any insight is appreciated!
9205 on Linux
Cheers+
-- .. David -- http://www.freelists.org/webpage/oracle-l <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 08 2004 - 16:19:00 CST
 |
 |