query order clause and indices
- From: "David" <thump@xxxxxxxxxxxxxxxx>
- To: "Oracle Mailing List" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 8 Dec 2004 13:54:46 -0800 (PST)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
