Forum Stats

  • 3,782,592 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

Index question

donovan7800
donovan7800 Member Posts: 63
edited Nov 17, 2015 4:01PM in SQL & PL/SQL

Small question,

If I know I will have queries like:

WHERE colA = 'XXX'
AND colB = 'YYY'
AND colC = 'ZZZ'

WHERE colA = 'XXX'


WHERE colB = 'YYY'


WHERE colC = 'ZZZ'


but I know I will NOT have any partial usage of columns like:

WHERE colA = 'XXX'
AND colB = 'YYY'


Then I only need to create a single index on (colA, colB, colC) right? 


And if I did have a 2-column WHERE conditions using the above example, then I would want to make additional indexes for (colA, colB)  (colB, colC) (colA, colC) right?


Tagged:
Frank Kulash

Answers

  • Himanshu Kandpal
    Himanshu Kandpal Member Posts: 1,971 Silver Badge
    edited Nov 17, 2015 12:35PM

    Hi,

    What is you are trying to get after creating an index on ColA,ColB and ColC.

    Is it performance improvements in your select criteria then it depends upon lot of things like the number of rows being returned when you run WHERE colA = 'XXX'  or are there any others columns being selected or any other columns in the where clause, is this table going to have only updates or inserts.

    One thing you have to keep in mind that in some circumtances a Full table read is better than a index read.

    thanks

  • John Spencer
    John Spencer Member Posts: 8,567 Bronze Crown
    edited Nov 17, 2015 12:39PM

    An index on cola, colb, colc would cover the first two queries.  You might get an index skip scan for predicates on colb or colc alone, but it would depend on a number of factors, including the the size of the table and the distribution of the column values.

    I might be tempted to create additional indexes on colb alone and colc alone to cover the other singleton predicates.  That assumes that both are reasonable selective compared to the size of the table,

    John

    Frank Kulash
  • donovan7800
    donovan7800 Member Posts: 63
    edited Nov 17, 2015 2:59PM

    Yes the reason is for performance, one of these tables is 25M rows.

    colA - low selectability, maybe only several distinct values

    colB - high selectability, almost distinct, 1 colB will usually return 1-5 records mostly.

    colC - maybe ~50 distinct values

    Sounds like the best approach would be to try these indexes out and look and the xplan on DEV thx.

  • John Spencer
    John Spencer Member Posts: 8,567 Bronze Crown
    edited Nov 17, 2015 3:35PM
    donovan7800 wrote:
    
    Yes the reason is for performance, one of these tables is 25M rows.
    colA - low selectability, maybe only several distinct values
    colB - high selectability, almost distinct, 1 colB will usually return 1-5 records mostly.
    colC - maybe ~50 distinct values
    
    Sounds like the best approach would be to try these indexes out and look and the xplan on DEV thx.
    

    If cola has few distinct values and a relatively even distribution among those values, then I would consider either colb, colc or colc,colb as a compound index, depending on the relative frequency of querying on either alone and leave cola un-indexed since it sounds like a query on cola alone would return a significant fraction of the table so a full table scan would likely be more appropriate anyway.

    Having said that, you are correct in that the best approach would be to test it.

    John

  • Unknown
    edited Nov 17, 2015 4:01PM
    Then I only need to create a single index on (colA, colB, colC) right? 

    No - you do not NEED to create ANY indexes unless a column is being used to enforce a constraint.

    Other than constrain support an index is a 'solution' to a 'problem'. Until you identify an actual problem you do NOT need an index.

    Think about it - why would you need ANY indexes on a table with only 20 rows when all 20 rows are in ONE block? Does that makes any sense?

    See the point I'm making? Without knowing the FULL details about your data and how it is used it is NOT possible to determine whether ANY indexes might be useful as part of some 'solution' to your 'unknown' problem.

    I've have seen far too many developers adding indexes to tables just because they think it MIGHT help performance.

This discussion has been closed.