Re: leaf node splits
- From: Dom Brooks <dombrooks@xxxxxxxxxxx>
- To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 27 Jan 2012 16:27:23 +0000
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--
//www.freelists.org/webpage/oracle-l
Other related posts:
