| Compund index vs simple index [message #187522] |
Mon, 14 August 2006 05:56  |
Nau
Messages: 24
Registered: October 2004
|
Junior Member |
|
|
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
|
|
|
|
| Re: Compund index vs simple index [message #187598 is a reply to message #187522] |
Mon, 14 August 2006 11:10  |
SQLAREA
Messages: 21
Registered: January 2006
Location: Belgium
|
Junior Member |
|
|
Hi,
I think that index type 2 (simples one) takes more space than index type 1, isn't it???
A B-tree index is an additional structure, segment in the database. The leaf blocks contain the indexed value(s) and a rowid - a pointer to the physical address of the table rows.
The size of the B-tree depends on the number of indexed table rows, the byte value of the indexed value(s) and some additional overhead like itl slots, pct free during index creation, leaf block splits (90/10 and 50/50) which occur after some table DML. (you can query v$sysstat for the number of leaf block splits)
Back to you question : a composite index will require less disk space than 3 separated B-tree structures.
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 = ' ',
The goal of composite indexes is indeed to have in 1 single B-tree more than 1 "predicate column", as such the number of table access by index rowid will be lesser compared with the 3 separated structues.
but in any other sql is more effective index type 2 , isn't it??? is that true??,
The column order is very important if you create a composite index. Starting from release 9.2 (I think) Oracle has introduced the index skip scan which can influence your choice regarding the column order. Whether you should go for 1 composite index whether for 3 separated indexes depends on your queries. You should define the most frequent queries accessing your tables and consider the predicate (where clause). With appropriate sql tuning and appropriate stats you can decide which solution best/most fit your needs. You can have a composite index on (col1,col2,col3) and another one on col3 or on col2. The more indexes you have the more disk space you need, the higher the performance cost for table DML
any other difference?
Check out the concepts manual, take a look as well to index organized tables (IOT)
Here is a excellent white paper "Understanding Indexes" from Mr Tim Gorman http://www.evdbt.com/2004_paper_549.doc
And here is my own white paper regarding space requirements for B-tree indexes. http://www.orafaq.com/papers/btree_sizing.pdf
Regards
Guy
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
