Home » RDBMS Server » Performance Tuning » Compund index vs simple index
Compund index vs simple index [message #187522] Mon, 14 August 2006 05:56 Go to next message
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 Go to previous message
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
Previous Topic: Increasing the SGA size
Next Topic: How to tune a select query which gives a result of 120 million rows?
Goto Forum:
  


Current Time: Thu Dec 08 16:37:23 CST 2016

Total time taken to generate the page: 0.24512 seconds