Need is necessary for any change. I hope all agree to this. So why many DBA’s (not all) rebuilds indexes on periodical basis without knowing the impact of it?
Let’s revisit the facts stated by many Oracle experts:
- B Tree indexes can never be unbalanced
- The distance of any leaf block (which contains the index value) from the root block is always same.
- B Tree performance is good for both small and large tables and does not degrade with the growth of table
When will rebuild help?
• When the data in index is sparse (lots of holes in index, due to deletes or updates) and your query is usually range based.
If your SQL’s use “=” predicate, rebuilding of index may not help you at all unless the DML’s (updates/deletes) on the table are cause of increasing the height of index. Due to heavy updates/deletes on a table, the space in the index can be left unused (unless it is reused) due to which block splits and if the splitting goes beyond the level of accomodation, the height of index increases by 1.
In simpler terms, unless you reduce the height (BLEVEL) of index, the time taken to reach the leaf block from root block will not change.
As explained above, the height of index can be decreased by rebuild of an index(only if height was increased to updates/deletes).
Only in case of range scan, rebuild (consider coalesce option also) may help.
Select * from emp where empno between 1000 and 2000;
In above case there could be lot of deleted/updated data which will also be read while reading between 1000 to 2000. Rebuilding will reclaim any unused space and hence the select could be faster.
• Index fast full scan/ Index full scan. (rare case)
If your SQL’s are designed in a way that mostly data is selected from index (it doesn’t goes to table). In this case the whole index is read for data not for redirecting to table.
Ex:
Select count(empno) from emp; -- you have an index in empno
Select empno from emp -- you have an index in empno
Both the above SELECTs will fetch the data from Index only. Since the whole index is getting read, the space used by deleted rows is also read at the time of SELECT operation
10 comments:
I am not a DBA but I have stupid question. If the BTree index is affected by things like updates and deletes, I think these can leave holes within the index. Is it also necessary to re-analyze after loading many records and why? I mean if the table grows why can't the index grow too?
Beautifully explianed use of How B tree index works . Will be more helpful if can further add how to quick check for rebuild require & possible ways in 9i & 10g
Thanks Harvinder, for your comments, my new post How to find indexes which are candidate for rebuild? is focssing that.
Kindly give your feedback on that.
Hello Tapiwanashe,
Firstly, Thanks for your comment.
Yes, B tree are affected by updates/deletes, and they leaves hole but these holes can be used for future insert.
Let me take an example.
Assume a leaf index block holds index entries for 20 rows and you delete 10 rows.
The hole for 10 rows can be used for newer insert when you insert similar kind of data.
When I say similar, it means index entries which can be referred by the branch block (of that leaf block) can be inserted.
But if all 20 rows are deleted, the full block goes free and this block can go into freelist and can be used anywhere (irrespective of being similar or not).
Update works similar to delete in case of index. Update means delete + insert operation for index. If you update a value 10 to 20, Oracle will first delete 10 and insert 20 (may or may be not within a single block) because reference to 20 may be done by a different branch block (or also values close to 20 are in different leaf block of the same branch block).
Analyze helps in getting the statistics which helps in giving more information to optimizer while selecting the data.
Answer to your second part of question (if table grows, why index can’t grow) is:
Both table, and index grows but their growth pattern is different.
A table block goes to freelist, if post update/delete the block usage goes below the PCTUSED level.
But in case of index, you cannot specify PCTUSED in CREATE INDEX, so block returns to freelist only when it is empty.
Let me know if I’m not able to explain clearly.
Sachin
Hi Sachin,
Regarding following comment
-------------
Yes, B tree are affected by updates/deletes, and they leaves hole but these holes can be used for future insert.
Let me take an example.Assume a leaf index block holds index entries for 20 rows and you delete 10 rows.
The hole for 10 rows can be used for newer insert when you insert similar kind of data.
When I say similar, it means index entries which can be referred by the branch block (of that leaf block) can be inserted.
------------
The index space will not get reused when similar data is inserted during the same transaction. i.e. if we delete 10 rows and insert the same rows in the same transaction then the index entries will not get reused.
But if the rows are added as a seperate transaction then the index entries will get reused.
Please refer to the Metalink Thread 619078.992 : 'Question about how index space is reused'.
Regards,
Vaibhav
Hi Vaibhav,
I searched the document you refered but with no success
I appreciate if you can give me the link.
BTW - check this testcase:
SQL> create table test as select * from dba_objects;
Table created.
SQL> create index test_in on test(object_id);
Index created.
SQL> analyze index test_in validate structure;
Index analyzed.
SQL> select index_name,leaf_blocks from user_indexes where index_name='TEST_IN';
INDEX_NAME LEAF_BLOCKS
------------------------------ -----------
TEST_IN 107
SQL> delete from test;
48533 rows deleted.
SQL> insert into test select * from dba_objects; -- DELETE AND INSERT within a single transaction
48534 rows created.
SQL> commit;
Commit complete.
SQL> analyze index test_in validate structure;
Index analyzed.
SQL> select index_name,leaf_blocks from user_indexes where index_name='TEST_IN';
INDEX_NAME LEAF_BLOCKS
------------------------------ -----------
TEST_IN 107
Hi Sachin,
On Metalink in 'Advanced Search' check for document id 619078.992.
In your testcase, you are not computing the statistics on the index.
Use : analyze index index_name compute statistics. ( and not VALIDATE STRUCTURE )
Testcase 1 -- delete and insert are performed in a single transaction
SQL> create table mytb as select * from all_objects;
Table created.
SQL> create index indx_mytb on mytb(object_name);
Index created.
SQL> select leaf_blocks from user_indexes where index_name='INDX_MYTB';
LEAF_BLOCKS
-----------
41
SQL> begin
2 delete from mytb;
3 insert into mytb select * from all_objects;
4 end;
5 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze index indx_mytb validate structure;
Index analyzed.
SQL> select leaf_blocks from user_indexes where index_name='INDX_MYTB';
LEAF_BLOCKS
-----------
41
SQL> analyze index indx_mytb compute statistics;
Index analyzed.
SQL> select leaf_blocks from user_indexes where index_name='INDX_MYTB';
LEAF_BLOCKS
-----------
84
=============================
Testcase 2 -- delete and insert are performed in a differenr transactions
SQL> drop table mytb;
Table dropped.
SQL> create table mytb as select * from all_objects;
Table created.
SQL> create index indx_mytb on mytb(object_name);
Index created.
SQL> select leaf_blocks from user_indexes where index_name='INDX_MYTB';
LEAF_BLOCKS
-----------
41
SQL> begin
2 delete from mytb;
3 commit;
4 insert into mytb select * from all_objects;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> select leaf_blocks from user_indexes where index_name='INDX_MYTB';
LEAF_BLOCKS
-----------
41
SQL> analyze index indx_mytb compute statistics;
Index analyzed.
SQL> select leaf_blocks from user_indexes where index_name='INDX_MYTB';
LEAF_BLOCKS
-----------
41
Regards,
Vaibhav
Thanks Vaibhav.
Point seems correct.
To put a logical reaosn for viewers, " Oracle doesnot resuse the free space emptied by delete/update within a transaction"
assuming that user can anytime rollback. And if rollback happens, it will need that space, so assuming a rollback, emptied blocks doesnot go to freelist within a transaction. But definately other transactions can use it.
But the point remains that the space can be reused (outside the trsansaction which is responsible for delete).
Thanks,
Sachin
Sachin
Post a Comment