Problem in Identifying columns in Index creation
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Problem in Identifying columns in Index creation

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462

    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 .....

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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 .

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530

    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
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

  4. #4
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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)

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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 );

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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 01:21 PM.

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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.

  8. #8
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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;

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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;

  10. #10
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    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
  •  


Click Here to Expand Forum to Full Width