DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: query on indexes

  1. #1
    Join Date
    Jan 2003
    Location
    india
    Posts
    10

    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

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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!

  3. #3
    Join Date
    Jan 2003
    Location
    india
    Posts
    10
    thanks

    pls. also suggest me for the first question

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    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

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    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"
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width