Home » RDBMS Server » Performance Tuning » Tuning/index
Tuning/index [message #65143] Mon, 17 May 2004 02:44 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #65147 is a reply to message #65146] Tue, 18 May 2004 00:48 Go to previous messageGo to next message
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
Senior Member
Hi,

Give me ur mail id i will mail a document for the same.

Daljit Singh.
Re: Tuning/index [message #65149 is a reply to message #65147] Wed, 19 May 2004 00:11 Go to previous messageGo to next message
Sujit Sarkar
Messages: 40
Registered: September 2003
Member
Dear Daljit

You can send me the proposed document in my mail id
1. sujit_ocp@rediffmail.com
2. sujitsrkr@rediffmail.com

thanx a lot for the help

Sujit
Re: Tuning/index [message #65188 is a reply to message #65145] Wed, 02 June 2004 22:13 Go to previous message
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
Previous Topic: How to improve performance on insertion, deletion and update
Next Topic: How to improve efficiency of Oracle 9i APS
Goto Forum:
  


Current Time: Thu Mar 28 12:22:18 CDT 2024