Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Index rebuilding

RE: Index rebuilding

From: Tony Jambu <tjambu_freelists_at_yahoo.com.au>
Date: Mon, 15 Nov 2004 02:33:25 +1100
Message-Id: <6.2.0.14.2.20041115005851.03718190@pop.mail.yahoo.com.au>


Hi
So called 'Rules' are there to be tested in the environment and version your are in. It does not mean it will _always_ be true.

Two examples in Richard's paper (fantastic work) that does not match my observation are:

  1. What happens when the left most leaf split, when a new row with new max value in inserted?

Will the two sibling be 50-50 filled like all other nodes and leafs? According to Richards paper it is 90-10 or 99-1 split. My observation is that it (#rows-1) to 1. Yes only one row in the right most block.

Try this

drop table t1;
create table t1 (col1 number);
create UNIQUE index t1_IDX on t1(col1) ;

begin
for i in 1..578 loop

    insert into t1 values(i);
end loop;
end;

index dump the object and you will should see this

Now insert a single record
Insert into t1 values(579);

and index dump it. What do you get?

In fact what Richard is saying is, it is more like 99 to 1. What my observation is, it is (total rows before split -1) to 1.

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;
/

On analysing the index and selecting from index_stats , I get

SQL> select lf_blks, del_lf_rows from index_stats;

   LF_BLKS DEL_LF_ROWS
---------- -----------

       939      499001
------^^^^^^

which shows that there is a lot of unused deleted blocks.

Is he wrong or am I wrong? All I can say right now is, that is what Richard observed and this is what I observe. (I am on 9.2.0.4 on Windows XP).

However! if you execute gather stats using DBMS_STATS and and select from user_indexes, you'll see

INDEX_NAME BLEVEL LEAF   DISTINCT  CLUSTERING NUM   AVG LEAF      AVG DATA
                  BLOCKS KEYS      FACTOR     ROWS  BLKS PER KEY  BLKS PER KEY
---------- ------ ------ --------- ---------- ----- ------------- ------------
    T2_IDX      2      3      1000          5  1000             1             1

----------------------^^^^^

Ah huh, so the leaf blocks are being reused after all. NOTE: in this case, we are testing a Right Hand growth index ie index column is based on a ascending sequence #

So it really depends on the test case and how you validate your hypothesis Check it out yourself.

Questions



Q1. So can someone explain the difference in results when using Index_stats and User_indexes?

Q2. From index_stats
select lf_rows, del_lf_rows, lf_blks from index_stats;

   LF_ROWS DEL_LF_ROWS LF_BLKS
---------- ----------- ----------

    500001 499001 939

Does that mean, _actual_ rows left = LF_ROWS - DEL_LF_ROWS ?

ta
tony

At 05:40 AM 13/11/2004, Goulet, Dick wrote:

>Looked at Richard Foote's paper.  Don't know about that.  I did prove to
>OTS several years ago that a block could get "lost" in an index due to
>deletion/updates that left it empty.  I believe that got finally fixed
>in Oracle 8i.  I've still seen cases of index's becoming unbalanced, I
>know the docs day it's impossible, but it does happen without the index
>height increasing. And I still believe that index deletes don't get
>flushed so efficiently, as Richard suggests.  If that was the case then
>I can't explain why an index rebuild can cause an index to shrink by 30%
>or more.  And recent experience still shows that a rebuild can cause
>significant performance improvement.  And Oracle has provided the
>capability to rebuild indexes which is not trivial.  Therefore, NEVER
>use the word "never" unless your absolutely certain that under all
>circumstances it will be absolutely true.  And in the current context,
>that is the truth, that is, never can never be an absolute.
>
>BTW: Since we've a few "myth busters" in the group.  I appreciate the
>effort these people put into "myth busting", even if they are later
>proven to have erred.  At a very minimum they start discussion and
>re-examination of commonly held beliefs that can have changed or lost
>significance over the years(like it's best to have all of a tables data
>in the first extent).  Such discussion, although sometimes the start of
>"Holy Wars", is healthy (not the Holy War though) and a necessary part
>of all of us growing.  That being said, let it be noted that I agree to
>disagree, in part, with Mr Foote.
>
>
>Dick Goulet
>Senior Oracle DBA
>Oracle Certified 8i DBA


--
http://www.freelists.org/webpage/oracle-l
Received on Sun Nov 14 2004 - 09:33:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US