Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    20

    Unanswered: Compund index vs simple index

    I want to know the differece between a compund index and a simple one.

    For example I have a compund index with three fileds: field1, field2, field3

    index type 1:
    create index index1 on table1 (field1, field2, field3);

    What happen if I change this compund index for a simple one?, something like this:

    index type 2:
    create index index1 on table1 (field1);
    create index index2 on table1 (field2);
    create index index3 on table1 (field3);

    I think that index type 2 (simples one) takes more space than index type 1, isn't it???
    I think index type 1 is more effective in a sql with all the indexed fields in the where, something like that: select * from table1 where field1 = ' ' and field2 = ' ' and field3 = ' ', but in any other sql is more effective index type 2 , isn't it???
    is that true??, any other difference?

    Any advice or design criteria about indexes (compund or simple) will be greatly appreciatted.

    Thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Efficient is relativ

    The advantage of the (as you call it) simple index definitions is, that a retrieval with WHERE field2 = 'bla' will use that index. Whereas the compound index would not be used. AFAIK Oracle will never use more than one index, but can use partial index. So for a criteria like WHERE field1 = 'bla' and field2 = 'bla2' the compound index will be used (even if you do not use field3)

    Having said this: which index is better depends on the queries you make. Take your application, check the most frequent queries and run an EXPLAIN PlAN on them to check which indexes are used (or not). The create the indexes that are needed for your queries.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Not strictly true as index skip scans can use compound indexes where the leading columns arent specified. However performance is never as good as an index range/unique scan.

    Also not only take into account which columns are specified by your application most commonly but also take into account which columns are the most selective. So where both columns a & b are specified but the column which is most selective first as you will traverse fewer index blocks. You can also improve index performance using the compress option where you do get repeated values.

    You can use indexes to elimanate sorts where all the columns are specified in the order by/group by and all are not null.

    Alan

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Haven't heard about index skip scan so far, so I had to read up in the performance guide. It does sound sound interesting.
    But none of my composite keys would have qualified for that execution plan as far as I can tell. Most of the time the first column doesn't have only a few distinct values. But I'll take that into my check list for the next time.

    Thanks for the hint.

    but also take into account which columns are the most selective.
    Very true!

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You have to be careful if you do see it as sometimes it gives worse performance than a FTS.

    Alan

  6. #6
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Quote Originally Posted by AlanP
    ...
    You can use indexes to elimanate sorts where all the columns are specified in the order by/group by and all are not null.
    ...
    AlanP, can you explain this further ?

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Because traversal of an index returns data values in order it can elimanate the sort by just by using the index. The restrictions are that all the columns in the order by/group by must be in the composite index and in the same order. Also I believe one of the columns must be not null but I may be wrong on that.

    Alan

Posting Permissions

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