Home » RDBMS Server » Performance Tuning » use of index in performance tuning (oracle 8i)
use of index in performance tuning [message #347075] Wed, 10 September 2008 10:21 Go to next message
Messages: 2
Registered: May 2007
Junior Member
If I have a table tab having column c1,c2,c3,c3,c5 having millions of records.

I created composite index on (c1,c2,c3).

Will the index be use if I fire the following query

1)select * from tab
where c1=somevalue(say 1)

only one column is used.

and now??

2)select * from tab
where c1= 1
and c2=1
and c3=1

here all three columns are used.

in which condition the index will be actually used.
Re: use of index in performance tuning [message #347082 is a reply to message #347075] Wed, 10 September 2008 10:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64611
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://www.orafaq.com/forum/t/84315/102589/, scroll down to "On indexes"


[Updated on: Wed, 10 September 2008 10:40]

Report message to a moderator

Re: use of index in performance tuning [message #347084 is a reply to message #347075] Wed, 10 September 2008 10:49 Go to previous message
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Depends on whether Oracle decides that either query merits the index. Do an explain plan (implying tables are analyzed with fresh stats).

Even in the case of c1,c2,c3 criteria specified - if number of rows expected back in a large % of the table, then perhaps neither use the index.

The literal answer of "can it be used" - absolutely. (where only C1 is specified). I dont know what functionality may have come into play (if any) in 9i/10g not present in 8i in these regards,
but c1 is the top level of the compound index and if selective enough should merit usage.

Other option for Oracle would be to due a fast full scan on the index if large row counts are expected back (if available in 8i and meets criteria), otherwise a table scan would be employed.

Lets toss out an example - if c1,c2,c3 were representing state,city,zip code in the millions+ table, then state alone might be too unselective.

I think in 9i the skip-scan access method became available where concatonated index can be used even if single field that is not the root field of the index is specified (c2, c3).

Interesting to look into depending on selectivity and ultimately value of the concatonated index is to make single indexes and take advantages of index join efficiency.

I say try it out and see if Oracle takes. If the index is not used, to distinguish if this is a 8i limitation or if oracle just does not choose it because it is more costly, you could
alter a session parameter to tell Oracle that indexes are no overhead at all -

(Tell Oracle that when calculating plan cost, discount the cost of index usage to 1% of default cost).

Further you could entice with
ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 50; (Tell Oracle as well that it can expect half the index values to be cached in memory).

Plenty of good links for reference material here, search from the home page.

Best Regards,
Previous Topic: how to unpin a table from cache
Next Topic: Performance impact Kernel Parameters
Goto Forum:

Current Time: Sun Mar 26 23:53:34 CDT 2017

Total time taken to generate the page: 0.11095 seconds