Hi All ,

I have a problem in Identifying Columns while creating Index for the following query .

could anyone please suggest what columns and order to be present in Index Creation .

I have created Index on following Columns ..

VERSION_ID, QUEST_ID, LONG_TXT_ID, CDNG_VAL

Table Structure :

CREATE TABLE CDNG_HIER
(
VERSION_ID NUMBER,
QUEST_ID NUMBER,
CDNG_VAL NUMBER,
CDNG_LVL NUMBER,
LONG_TXT_ID NUMBER
)


Query :

select ch.quest_id,cdng_val,cdng_lvl,long_txt,short_txt from cdng_hier ch,long_txt lt,cdng_quest cq
where ch.version_id = 23456
and lt.long_txt_id = ch.long_txt_id
and ch.quest_id = cq.quest_id
and cq.version_id = ch.version_id
and cq.cdng_nbr in (SELECT CDNG_NBR FROM USER_GRP_CDNG_ACCESS WHERE VERSION_ID = 23456
AND USER_ID = 'test123' )
ORDER BY ch.rowid


Explain Plan :

SELECT STATEMENT Optimizer=CHOOSE (Cost=95 Card=3946 Bytes=516926)
SORT (ORDER BY) (Cost=95 Card=3946 Bytes=516926)
NESTED LOOPS (Cost=14 Card=3946 Bytes=516926)
NESTED LOOPS (Cost=12 Card=2 Bytes=166)
NESTED LOOPS (Cost=9 Card=1 Bytes=58)
VIEW OF VW_NSO_1 (Cost=6 Card=1 Bytes=13)
SORT (UNIQUE) (Cost=6 Card=1 Bytes=19)
INDEX (RANGE SCAN) OF USER_GRP_CDNG_ACCESS_PK (UNIQUE) (Cost=2 Card=1 Bytes=19)
TABLE ACCESS (BY INDEX ROWID) OF CDNG_QUEST (Cost=3 Card=23 Bytes=1035)
INDEX (RANGE SCAN) OF XIF156CDNG_QUEST (NON-UNIQUE) (Cost=1 Card=23)
TABLE ACCESS (BY INDEX ROWID) OF CDNG_HIER (Cost=3 Card=169 Bytes=4225)
INDEX (RANGE SCAN) OF PK_CDNG_HIER (UNIQUE) (Cost=2 Card=169)
TABLE ACCESS (BY INDEX ROWID) OF LONG_TXT (Cost=1 Card=197317 Bytes=9471216)
INDEX (UNIQUE SCAN) OF LONG_TXT_PK (UNIQUE)


Thanks for your Help ...
I need to give explanation to my manager what is the best suitable Index .

Appreciate your time for identifying Columns for Index Creation .....