-
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.
-
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 :)
-
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.
-
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
-
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
-
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
-
Why don't you post the SQL statements?
Probably that would help us to determine the use of indexes.
-
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
