An empty index block or those blocks that contain nothing
but deleted index entries are placed on the index free list and can potentially
be recycled during subsequent index block split operations.
A point that is not well known about such empty index block
is how oracle considers them when calculating index related statistics and
possible implications this may have on the CBO.
Let’s take an example here.
rajesh@ORA11G> set feedback off
rajesh@ORA11G> drop table t purge;
rajesh@ORA11G> create table t
2 as
3 select rownum as id, rpad('*',10,'*') x
4 from all_objects a
5 where rownum <=10000;
rajesh@ORA11G> create index t_idx on t(id);
rajesh@ORA11G> set feedback on
rajesh@ORA11G>
OK, now we have an index with 10000 entries, let’s just check to see
how many leaf blocks we currently have,
rajesh@ORA11G>
rajesh@ORA11G> analyze index t_idx validate structure;
Index analyzed.
rajesh@ORA11G> select lf_rows,lf_blks,del_lf_rows from
index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- -----------
10000 21 0
1 row selected.
rajesh@ORA11G>
So we have 10000 entries and 21 leaf blocks, with no deleted index
entries at this stage.
Let’s delete the vast majority of rows from the Table.
rajesh@ORA11G>
rajesh@ORA11G> delete from t where id <=9990;
9990 rows deleted.
rajesh@ORA11G> commit;
Commit complete.
rajesh@ORA11G>
Let’s start by looking at how ANALYZE INDEX.....VALIDATE STRUCTURE
deals with empty leaf blocks and index entries.
rajesh@ORA11G>
rajesh@ORA11G> analyze index t_idx validate structure;
Index analyzed.
rajesh@ORA11G> select lf_rows,lf_blks,del_lf_rows from
index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS
---------- ---------- -----------
10000 21 9990
1 row selected.
rajesh@ORA11G>
The first thing to note is LF_ROWS statistics still has a value of
10000, it still counts the index entries even if they were deleted. We also
notice that LF_BLKS value is 21 so those leaf blocks are effectively empty and
are still counted.
Let’s see how oracle collect stats using dbms_stats API.
rajesh@ORA11G>
rajesh@ORA11G> begin
2 dbms_stats.gather_table_stats(user,'T',
3 cascade=>true,
4 no_invalidate=>false,
5 method_opt=>'for all columns size 1');
6 end;
7 /
PL/SQL procedure successfully completed.
rajesh@ORA11G> column index_name format a10
rajesh@ORA11G> select index_name,num_rows,leaf_blocks
2 from user_indexes
3 where table_name ='T';
INDEX_NAME NUM_ROWS
LEAF_BLOCKS
---------- ---------- -----------
T_IDX 10 1
1 row selected.
rajesh@ORA11G>
We notice a couple of difference here, the NUM_ROWS value is 10 and
LEAF_BLOCKS is 1, highlighting non deleted entries and only those index leaf
blocks containing non-deleted entries. Although there are 20 other leaf blocks
in the structure, they are not considered and counted when statistics are
collected using dbms_stats API.
If we run the following simple query that effectively selects the
remaining rows from the table. We notice the following in plan.
rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> alter session set statistics_level=all;
Session altered.
rajesh@ORA11G> select * from t where id between 1 and 10000;
ID X
---------- ----------
9991 **********
9992 **********
9993 **********
9994 **********
9995 **********
9996 **********
9997 **********
9998 **********
9999 **********
10000 **********
10 rows selected.
rajesh@ORA11G> select * from table(dbms_xplan.display_cursor(format=>'allstats
last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID 1y7878qmn0nvt,
child number 0
-------------------------------------
select * from t where id between 1 and 10000
Plan hash value: 470836197
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |Buffers |
---------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | | 10 |
25 |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
1 | 10 | 10 |
25 |
|* 2 | INDEX RANGE
SCAN | T_IDX | 1 | 10 |
10 | 23 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 -
access("ID">=1 AND "ID"<=10000)
19 rows selected.
rajesh@ORA11G>
Now, let us see what happens if we use the old Analyze command to
calculate statistics.
rajesh@ORA11G> analyze index t_idx compute statistics;
Index analyzed.
rajesh@ORA11G> select index_name,num_rows,leaf_blocks
2 from user_indexes
3 where table_name ='T';
INDEX_NAME NUM_ROWS
LEAF_BLOCKS
---------- ---------- -----------
T_IDX 10 21
1 row selected.
rajesh@ORA11G>
A big difference, we notice that previously DBMS_STATS don’t include
empty leaf blocks in statistics, we now notice that when using ANALYZE command
does include such empty leaf blocks, the LEAF_BLOCK is now 21 and not 1 as it
was previously.
But that leaf block is one of the key statistics used by the CBO when
calculating the cost of the index related access paths, could this still make a
difference in the execution of this query?
Yes it is.
rajesh@ORA11G>
rajesh@ORA11G> select * from t t1 where id between 1 and
10000;
ID X
---------- ----------
9991 **********
9992 **********
9993 **********
9994 **********
9995 **********
9996 **********
9997 **********
9998 **********
9999 **********
10000 **********
10 rows selected.
rajesh@ORA11G> select * from
table(dbms_xplan.display_cursor(format=>'allstats last'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID cq62wu7jvjj6g,
child number 0
-------------------------------------
select * from t t1 where id between 1 and 10000
Plan hash value: 1601196873
----------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows
|Buffers |
----------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | | 10 |
31 |
|* 1 | TABLE ACCESS FULL| T |
1 | 10 | 10 |
31 |
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
filter(("ID">=1 AND "ID"<=10000))
18 rows selected.
rajesh@ORA11G>
So empty leaf blocks can still have a large impact on not only how a
query may perform but indeed on how the CBO calculates the associated costs,
depending on how the statistics are generated
