Composite index help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Composite index help

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    I have ran Oracle Expert to find out the index recommendations.
    I was confused about the following indexes, so I need your help !!
    I am using Oracle 8.0.5.
    Here are the recommendations by Oracle expert:

    CREATE INDEX PR.IX_AUTH_WORK
    ON PR.MWEBAUTH
    (AUTH_WORK_ID,
    AUTH_RES_ID,
    AUTH_SECONDARY_STATUS,
    AUTH_STATUS,
    AUTH_RESTRICTED,
    AUTH_TIMECARD,
    AUTH_REM_AMOUNT,
    AUTH_AMOUNT,
    AUTH_ID)
    LOGGING
    INITRANS 2
    MAXTRANS 255
    TABLESPACE PROD
    PCTFREE 10
    STORAGE (
    INITIAL 500K
    NEXT 500K
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 1024);

    CREATE INDEX PR.MWEBAUTH_IDX_004
    ON PR.MWEBAUTH
    (AUTH_RES_ID,
    AUTH_RESTRICTED,
    AUTH_TIMECARD,
    AUTH_WORK_ID,
    AUTH_START_DATE,
    AUTH_FIN_DATE,
    AUTH_ID,
    AUTH_SCHEDULE,
    AUTH_REM_AMOUNT)
    TABLESPACE PROD
    PCTFREE 10
    STORAGE (
    INITIAL 500K
    NEXT 500K
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 1024);

    CREATE INDEX PR.MWEBAUTH_IDX_005
    ON PR.MWEBAUTH
    (AUTH_STATUS,
    AUTH_RESTRICTED,
    AUTH_RES_ID,
    AUTH_START_DATE,
    AUTH_FIN_DATE,
    AUTH_ID,
    AUTH_WORK_ID)
    TABLESPACE PROD
    PCTFREE 10
    STORAGE (
    INITIAL 500K
    NEXT 500K
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 1024);

    CREATE INDEX PR.MWEBAUTH_IDX_015
    ON PR.MWEBAUTH
    (AUTH_SECONDARY_STATUS,
    AUTH_WORK_ID,
    AUTH_RES_ID,
    AUTH_START_DATE,
    AUTH_FIN_DATE,
    AUTH_ID)
    TABLESPACE PROD
    PCTFREE 10
    STORAGE (
    INITIAL 500K
    NEXT 500K
    PCTINCREASE 0
    MINEXTENTS 1
    MAXEXTENTS 1024);

    Que1. Auth_ID is a Primary key on this table, then why do you have to include that to
    a composite index.
    Que2. Why is it recommending to do LOGGING on only one composite index IX_AUTH_WORK
    and not all ?
    Que3. AUTH_WORK_ID and AUTH_RES_ID are foreign keys, should these be 1st in the
    composite index to make sure that they will be used always !
    Que4. Will separate indexes make it better than composite indexes.
    Sonali

  2. #2
    Oracle Expert searches the load in the sample, he is doing the recommendation according the wheres in the statements in the sample.
    where a=1 and b=2 and c=3
    Index in a will be used
    Index in a,b will be used
    Index in a,b,c will be used
    If you have index in a and index in b and index in c then only in a will be used.
    If c is the primary key, the index of the PK will not be used (it is not the lead in the where)
    I don't know why the logging on that specific index :( , anyway he is not recommending nologging in the others
    Ramon Caballero, DBA, rcaballe@yahoo.com

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    Thanks, I know how the composite index work. I had some other questions ....

    Que1. Auth_ID is a Primary key on this table, then why do you have to include that to
    a composite index.

    If this is not used then adding it to a composite index make it worst ?

    Que2. Why is it recommending to do LOGGING on only one composite index IX_AUTH_WORK
    and not all ?
    Que3. AUTH_WORK_ID and AUTH_RES_ID are foreign keys, should these be 1st in the
    composite index to make sure that they will be used always !
    Que4. Will separate indexes make it better than composite indexes.
    Sonali

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I wouldnt trust too mcuh Oracle Expert´s advice, it bases the recommendation with stats in the shared pool. If you look some of the recommendations they are non-sense

  5. #5
    Join Date
    Jan 2001
    Posts
    318
    Hi Pando,
    Thanks... this is what I was thinking. But this is sent by one of our customers. I want to do a good job for them.
    What do you think, where should I start tuining these ?? Do I have to scan through all queries running behind for this part ?

    The indexes recommended are all mangled !!!

    Is logging really required, if so should I put them on all ?

    Am I right about putting FK column 1st in indexes ?


    thanks a lot again

    Sonali
    Sonali

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I dont know what is the exact way but we only tune SQL when we find a process is slow then we just trace the session that executed the query then from trace file and tkprof we can gather the statistics and tune based on those stats, LOGGING, by default it´s set to LOGGING so all the recomemndations are actually using LOGGIN I dont know why it explicitly specified in the first but all others are using LOGGING too!

    You are probably right about putting FK in the 1st column so consequently that FK is indexed thus avoid parent table blocking when simple joins are perfomed, FK will always be used as an index.. well sort of you know what mean if you know how the order of columns affects composite indexes

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Why don't you post the SQL statements?
    Probably that would help us to determine the use of indexes.

  8. #8
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Solution

    Hi, 14th May 2001 21:26 hrs *******

    4).it all depends upon the application

    In the real in a very big banking project i have seen several composite index in the application.In tuning for a particular statement it took me around 150 minutes.The reason was in the join condition column was second in order of the composite index.So i gave index for the particular column and the performance was tremondously came down to 30 seconds.

    So when i proposed to drop the composite index i was adviced it may be used somewhere in other part of the application where its usage is required and after the investigation it was found to be true.

    Oracle recommends always not to use composite index or tells to avoid the same.

    3)If the foreign keys are indexed are it is helpful from locking mechanism.Read a very interesting point from OTN link below.

    http://otn.oracle.com/support/bboard/content/717.htm

    for your question it depends on what order it is defined in the primary key.

    2)Logging==>AUTH_WORK_ID can you please let me know from your application it is anywhere using parallel query option ? and does it has already any index also please let me know the the following columns have indexes

    AUTH_RES_ID
    AUTH_STATUS
    AUTH_SECONDARY_STATUS

    Ok i think i got the solution now i think as you have said in 3 point are the foreign keys multiple column say AUTH_WORK_ID ,AUTH_RES_ID or individual i hope you after the investigation you would have understood .Please let me know the informations i have requested.

    1)For 1 you got the answer from 4.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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