Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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...Received on Fri Feb 17 2006 - 05:06:08 CST
> 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
>