-
Problem in Identifying columns in Index creation
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 .....
-
I think we need to Include all the columns in the where clause for better performance of Index .
please let me know if i am missing anything
if anyone can interpret Explain Plan that would be of geat Help .
-
Re: Problem in Identifying columns in Index creation
Hi
Can you please post all the table structures used in your query ,so that I can suggest you the right indexes need to be created for your query.
Regards
Rohit S Nirkhe.
Oracle/Apps DBA
OCP 8i,9i
-
Hi rohit
pls find all the table structure details used in the query .
===================
CREATE TABLE CDNG_HIER
(
VERSION_ID NUMBER,
QUEST_ID NUMBER,
CDNG_VAL NUMBER,
CDNG_LVL NUMBER,
LONG_TXT_ID NUMBER
)
========================
cdng_quest
CREATE TABLE CDNG_QUEST
(
QUEST_ID NUMBER NOT NULL,
VERSION_ID NUMBER NOT NULL,
CDNG_NBR NUMBER NOT NULL,
LONG_TXT_ID NUMBER NOT NULL,
CDNG_PRE_TXT_ID NUMBER NOT NULL,
CDNG_POST_TXT_ID NUMBER NOT NULL,
CDNG_INSRT_FLG NUMBER DEFAULT 0 NOT NULL,
QUEST_FLG NUMBER DEFAULT 1 NOT NULL,
FLEXIBLE_FLG NUMBER DEFAULT 0 NOT NULL,
SHORT_TXT VARCHAR2(255)
)
CREATE INDEX XIF156CDNG_QUEST ON CDNG_QUEST
(VERSION_ID)
CREATE UNIQUE INDEX CDNG_QUEST_PK ON CDNG_QUEST
(QUEST_ID)
CREATE INDEX XIF190CDNG_QUEST ON CDNG_QUEST
(CDNG_PRE_TXT_ID)
CREATE INDEX XIF205CDNG_QUEST ON CDNG_QUEST
(LONG_TXT_ID)
==================================
long_txt
CREATE TABLE LONG_TXT
(
LONG_TXT_ID NUMBER NOT NULL,
HASH_TXT VARCHAR2(8) NOT NULL,
LONG_TXT_TYP_IND VARCHAR2(2) NOT NULL,
LONG_TXT VARCHAR2(255) NOT NULL
)
ALTER TABLE LONG_TXT ADD (
CONSTRAINT LONG_TXT_PK PRIMARY KEY (LONG_TXT_ID)
CREATE UNIQUE INDEX LONG_TXT_PK ON LONG_TXT
(LONG_TXT_ID)
CREATE INDEX HASH_IDX_LONG_TXT ON LONG_TXT
(HASH_TXT)
-
What about changing the IN clause to an exists?
If the columns that I listed are not indexed then
they should be. Sometimes a concatenated index
is the way to go. But not always.
Code:
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
ch.long_txt_id = lt.long_txt_id AND
ch.quest_id = cq.quest_id AND
ch.version_id = cq.version_id AND
cq.cdng_nbr EXISTS
( SELECT cdng_nbr
FROM user_grp_cdng_access
WHERE version_id = 23456 AND
user_id = 'test123' )
ORDER BY ch.ROWID;
CREATE INDEX idx1 ON cdng_hier
( version_id,
long_txt_id,
quest_id,
version_id );
-
CREATE INDEX idx1 ON cdng_hier
( version_id, ----------------->
long_txt_id,
quest_id,
version_id ); --------------->
Can you create this index?
==============
I need to give explanation to my manager what is the best suitable Index .
==============
Is your manager a IT guy or a Accountant guy?
Tamil
Last edited by tamilselvan; 07-20-2004 at 12:21 PM.
-
Originally posted by tamilselvan
[B]
CREATE INDEX idx1 ON cdng_hier
( version_id, ----------------->
long_txt_id,
quest_id,
version_id ); --------------->
Can you create this index?
Apparenlty not. How about:
CREATE INDEX idx1 ON cdng_hier
( version_id,
long_txt_id,
quest_id);
That's why your the moderator and I'm not.
-
cost is same in both cases .
CREATE INDEX idx1 ON cdng_hier
( version_id,
long_txt_id,
quest_id);
if i replace IN with EXISTS clause
it gives following message
ORA-00920: invalid relational operator
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
ch.long_txt_id = lt.long_txt_id AND
ch.quest_id = cq.quest_id AND
ch.version_id = cq.version_id AND
cq.cdng_nbr EXISTS
( SELECT cdng_nbr
FROM user_grp_cdng_access
WHERE version_id = 23456 AND
user_id = 'test123' )
ORDER BY ch.ROWID;
-
Sorry, I should have seen that the field name should not have been in the where clause. Using EXISTS rather than IN changes a noncorrelated query to a correlated query. Which eliminates the nested loops.
Code:
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
ch.long_txt_id = lt.long_txt_id AND
ch.quest_id = cq.quest_id AND
ch.version_id = cq.version_id AND
EXISTS
( SELECT cdng_nbr
FROM user_grp_cdng_access
WHERE version_id = 23456 AND
user_id = 'test123' )
ORDER BY ch.ROWID;
-
Cost is increasing if i Use EXISTS clause instead of IN
--------------------------------------------------------
i also have primary key on all 4 columns ...
anyway i guess we can leave this query and Index like this .
SELECT STATEMENT Optimizer=CHOOSE (Cost=1400 Card=76954 Bytes=8772756)
SORT (ORDER BY) (Cost=1400 Card=76954 Bytes=8772756)
FILTER
NESTED LOOPS (Cost=49 Card=76954 Bytes=8772756)
HASH JOIN (Cost=10 Card=39 Bytes=2574)
TABLE ACCESS (BY INDEX ROWID) OF CDNG_QUEST (Cost=3 Card=23 Bytes=943)
INDEX (RANGE SCAN) OF XIF156CDNG_QUEST (NON-UNIQUE) (Cost=1 Card=23)
TABLE ACCESS (BY INDEX ROWID) OF CDNG_HIER (Cost=6 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)
INDEX (RANGE SCAN) OF USER_GRP_CDNG_ACCESS_PK (UNIQUE) (Cost=2 Card=1 Bytes=15)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|