-
query on indexes
Hi Gurus
I have some questions
If Field can have 50 to 100 values and application uses that column in search condition
is there need to have index on that column.
if there is an composite index on two fields in order f1,f2.
if search is made only on f2 then will created index work.
or i have create additional index on f2
rgds.
Avi
-
If you are using 9i then the index will be 'skip scanned' which will allow the index to be used when the primary column in the composite index is not used in the query, however in previous releases you would need to create an additional index.
Regards
Jim
Oracle Certified Professional
"Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"
Click HERE to vist my website!
-
thanks
pls. also suggest me for the first question
-
Re: query on indexes
Originally posted by avi kumar
If Field can have 50 to 100 values and application uses that column in search condition
is there need to have index on that column.
Probably - some cases when it would not be used (e.g. a very small table). Run ANALYZE TABLE xxx COMPUTE STATISTICS; afterwards.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Re: query on indexes
Originally posted by avi kumar
If Field can have 50 to 100 values and application uses that column in search condition
is there need to have index on that column.
The row selectivity is less important than the block selectivity -- what this means is that if you have 50 rows per block, say, it is possible that with 50 distinct values you could have the following situations ...
i) Each block contains one row of each value of the column (v. poor block selectivity). an index will be very counter-productive here.
ii) Each block contains rows that all have the same value for that column. (excellent block selectivity), an index will work very well here.
iii) somewhere in between.
The block selectivity is measurable, and if you analyze an index a column of user_indexes called CLUSTERING_FACTOR will be populated. if the value of this is close to the number of rows in the table then you have case (ii)above -- if it's close to the number of blocks then you have case (i).
This isn't the whole story, but it's enough to dispael the problems with using row selectivity as a measure of "indexability"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|