| use of index in performance tuning [message #347075] |
Wed, 10 September 2008 10:21  |
agrawal.prachi@gmail.com
Messages: 2
Registered: May 2007
|
Junior Member |
|
|
Hi,
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 #347084 is a reply to message #347075] |
Wed, 10 September 2008 10:49  |
harrysmall3
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 -
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1;
(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,
Harry
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
