query order clause and indices

From: David <thump_at_cosmiccooler.org>
Date: Wed, 8 Dec 2004 13:54:46 -0800 (PST)
Message-ID: <1290.64.37.153.21.1102542886.squirrel@64.37.153.21>

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_AR

TI18_, 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
by item0_.MASTERY_SKILL_LEVEL asc ) where rownum <= :1

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

Received on Wed Dec 08 2004 - 15:57:00 CST