Re: Index rebuilding
- From: Tony Jambu <tjambu_freelists@xxxxxxxxxxxx>
- To: richard.foote@xxxxxxxxxxx
- Date: Mon, 15 Nov 2004 23:27:38 +1100
Hi Richard
Thanks for your comments and clarification.
Comments in line with some sections deleted.
At 12:03 AM 16/11/2004, Richard Foote wrote:
>Obviously 99-1 is not a particularly clear term either !!
Understand where you are coming from now. And my observation
matches your hypothesis.
>>
>>
>>
>> 2. Reusable Empty/deleted Blocks
>>
>> Richards test was
>> 1. Insert 10,000 rows
>> 2. Del 1st 9,990 rows
>> 3. Analyse index
>> 4. Insert another 10,000 rows of increasing value
>> 5. Re analyse index.
>>
>> The test I carried out was to insert 500,000 rows but to start deleting
>> individual rows when the number of rows reached 1000. ie a rolling 1000
>> records of about 499,000 inserts and deletes.
>>
>> create table t2 (col1 number);
>> create UNIQUE index t2_IDX on t2(col1) ;
>> alter table t2 add constraint T2_PK PRIMARY KEY (col1);
>>
>> REM 500,000 Rolling Inserts & Dels with rolling 1000 records
>>
>> begin
>> for i in 100000..600000 loop
>> delete t2 where col1 = i-1000 ;
>> insert into t2 values(i);
>> end loop;
>> end;
>> /
>
>You've forgotten one very important thing with this example, the commit !!
Big DUH. What a wally I am. I re-ran the test with the commit and this is
what i got
INDEX_NAME BLEVEL LEAF DISTINCT CLUSTERING NUM AVG LEAF AVG DATA
BLOCKS KEYS FACTOR ROWS BLKS PER KEY BLKS PER KEY
---------- ------ ------ --------- ---------- ----- ------------- ------------
T2_IDX 1 3 1000 3 1000 1 1
LF_ROWS DEL_LF_ROWS LF_BLKS
---------- ----------- ----------
1002 2 4
This result now reflects what you wrote in your paper. Thanks for
clarifying this.
>> Questions
>> ----------
>> Q1. So can someone explain the difference in results when using
>> Index_stats
>> and User_indexes?
>
>Validate Structure is showing more of the "truth" than dbms_stats. The 3 in
>DBA_INDEXES.LEAF_BLOCKS represents the number of leaf blocks that actually
>contain non-deleted index entries, whereas INDEX_STATS shows us the total
>number of index blocks currently in the index structure, including those
>re-placed on the freelist due to "empty" leaf blocks that have had all index
>entries deleted. These "empty" blocks can be reused by Oracle in a different
>location within the index structure with subsequent insertions (remembering
>in your example this wasn't initially possible as all insertions/deletions
>were in the same transaction).
Are these documented or just your observation from the work you have done.
Will keep this in mind for future work.
Thanks again for clearing up the discrepancies.
ta
tony
--
//www.freelists.org/webpage/oracle-l
Other related posts:
