Re: Influence of index structure to insert performance

It is possible for the performance on insertions to be affected by column ordering in indexes, but in many cases the effect is likely to be something that gets lost in the general "noise" level of activity.

To demonstrate the concept:
Imagine you have a high volume insert

    (sydate, sequence_number, random_value)

If your index is in the same column order then as you insert a row into the table, its insertion point in the index will (usually) be in the same index leaf block as last time - with the occasional fill, change of leaf block, and insertion into a branch block. This will maximise the benefit you can get from buffering and pinning.

If your index is created in the column order

    (random_value_col, sequence_number_col, date_col) then each row you insert into a table may have to find a different leaf block from last time. In the best case this will be two or three logical I/Os to get the correct block; in a more typical case you may also have to do a physical read every now and again to load a leaf block into memory.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


<suriawan_at_gmx.de> wrote in message 
news:1140042090.363702.213310_at_g14g2000cwa.googlegroups.com...


> Hello,

>

> I have table A and it has columns: col_1, col_2, col_3, .., col_n. Is

> there any performance different for insert operation if I create an

> unique index on (col_1, col_2, col_3) or a unique index on (col_3,

> col_2, col_1)? Is clustering_factor an issue for the insert?

>

> Thank you.

>

> Regards,

> Suriawan

>