| 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 management
Howard J. Rogers wrote:
> Oh for gawd's sake. Please find out how indexes work.
>
> The clue is in the words "empty blocks". What do you think PCTUSED of
> zero means for an index block, exactly?
>
Upon reflection, that post was made in the heat of the moment, and was unfair and unpleasant to Brian. So my apologies, and please let me explain properly why what you posted was wrong.
Instead of ASCII art, let's do an actual test on a real database.
Here is the baseline test.
SQL> create sequence scott.auto
2 start with 1
3 increment by 1
4 maxvalue 500000
5 order;
Sequence created.
SQL>
SQL> create table invoices (
2 invoice# number,
3 invdesc varchar2(30));
Table created.
SQL>
SQL> create index invidx on invoices(invoice#);
Index created.
SQL>
SQL> create or replace trigger INVOICE_AUTO
2 before insert on invoices
3 for each row
4 begin
5 select auto.nextval into
6 :new.invoice# from dual;
7 end;
8 /
Trigger created.
SQL> insert into invoices (invdesc) select object_name from dba_objects; 6225 rows created.
SQL> insert into invoices (invdesc) select invdesc from invoices; 6225 rows created.
SQL> insert into invoices (invdesc) select invdesc from invoices; 12450 rows created.
SQL> insert into invoices (invdesc) select invdesc from invoices; 24900 rows created.
SQL> insert into invoices (invdesc) select invdesc from invoices; 49800 rows created.
SQL> insert into invoices (invdesc) select invdesc from invoices; 99600 rows created.
SQL> commit;
Commit complete.
SQL> select max(invoice#) from invoices;
MAX(INVOICE#)
199200
SQL> analyze table invoices compute statistics; Table analyzed.
SQL> select leaf_blocks from dba_indexes where index_name='INVIDX';
LEAF_BLOCKS
398
Let's just stop there. Our base index is 398 leaf nodes big at this point. How much does this index grow by if I insert another 10,000 rows (which, because of the monotonically incrementing sequence number, will be placed at what we generally think of as the 'right-hand side' of the index)?
SQL> insert into invoices (invdesc) select invdesc from invoices where rownum < 10001;
10000 rows created.
SQL> select max(invoice#) from invoices;
MAX(INVOICE#)
209200
[That's just to prove the sequence is still ascending, and therefore the new rows really must have been housed in the 'right' of the index]
SQL> analyze table invoices compute statistics; Table analyzed.
SQL> select leaf_blocks from dba_indexes where index_name='INVIDX';
LEAF_BLOCKS
418
So an insert of 10,000 new rows can be expected to make our index grow by around 20 new index leaf blocks (418 blocks now, 398 blocks before).
What happens now if I first *delete* a batch of invoices (in ASCII art, that means clearing out the left-hand side of the index) and then inserting 10,000 new ones. According to the ASCII art school of index management, that should mean my index acquiring about 20 new leaf nodes on the right, ignoring the existence of all the empty blocks on the left. But what actually happens on a real database?
SQL> delete from invoices where invoice# < 20000; 19999 rows deleted.
SQL> commit;
Commit complete.
SQL> insert into invoices (invdesc) select invdesc from invoices where rownum < 10001;
10000 rows created.
SQL> commit;
Commit complete.
SQL> select max(invoice#) from invoices;
MAX(INVOICE#)
219200
[Again, just to prove that the invoice number field has been incrementing all the time]
SQL> analyze table invoices compute statistics; Table analyzed.
SQL> select leaf_blocks from dba_indexes where index_name='INVIDX';
LEAF_BLOCKS
422
So *this* insert of 10,000 records has only made me acquire 4 new leaf blocks, not 20 as was the case before. Yet the same quantity of new records has been inserted as before (10,000), and the sequence number was incrementing all the while. So one can only conclude that most of the new inserts must have been housed in blocks which the index *already had acquired* -that is, in the empty blocks vacated by the bulk deletion of 20,000 rows. Even though they are supposed to be "left-hand" leaf nodes and thus unusable.
Well, those vacated blocks might have been on the 'left' of the picture when drawing ASCII art, but they clearly aren't in real life, and are re-usable by new inserts even if your sequence is monotonically incrementing.
Good question why I got 4 new blocks at all, I suppose. Some of that is down to the fact that I arbitrarily deleted 20,000 rows. It is quite possible that this would have left a few tens of rows in one of the 'left-hand' blocks, thus rendering it NOT re-usable by subsequent inserts. You've also got block splitting going on as the nodes already part of the index are filled (the 90:10 rule).
Incidentally, and just for fun, what happens to my "bloated", and "right-hand skewed" index if I rebuild it (immediately after the test above finished)?
SQL> alter index invidx rebuild;
Index altered.
SQL> analyze table invoices compute statistics; Table analyzed.
SQL> select leaf_blocks from dba_indexes where index_name='INVIDX';
LEAF_BLOCKS
444
So if I DON'T rebuild, the index has 422 leaf nodes. And if I DO rebuild this wicked, skewed bloated monstrosity of an index, it has 444 leaf nodes.
And no, I'm not making that up. The index is *bigger* after the rebuild than before. Surprised? I was, actually. But there you go. That might give the "I have some spare CPU cycles so why not use them because it can't do any harm" school of index management pause for thought.
Hopefully one day before I'm dribbling too much to care, people will finally get the message that Oracle's indexes are efficient structures, efficiently managed, that are quite capable of looking after themselves if given half a chance. And that the need to rebuild is vanishingly small for more than, oh, say, about 95% of the indexes on the face of the planet.
The other message I hope people in general get the gist of is this: ASCII art is no substitute for testing it out in reality. More generally, what you think *ought* to reasonably happen quite often doesn't.
Regards
HJR
Received on Tue May 11 2004 - 16:31:16 CDT
 |
 |