Re: leaf node splits

From: Andy Klock <andy_at_oracledepot.com>
Date: Fri, 27 Jan 2012 11:33:40 -0500
Message-ID: <CADo_RaOFDXFqfVk462sbuGp3oqGT-qvjBXXJbCEABwh2kznrjQ_at_mail.gmail.com>

I would think that:
>Does the insert include a "sequence.nextval" and if so is that column
indexed ?
Yes. Another global unique index on the primary key populated by sequence number.

This would explain this phenomenon. You have have a right handed index that has no where to go but to the right with every subsequent run. I thought I covered that, but Jonathan is extremely thorough.

Andy

On Fri, Jan 27, 2012 at 11:27 AM, Dom Brooks <dombrooks_at_hotmail.com> wrote:

> Perhaps what I should have asked is as index splits are recursive
> transactions
> unaffected by my rollback, why do I consistently see the same number of
> 90-10 splits in these subsequent transactions?
>
>
> >How many list subpartitions in each partition ?
> 3 subpartitions in each of 64 partitions
>
> >Are you using ASSM or freelist management on the tables or indexes ?
> ASSM.
>
> >Do any of the indexes have very repetitious key values ?
> Yes.
>
> One of these global indexes is a unique composite index with 6 columns.
> 17 million rows in table.
> Column 1 - 3786 distinct values
> Column 2 - 1000862 distinct values
> Column 3 - 2 distinct values
> Column 4 - the partition date column - 64 distinct values
> Column 5 - 2 distinct values
> Column 6 - 2 distinct values
>
> (From an index maintenance perspective, the date column would probably be
> better at position 1 but
> some queries might suffer as a result - something to investigate)
>
> Two other non-unique local indexes - one on column 2 above; one composite
> on the subpartition key (140 distinct values),
> column 1, column 2.
>
>
> >Does the insert include a "sequence.nextval" and if so is that column
> indexed ?
> Yes. Another global unique index on the primary key populated by sequence
> number.
>
> >Is the volume of data (including indexes) large relative to the size of
> the cache ?
> Table - 3G
> Indexes - 3G
> Buffer Cache - 16G
>
> Cheers,
> Dominic--
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 27 2012 - 10:33:40 CST