| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: index rebuilding...
"Don Burleson" <don_at_burleson.cc> wrote in message
news:998d28f7.0301310721.3c724ec_at_posting.google.com...
> Hi,
>
> Do a Google search on "oracle index rebuild".
>
> The second hit has a good article on the topic.
What sort of advice is that? Do you really think Google will always return the same results in the same order, globally and forever? Why not just provide the link directly (which, for anyone interested, I imagine was intended to be Don's own article at http://www.dba-oracle.com/art_index1.htm
As it is, it's one of the weakest, error-ridden pieces of writing I've seen in a long time, and certainly is NOT a "good article on the topic". Let's start with the first line, shall we:
"Indexes require rebuilding when deleted leaf nodes appear". I don't even know what a deleted leaf node is. Do you mean 'deleted leaf rows'? In which case, you're wrong, because deleted leaf rows can disappear just as easily as appear. Oracle re-uses deleted leaf row space, all on its own.
Next. " Oracle index nodes are not physically deleted when table rows are deleted". I would have more confidence in the advice you offer if you could get the distinction between a node (ie, a block)and a row right. You go on "deleted leaf nodes can be easily identified " and then show a report one of whose headings is, in plain black and white, 'Deleted Leaf Rows'.
As for this: "Note that Oracle indexes will "spawn" to a fourth level only in areas of the index where a massive insert has occurred, such that 99% of the index has three levels, but the index is reported as having four levels. " ... well, all I can say is: this is complete baloney. Oracle's index structures are B*Trees, meaning that they are 'balanced' trees. Meaning that if it takes you three steps to get to the leaf nodes on one side of the index, it is utterly impossible for it to take 4 on the other side. Oracle guarantees that the height of the index is always balanced across the entire index. the situation you describe does not, cannot and will not happen in Oracle.
"Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes". Not true. You may well have more than 20% of your leaf ROWS shown as being deleted. But they'll be re-used if you wait long enough, because Oracle re-uses the space:
SQL> create index emp_ename_idx on emp(ename); Index created.
SQL> analyze index emp_ename_idx validate structure; Index analyzed.
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
19 0
[ie, no deleted leaf ROWS (and please note Oracle's own terminology)]
SQL> update emp set ename='ZEBEDEE' where ename='ALLEN';
1 row updated.
SQL> commit;
Commit complete.
SQL> analyze index emp_ename_idx validate structure; Index analyzed.
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
20 1
[Oh Lord, a deleted leaf row has appeared!!! Better get ready to rebuild, I suppose. But hang on...]
SQL> insert into emp (empno, ename) values (9993, 'ABBOT');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze index emp_ename_idx validate structure; Index analyzed.
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
20 0
[Phew! A fresh insert has gotten rid of the deleted leaf row, meaning that the space has been re-used. And note the original entry was 'ALLEN' and the new one was 'ABBOT', so it's not a question of needing a new entry identical to the old before the space is reused. In point of fact, an insert that makes use of a deleted leaf row space clears out ALL deleted leaf rows in that block, so if I'd updated 'ALSOP', 'ALBERTT', 'ATHGUARD' and 'ATHLON', the insertion of 'ABBOT' would have removed all four deleted leaf rows. As proof:
insert various new rows into EMP;
SQL> select ename from emp
2 where ename like 'A%';
ENAME
SQL> create index emp_ename_idx on emp(ename); Index created.
SQL> analyze index emp_ename_idx validate structure; Index analyzed.
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
24 0
Now time for the updates.
SQL> update emp set ename='ALSOP' where ename='ALSOPP';
1 row updated.
SQL> update emp set ename='ALBERTT' where ename='ALBERT';
1 row updated.
SQL> update emp set ename='ATHGUARD' where ename='ATHERGUARD';
1 row updated.
SQL> update emp set ename='ATHLON' where ename='ATHLIN';
1 row updated.
SQL> commit;
SQL> analyze index emp_ename_idx validate structure; Index analyzed.
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
28 4
SQL> insert into EMP (empno, ename)
2 values (4453, 'ABBOT');
1 row created.
SQL> commit;
Commit complete.
SQL> analyze index emp_ename_idx validate structure; Index analyzed.
SQL> select lf_rows, del_lf_rows from index_stats;
LF_ROWS DEL_LF_ROWS
---------- -----------
25 0
So one insert cleans out 4 deleted entries, making their space available for re-use. And not a rebuild in sight.
If this were an index on a sequence number, then yes -the nature of sequences is such that you'll never be assigned a new number which could make use of the previously-deleted rows, so a rebuild MIGHT be warranted. (But I'd try a coalesce first, if I had 8i -and if your article was only ever written for Oracle 7, then don't you think it's a bit 'off' offering it as 'good advice' on index management 3 major releases later?)].
Next... "As you may know, you can easily rebuild an Oracle index with the command: ALTER INDEX index_name REBUILD". You're kidding, right?? "easily rebuild"???? Never mind the vast amounts of I/O, the exclusive table locking. Oh, I see... your description of what a rebuild does talks about it 'walking the existing index' and 'populating temporary segments', but the minor matter of locking out all users from performing DML on the indexed key doesn't warrant a mention. Uh huh.
I won't even get into whether the statement that 'bitmapped indexes are faster when the index key has less than 25 distinct values' has any validity at all. Faster at what, and than what? And why 25? You mean a bitmap index with 26 distinct values is going to be slower? Unacceptably slower? Imperceptibly slower? Than knitting? Than using a b*tree index? Than doing a full table scan? The number of distinct values for which a bitmap index might be beneficial is *relative* to the number of records in the table, and there is no specific cut-off point at which they begin to be useful or cease to be useful. The number 25 is a figment of your imagination.
And I notice you cheerfully recommend converting a b*tree index into a bitmap one if the magic number 25 is true, without once mentioning the horrendous side effects of doing so in a DML-intensive environment.
I dunno. Maybe I'm missing something. But this article is so trite, incorrect, full of wrong terminology, and downright misleading that it wouldn't be my first recommendation (or indeed any recommendation at all) for anyone wanting to know something meaningful about index rebuilds. Maybe that's why you didn't include the actual article link?
HJR Received on Fri Jan 31 2003 - 11:48:29 CST
 |
 |