| Tuning/index [message #65143] |
Mon, 17 May 2004 02:44  |
shweta
Messages: 8
Registered: May 2001
|
Junior Member |
|
|
I use oracle 9i.This is the query I execute in The OEM Pack for tuning.
SELECT DISTINCT zz.comp_appl_id
FROM lot_workflowstage_dtl zz WHERE zz."STATUS" <> 'X' AND zz.companyid = 2000
AND NOT EXISTS (SELECT 'x' FROM lot_workflowstage_dtl yy WHERE yy."STATUS" <> 'X'
AND yy.companyid = 2000 AND yy.stagestatus ='J' AND stage <> 'SLSACLNO' AND stage <> 'CSTACOFF'
AND yy.comp_appl_id = zz.comp_appl_id)
The reprot Generated is:
A new B*-tree index is recommended for the table SCB.
LOT_WORKFLOWSTAGE_DTL.
Recommended columns
-------------------
COMPANYID
There was at least one reference by an equality operator.
STAGESTATUS
There was at least one reference by an equality operator.
COMP_APPL_ID
There was at least one reference by an equality operator.
There was at least one reference by SELECT, ORDER-BY or GROUP-BY
clauses.
Number of rows in the table: 12317
Distinct values in the index: 792
******************** SQL Statement Information ********************
The recommendation above is based on the following SQL:
SQL: SELECT DISTINCT zz.comp_appl_id
FROM lot_workflowstage_dtl zz
WHERE zz."STATUS" <> 'X'
AND zz.companyid = 2000
AND NOT EXISTS (SELECT 'x'
FROM lot_workflowstage_dtl yy
WHERE yy."STATUS" <> 'X'
AND yy.companyid = 2000
AND yy.stagestatus = 'J'
AND yy.comp_appl_id = zz.
comp_appl_id)
I made single index on all this three fields and again Tunned it but got the same report as above.After this i made a composite Normal index and it
wrked fine.
Sir, My question is as far as i know oracle 9i suggests that composite index should be avoided and single index should be used.
Could You Pls. help me.
Thanks a lot in advance
|
|
|
|
| Re: Tuning/index [message #65145 is a reply to message #65143] |
Mon, 17 May 2004 20:23  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
First of let me explain you that I amd not agree with you this statement : "oracle 9i suggests that composite index should be avoided and single index should be used."
It totally depends on various factors whether to go for a single column index or for multi column index. Moreover in oracle 9i there is a new feature index scip scan in which there is no need to use the leading column of a multicolumn index in your query. And if you ask me, i would say that composite indexes are more powerfull than single column indexes, because much data is avaiable in index itself and there is no need to scan the table to check any condition. Simply in your case, if you create 3 diff indexes on diff fields than also oracle will use only one index and for that values oracle will scan the table, but if you have a composite index in that 3 fields than oracle will use that index and all the data will be available in index itself.
I hope it will help you. And for more info on indexes & tuning refer oracle doc.
Daljit Singh.
|
|
|
|
| Re: Tuning/index [message #65146 is a reply to message #65145] |
Mon, 17 May 2004 22:33 |
Sujit Sarkar
Messages: 40
Registered: September 2003
|
Member |
|
|
Dear Sir,
I have written in your reply "in oracle 9i there is a new feature index scip scan in which there is no need to use the leading column of a multicolumn index in your query"
can you pls explain what is the meaning of this paragraph . If possible in detail
thanx & rgrds
Sujit
|
|
|
|
|
|
|
|
| Re: Tuning/index [message #65188 is a reply to message #65145] |
Wed, 02 June 2004 22:13 |
Vikash
Messages: 17
Registered: April 2002
|
Junior Member |
|
|
Dear Sir,
Could u pls forward me the document on the new features on the statement below.
"in oracle 9i there is a new feature index scip scan in which there is no need to use the leading column of a multicolumn index in your query"
MY mail add is vs_2601@hotmail.com
Thanks,
Vikash
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
