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.
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.
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.
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.
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.