jump to navigation

Deleted Index Entries Part II (V-2 Schneider) June 18, 2008

Posted by Richard Foote in Index Delete Operations, Oracle Indexes.
17 comments

I’m back !!

In Part I, we looked at how index entries are only marked as deleted during delete/update operations. This means the DML operation and resultant transaction doesn’t have to concern itself with physically cleaning out the deleted entries.

The question is therefore, are these deleted index entries “deadwood”, wasted space that makes the index less compact and less efficient over time requiring a potential periodic index rebuild, or can this deleted space be subsequently reused by Oracle ?

There’s a common perception that deleted space within an index is indeed “deadwood” or can only be reused in very specific circumstances, such as when an identical index value is subsequently re-inserted.

However, this is just another of Oracle’s infamous urban myths. In the vast majority of cases, deleted space within an index can indeed be recycled and can indeed be subsequently reused. Today, I’m just going to begin by focusing on the most common method by which deleted index entries are generally cleaned up.

All it takes to clean out all the deleted index entries within a specific index leaf block is a subsequent insert in the block. That’s it, that’s all it takes. Just one new index entry in a leaf block will automatically clean out all associated deleted index entries that may currently exist within the block. Even if there are hundreds of deleted entries with the leaf block, just the one new index entry will clean them all out. Note the new index value doesn’t have to be the same as any of the deleted index entries, it doesn’t even have to be within the range of any of the deleted index entries within the block. Any new index entry within the leaf block will do the job quite nicely.

A very simple demonstration I use to highlight this point. First, create a simple table and associated index with 10 rows.

SQL> CREATE TABLE del_stuff (id NUMBER, name VARCHAR2(30));

Table created.

SQL> CREATE INDEX del_stuff_i ON del_stuff(id);

Index created.

SQL> INSERT INTO del_stuff SELECT rownum, ‘Bowie’ FROM dual CONNECT BY level <=10;

10 rows created.

SQL> COMMIT;

Commit complete.

Next, deleted say 4 of the rows from the table.

SQL> DELETE del_stuff WHERE id in (2,4,6,8);

4 rows deleted.

SQL> COMMIT;

Commit complete.

A view of INDEX_STATS after a ANALYZE … VALIDATE STRUCTURE command will show the following:

LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE
------- ----------- --------------- -------
     10           4              56     140

 

Note: It clearly shows 4 index entries are indeed currently marked as deleted.

 

A treedump will show the following:

 

—– begin tree dump

leaf: 0x1402e3a 20983354 (0: nrow: 10 rrow: 6)

—– end tree dump

 

Note: It only shows 6 rrow but 10 nrow values, clearly suggesting there are currently 4 deleted index entries.

 

A partial index block dump will show the following:

 

kdxlende 4

 

row#1[7928] flag: —D–,lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 40 2e 32 00 01

 

That indeed there are currently 4 index entries marked as deleted with the ‘D’ flag within the index block.

 

However, just a single subsequent insert will clean out all 4 of these deleted index entries. Note the new value (100) is not the same value as any of the previously deleted entries and is not even within the range of previously deleted index entries:

 

SQL> INSERT INTO del_stuff VALUES (100, ‘New Row’);

 

1 row created.

 

SQL> COMMIT;

 

Commit complete.

 

Now, if we look at the same leaf block, we find:

 

LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN U_SPACE
------- ----------- --------------- -------
      7           0               0      98

 

That indeed, INDEX_STATS no longer has any statistics of deleted index entries.

 

—– begin tree dump
leaf: 0x1402e3a 20983354 (0: nrow: 7 rrow:  7)
—– end tree dump

 

That the Index Tree dump no longer has any record of the deleted index entries.

 

That the index block dump no longer has any record of the deleted index entries and that:

 

kdxlende 0

 

the count of deleted index entries within the block has been reset to 0.

 

For most randomly inserted indexes, this means deleted entries will be eventually automatically cleaned out and the freed space reused by subsequent insert operations. Worrying about deleted space and rebuilding such indexes is typically unnecessarily.

 

I’ll next discuss another method by which Oracle will clean out and remove deleted index entries from its indexes …

 

Deleted Index Entries – Part I (Let It Be) June 8, 2008

Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes, Oracle Myths.
9 comments

Just before I hop on a plane to do some training in Europe, thought I might begin a little series on deleted space within an index. I’ll begin with a short piece on how we can determine what deleted space an index may currently have before beginning a discussion on whether this deleted space can indeed be reused by Oracle.

Generally speaking, when an index entry is deleted, Oracle doesn’t physically remove the index entry, it’s simply marked as deleted. It’s another case of Oracle putting off what could be an expensive operation for the current transaction and leaving any potential clean up operations to future processes. However, there’s often some confusion whether these deleted index entries remain “deadwood” within the index structure or whether they are somehow cleaned out later and the space potentially reused by subsequent inserts in the relevant index block.

To set the scene, we begin by creating a very simple scenario. Here we create a little table and associated index, insert a single row, commit it and then delete and commit the row afterwards. We can then have a bit of a look around to see how this deleted index entry is recorded by Oracle.

 SQL> CREATE TABLE test_delete (id NUMBER, name VARCHAR2(10));

Table created.

 

SQL> CREATE INDEX test_delete_idx ON test_delete (name);

 

Index created.

 

SQL> INSERT INTO test_delete VALUES (1, ‘BOWIE’);

 

1 row created.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> DELETE test_delete WHERE id = 1;

 

1 row deleted.

 

SQL> COMMIT;

 

Commit complete.

 

We begin by looking at statistics related to the deleted index entries within the INDEX_STATS view.

 

SQL> ANALYZE INDEX test_delete_idx VALIDATE STRUCTURE;

 

Index analyzed.

 

SQL> SELECT lf_rows, del_lf_rows, del_lf_rows_len FROM index_stats;

 

   LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN
---------- ----------- ---------------
         1           1              17 

 

So yes, the one and only index entry is a deleted index entry.

 

We can also see how many current deleted entries we have by looking at an index tree dump of the index.

 

SQL> SELECT object_id FROM dba_objects WHERE object_name = ‘TEST_DELETE_IDX’;

 

 OBJECT_ID
----------
     61198

 

SQL> ALTER SESSION SET EVENTS ‘immediate trace name treedump level 61198‘;

 

Session altered.

 

Following is the index tree dump generated by the above operation.

 

—– begin tree dump
leaf: 0x14008d2 20973778 (0: nrow: 1 rrow: 0)
—– end tree dump

 

We notice that the rrow count which is the number of non-deleted index row entries is 0 but the nrow count which is the total index row entries, including deleted entries is 1. Therefore, yes the index currently consists of just the one deleted index row entry.

 

We can also view the deleted index details by performing a dump of the associated index block.

 

SQL> SELECT file_id,block_id FROM dba_extents WHERE segment_name=’TEST_DELETE_IDX’;

 

   FILE_ID   BLOCK_ID
---------- ----------
         5       2257

 

SQL> ALTER SYSTEM DUMP DATAFILE 5 BLOCK 2257;

 

 

System altered.

Below is an  extract from the above index block dump:

     Itl                    Xid                                    Uba                Flag  Lck            Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  —-      0  fsc 0x0000.00000000

0x02  0x0008.024.0000075b  0x00804e29.0078.0b  –U-      1  fsc 0x0011.00000000

  ……

kdxlende 1

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8036

row#0[8021] flag: —D–, lock: 2, len=15

col 0; len 5; (5):  42 4f 57 49 45

col 1; len 6; (6):  01 40 10 0a 00 00

 

From the above, kdxlende 1 is a count of the deleted index entries. The index entry has a D flag set, signifying that the index entry has been deleted. Also note that the index entry was locked and deleted by the ITL entry associated with ITL number 2.

 

So yes, when we perform a delete that results in the deletion of an index row entry, the deleted index entry is marked as deleted but is not physically cleaned out at the time of the delete. All the above checks confirm this current state of the index.

 

The key question is therefore, are these deleted index entries ever reused/removed, or are they forever “deadwood” that would require a periodic rebuild of the indexes to clean out ?

 

Answer coming soon …

Oracle Indexing Myths (Telling Lies) September 26, 2017

Posted by Richard Foote in Oracle Indexes.
2 comments

DavidBowie-TellingLies-CD

I’m currently hard at work fully revising and updating my “Indexing Internals and Best Practices” seminar in time for a series of events I’ll be running in Australia in October/November. This has been a highly popular and acclaimed 2 day seminar that is a must for any DBA, Developer, Solutions Architect or anyone else interested in designing, developing or maintaining high performance Oracle-based databases/applications.

For a number of reasons, there are many myths in relation to how Oracle Indexes function, how they should be maintained and how and when indexes should be implemented. One of the goals of my seminar is to go through and dispel many of these myths and demonstrate how these misconceptions can unfortunately often result in inappropriate and inefficient indexing strategies being deployed. I have investigated numerous customer databases (including large scale Exadata sites) where the database is running in a severely sub-optimal manner as a direct result of  inappropriate indexing implementations based on these myths.

Some of these misconceptions are just plain wrong, some are true in other databases but not Oracle, some might have once been true but not anymore, some might be mostly true but not always, some might be mostly false but not always and it’s often these exceptions that can make such an important difference in how indexes could/should be deployed in some environments. Words such as “never” ,”always”, “all” and “none” are dangerous to use when discussing technology as exceptions, caveats and limitations can impact/distort the truth and how such technology should be implemented.

My use of the term “Best Practices” in the seminar title is actually a deliberate, somewhat cynical reuse of a term that actually gets many customers in strife as what might be a so-called “Best Practice” in one environment might be totally inappropriate in another environments.

In no particular order, the following Oracle Indexing “myths” (or misleading generalisations) are discussed throughout my seminar and are still prevalent out there in the Oracle universe:

  • Oracle B-Tree indexes become “unbalanced” over time and need to be rebuilt
  • Deleted space in index is “deadwood” and over time requires index to be rebuilt
  • If index height greater than ‘x’, it becomes inefficient and needs to be rebuilt
  • If index grows to 2x its height, it is 2x more costly to use
  • PCTFREE enables space for index entries to grow within current leaf block
  • If index has a poor (very high) Clustering Factor, rebuild the index
  • To improve the Clustering Factor, you have to rebuild the underling table
  • Clustering Factor “as good as it gets” after gathering 100% estimate index statistics
  • To improve performance, regularly rebuild indexes
  • You never have to rebuild an index to improve performance
  • Statistics from INDEX_STATS provides reliable metrics on when to rebuild an index
  • If delete rows as reported in INDEX_STATS > x%, rebuild
  • If delete rows as reported in INDEX_STATS < x%, don’t rebuild
  • Analyze Index Validate Structure is a safe method of collecting index metrics
  • Index rebuilds are inexpensive and unobtrusive
  • Primary/Unique Key constraints require a unique index
  • Drop/disable a constraint, unique index policing index is automatically dropped
  • All Foreign Key constraints must be indexed
  • Indexes should eliminate sorting
  • Only indexed columns require statistics
  • Bitmap Indexes only useful with low cardinality columns
  • Bitmap Index will be used when a B-tree is not for low cardinality columns
  • Null values are not indexed
  • Small tables (say < 100 rows) don’t benefit from indexing
  • Separating indexes from tables in tablespaces improves performance
  • Range scans not possible with Reverse Key indexes
  • Local indexes improve performance
  • Put most discriminating column first in concatenated indexes
  • If SQL references all columns in index, index column order is irrelevant
  • If leading column of index is not referenced in SQL, index not considered by CBO
  • Monitoring Indexes will highlight which indexes can be safely dropped
  • Indexing Tracking will highlight which indexes can be safely dropped
  • Index Compression make indexes smaller
  • B-Tree Index only useful with high cardinality columns
  • Pointless indexing a column with one distinct value
  • If more than x% rows returned, index is inappropriate, where x% between 0 & 100
  • Full Table Scan more efficient than index range scan with table access, when returning 100% of data
  • The CBO cost an internal value of no practical use for tuning/comparison purposes
  • Index is best solution to return 0% of data
  • You don’t need indexes in Exadata
  • Indexes less important in Exadata, as Storage Indexes can take over if database index is missing

It’s important to note it potentially only takes just the one bad row inserted in a table, one inaccurate statistic, one index being used inappropriately, one missing index not created during a application upgrade, to bring down a whole Oracle database, RAC nodes running on an Exadata included…

If you want to learn why the above statements are misleading and lots, lots, lots more, come and attend my Indexing Seminar. Tickets for the Australian events are on sale here: https://www.eventbrite.com.au/o/richard-foote-consulting-pty-ltd-15076769762

Dates for other regions will be announced in the near future: https://richardfooteconsulting.com/seminardates/

If you wish to express an interest in attending a seminar near you, please email: richard@richardfooteconsulting.com.

 

 

 

DEL_LF_ROWS Index Rebuild Criteria ? (Codex) May 22, 2011

Posted by Richard Foote in DEL_LF_ROWS, Indexing Myth, Oracle Indexes.
19 comments

In the same article and book where the erroneous claims regarding the BLKS_GETS_PER_ACCESS were made, we also find the following recommendation:
 
Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes“.
 
This is a very common claim however no matter how often it might be stated, it’s fundamentally flawed. Note My Oracle Support previously made these claims as well, such as with Metalink note 122008.1, although this has now been totally revised with the recommendation withdrawn.
 
It’s a flawed index rebuild criteria for two very good reasons.

1) It assumes this deleted space is somehow “wasted” or “deadwood” and needs to be removed. However, in the majority of scenarios, it’s nothing more than free space which will be subsequently reused by later inserts into the index. Therefore, basing a rebuild criteria on the percentage of deleted space will likely include indexes that will not benefit from a rebuild.

2) It assumes the DEL_LF_ROWS index statistic somehow accounts for all the “deleted” space within an index. However this statistic can significantly under-estimate the potential “wasted” space within an index. Therefore basing a rebuild criteria on the percentage of  “currently marked” deleted space can possibly miss indexes that might actually benefit from a rebuild (or coalesce).
 
Any criteria which selects indexes that don’t need to be rebuilt but misses out on those that do, is as I say, fundamentally flawed. Basing a rebuild criteria on the percentage of currently marked deleted index entries suggests a lack of understanding of both how Oracle indexes work and the actual meaning behind DEL_LF_BLKS.
 
This of course has been discussed many times before, but a quick demo to illustrate. First, I’m going to create a table with 10 rows with a unique ID column.

SQL> create table bowie (id number, code number, name varchar2(30));
 
Table created.
 
SQL> insert into bowie select rownum, rownum, 'ZIGGY STARDUST' from dual connect by level <=10;
 
10 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index bowie_id_i on bowie(id);
 
Index created.

 

If we now delete four of these rows:


SQL> delete bowie where id in (2,4,6,8);
 
4 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index bowie_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_delete from index_stats;
 
LF_ROWS DEL_LF_ROWS PCT_DELETE
------- ----------- ----------
     10           4         40

 
Indeed, we currently have 4 index entries marked as deleted. If we look at a dump of this index block:
 

Block header dump:  0x01c0521b
 Object id on Block? Y
 seg/obj: 0x13039  csc: 0x00.e30e53  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1c05218 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0x0005.017.00004219  0x00c002b4.0a36.07  –U-    4  fsc 0x0038.00e30e79
Leaf block dump
===============
header address 220340836=0xd222264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 10
kdxcofbo 56=0x38
kdxcofeo 7916=0x1eec
kdxcoavs 7860
kdxlespl 0
kdxlende 4
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 c0 52 13 00 00
row#1[8012] flag: —D–, lock: 2, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 c0 52 13 00 01
row#2[8000] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  01 c0 52 13 00 02
row#3[7988] flag: —D–, lock: 2, len=12
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  01 c0 52 13 00 03
row#4[7976] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 06
col 1; len 6; (6):  01 c0 52 13 00 04
row#5[7964] flag: —D–, lock: 2, len=12
col 0; len 2; (2):  c1 07
col 1; len 6; (6):  01 c0 52 13 00 05
row#6[7952] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 08
col 1; len 6; (6):  01 c0 52 13 00 06
row#7[7940] flag: —D–, lock: 2, len=12
col 0; len 2; (2):  c1 09
col 1; len 6; (6):  01 c0 52 13 00 07
row#8[7928] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 0a
col 1; len 6; (6):  01 c0 52 13 00 08
row#9[7916] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 0b
col 1; len 6; (6):  01 c0 52 13 00 09
—– end of leaf block dump —–
 

We note there are currently 4 rows with the delete flag set (eg. row#1[8012] flag: —D–). In order to make the transaction more efficient, Oracle doesn’t physically remove the deleted index entries but marks them as logically deleted. However, any subsequent DML within the index block will result in all the deleted entries being physically removed from the leaf block.

For example, here we insert a new index entry with a value of 42. Note this value didn’t exist previously and is outside all the current values within the index leaf block:

  
SQL> insert into bowie values (42, 1, 'MAJOR TOM');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index bowie_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_delete from index_stats;
 
   LF_ROWS DEL_LF_ROWS PCT_DELETE
---------- ----------- ----------
         7           0          0

 

Note that this has resulted in the removal of the previously logically deleted index entries. There are currently no deleted index entries within the leaf block. A block dump now clearly shows the deleted index entries are now all gone:

 
 
Block header dump:  0x01c0521b
 Object id on Block? Y
 seg/obj: 0x13039  csc: 0x00.e31029  itc: 2  flg: E  typ: 2 – INDEX
     brn: 0  bdba: 0x1c05218 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  —-    0  fsc 0x0000.00000000
0x02   0x0005.012.00004213  0x00c002b4.0a36.31  –U-    1  fsc 0x0000.00e3102b
Leaf block dump
===============
header address 220340836=0xd222264
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 7904=0x1ee0
kdxcoavs 7902
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  01 c0 52 13 00 00
row#1[8000] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  01 c0 52 13 00 02
row#2[7976] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 06
col 1; len 6; (6):  01 c0 52 13 00 04
row#3[7952] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 08
col 1; len 6; (6):  01 c0 52 13 00 06
row#4[7928] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 0a
col 1; len 6; (6):  01 c0 52 13 00 08
row#5[7916] flag: ——, lock: 0, len=12
col 0; len 2; (2):  c1 0b
col 1; len 6; (6):  01 c0 52 13 00 09
row#6[7904] flag: ——, lock: 2, len=12
col 0; len 2; (2):  c1 2b
col 1; len 6; (6):  01 c0 52 15 00 00
—– end of leaf block dump —–
 

So deleted space in most scenarios is nothing more than free space that can be cleaned out and reused by subsequent DML operations.
 
Lets look at another example, this time on a larger table with 1M rows with two indexes. The first index is effectively unique while the second index has 100 evenly distributed values (and so 10000 occurrences of each value):
 

SQL> create table radiohead (id number, code number, name varchar2(30));
 
Table created.
 
SQL> insert into radiohead select rownum, mod(rownum, 100), 'OK COMPUTER' from dual connect by level <= 1000000;
 
1000000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index radiohead_code_i on radiohead(code);
 
Index created.
 
SQL> create index radiohead_id_i on radiohead(id);
 
Index created.

 

If we now delete 40% of the table, those with an ID up to 400000, this has 2 different effects on our indexes. On the ID index, it will result in the first 40% of leaf blocks containing nothing but deleted index entries. On the CODE index, it will result in all the leaf blocks containing approximately 40% of deleted index entries. Note this delete is being performed within a single very large logical transaction, not via lots of small discrete transactions, so that none the deleted entries are cleaned out. Note this also requires a sufficiently large buffer cache to cache all this data to prevent read operations (including the validate structure command itself) from subsequently cleaning out the deleted index entries as well via subsequent block cleanout operations.
 

SQL> delete radiohead where id between 1 and 400000;
 
400000 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index radiohead_code_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
 1000000      400000          40
 
SQL> analyze index radiohead_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
 1000000      400000          40

 

So both have a “large” percentage of deleted index entries, although the distribution of such deleted entries differs between the two indexes. A rebuild criteria simply based on the percentage of deleted entries would suggest both indexes need a rebuild. However, if we were to re-insert a similar amount of data again (note, even with a new range of values for the ID column):
 

SQL> insert into radiohead select rownum+1000000, mod(rownum,100), 'THE KING OF LIMBS' from dual connect by level <= 400000;
 
400000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index radiohead_code_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
 1000002           2       .0002

SQL> analyze index radiohead_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
 1007633        7633  .757517866
 

We notice that the vast majority of the deleted entries have automatically been cleaned out, without an index rebuild in sight. In the case of the ID index with 40% of index leaf blocks containing nothing but deleted index entries, even though a different range of values were inserted, the effectively “empty” leaf blocks were recycled within the index, removing all the associated deleted index entries in the process.
 
In the case of the CODE index with all the leaf blocks containing approximately 40% of deleted index entries, subsequent inserts into these leaf blocks cleaned out all the deleted index entries and reused the subsequently freed space.
  
Once you begin to understand how Oracle reuses deleted index space and how the free space related to previously deleted entries may not actually be reflected in the DEL_LF_ROWS statistic anyways, one begins to understand why basing an index rebuild criteria on the so-called ratio of deleted index entries is so flawed.

In fact, if you want to avoid some nonsensical  index rebuild criteria based on DEL_LF_ROWS, all you need to do is simply delete yet more rows, as so wonderfully here explained by Jonathan Lewis.

To illustrate, we create a table and index and populate it with 100000 rows:

SQL> create table pink_floyd (id number, code number, name varchar2(30));
 
Table created.
 
SQL> insert into pink_floyd select rownum, mod(rownum, 100), 'WISH YOU WERE HERE' from dual connect by level <= 100000;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index pink_floyd_id_i on pink_floyd(id);
 
Index created.
 
SQL> analyze index pink_floyd_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
  100000           0           0

 

So far, so good. Let’s now deleted 40% of the rows throughout the table:


SQL> delete pink_floyd where mod(id,100) between 20 and 29 or mod(id, 100) between 40 and 49 or mod(id, 100) between 60 and 69 or mod(id, 100) between 80 and 89;
 
40000 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index pink_floyd_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
  100000       40000          40
 

Oh dear, we have an index with 40% of the rows deleted. However, rather than rebuild the index, let’s simply delete a few more rows …


SQL> delete pink_floyd where mod(id, 100) = 1;
 
1000 rows deleted.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index pink_floyd_id_i validate structure;
 
Index analyzed.
 
SQL> select lf_rows, del_lf_rows, del_lf_rows/lf_rows*100 pct_deleted from index_stats;
 
 LF_ROWS DEL_LF_ROWS PCT_DELETED
-------- ----------- -----------
   60000        1000  1.66666667

What do you know, now the ratio of deleted rows is just 1.67%, no need to rebuild it now !!

If you have a rebuild criteria which selects indexes that don’t actually need to be rebuilt and it misses out on those that potentially could benefit from a rebuild, while at the same time makes indexes look less in need of a rebuild the more you actually potentially fragment the index with further deletions, well as I said at the start, such a rebuild criteria is fundamentally flawed …

Index Rebuild vs. Coalesce vs. Shrink Space (Pigs – 3 Different Ones) February 8, 2008

Posted by Richard Foote in Index Coalesce, Index Rebuild, Index Shrink, Oracle General, Oracle Indexes, Performance Tuning.
32 comments

Previously, I discussed how an ALTER INDEX  … COALESCE is going to be less expensive in terms of using resources than an equivalent ALTER INDEX … SHRINK SPACE COMPACT (or ALTER INDEX … SHRINK SPACE) as the Coalesce doesn’t have to concern itself with ensuring all leaf blocks at the physical end of the index segment have all been moved to allow for the storage to be de-allocated from the index segment. If you just want to de-fragment an index and not necessarily reduce the overall space allocated to the segment, use Coalesce rather than the Shrink options as it’s cheaper.

But what about an ALTER INDEX … REBUILD, when, if ever, should it be used ?

Well the answer is as with most things Oracle, it depends.

Scenario One.

We have a table and the application deletes historical data but in a manner in which leaf blocks are not being entirely emptied. Basically, older stuff is removed, but it’s only removed in a random manner, from approximately the “earlier” 10% of the table. The index is sequenced which means only those leaf blocks in the “left-most” 10% of the index structure are impacted but all this deleted space is “deadwood” as new index entries are only being inserted into the “right-most” part of the index.

Note that basically 90% of the index is fine and very well utilised, it’s only 10% of the index that’s problematic. Of the problem 10% of leaf blocks, there’s plenty of free or deleted space, with many leaf blocks almost but not quite empty.

Coalesce (and indeed Shrink) will basically run through these 10% of fragmented leaf blocks and will merge the index row entries into as few leaf blocks as possible. With the 90% of blocks that are fine, Coalesce will basically read and then ignore them from any processing as there’s nothing that can be done for them.

Rebuild on the other hand will take an entirely different approach. It will (generally) read the entire existing index structure and will build a brand new, bright and shining index segment.  As part of this process, it will rebuild the entire index, it has no choice (assuming the index isn’t partitioned, but that’s another story) and will rebuild the 90% of the index that was actually perfect to begin with. Rebuilding 90% of something that doesn’t need rebuilding doesn’t sound particularly efficient and indeed it isn’t. As a result, the index rebuild will use substantially more resources and generate substantially more redo than an equivalent Coalesce (or Shrink Space).

Scenario Two.

We have an application that deletes data and it deletes data throughout the entire index structure. The deletes are significant with a substantial proportion of the overall rows having been deleted. Additionally, the table is not going to be repopulated with anything like the same volume of data or it won’t be repopulated for a substantial period of time. As such, all this deleted index space is “deadwood” as it’s not going to be used any time soon, if at all.

Now typically in this sort of scenario, it’s of course the table as much as the associated indexes that needs to be rebuilt. That’s a key point. However, maybe Full Table Scans are not an issue for this table so the wasted space in the table is not of urgent concern. Maybe the table in not in an ASSM tablespace or in a database that supports a Table Shrink command and maybe moving the table is not an immediate option due to availability concerns. For whatever reason (or lack of reason), the index needs to be de-fragmented.

Note it’s the entire index that’s problematic here and there could be portions of the index that have very few remaining index entries.

Now poor Coalesce (and indeed Shrink) has a bit of an issue here. They both merge index entries from two blocks into the one block where it can. However, if leaf blocks are really empty, these merged index entries may in turn be merged and moved again with index entries from yet another leaf block. And maybe yet again with another leaf block. And again and again … So a specific index entry may actually be moved into several different leaf blocks during the entire process. Each of these moves requires resources and generates redo and takes time.

Now the rebuild has an entirely different approach. As mentioned, it will basically (generally) read the entire exisiting index structure and will build a brand new one, but importantly as it does so will only have to locate a specific index entry once and once only. Also, as it’s the entire index structure that’s problematic, there’s no issue with fixing the entire index, as it’s all “broken”.

As a result of only having to deal with an existing index entry the once vs. the Coalesce which may relocate a specific index entry many times, the index rebuild is going to be substantially more efficient and potentially use significantly less resources and generate less redo.

This demo of the Differences between a Coalesce, Shrink Space and Rebuild shows when one out performs the other.

Basically, Coalesce is particularly efficient and uses less resources when the percentage of the overall index structure that’s problematic and fragmented is relatively small (less than approximately 20-25% of leaf blocks). Rebuild is particularly efficient when the percentage of the overall index structure that’s problematic and fragmented is relatively large and the average degree of fragmentation within an index leaf block is relatively high.  Note Pre 10g, an index needed to have at least 50% free space less pctfree in neighbouring leaf blocks for a Coalesce to be effective.

Now Rebuild (and Rebuild Online) potentially have locking implications that need to be considered although as we’ll see later, 11g has addressed some of these issues …

Index Internals Seminar December 15, 2007

Posted by Richard Foote in .
comments closed

I resigned from Oracle in September 2017 and started my own independent company Richard Foote Consulting. As such I will again for running my “Oracle Indexing Internals and Best Practices” seminar.

Please contact me at richard@richardfooteconsulting.com for further information or upcoming seminars or visit my Seminar Dates page.

I run a very in-depth 2 day Oracle Indexing Internals and Best Practices Seminar. There is also a condensed 1 day version of the Oracle Index Internals and Best Practices Seminar upon request.

Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.

This seminar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the seminar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy.  It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as maximising Oracle database investments. Much of the material is exclusive to this seminar and is not generally available in Oracle documentation or in Oracle University courses.

This is a must attend seminar that would be of much benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. The seminar is developed and personally delivered by Richard “Mr Index” Foote, a well-respected expert in Oracle Database technologies. All seminars are small class environments, with plenty of opportunity for attendees to ask questions specific to their particular environment.

For list of all upcoming seminars, see the Seminar Dates page.

All seminars include:

  • Detailed course notes
  • Tea/Coffee
  • Lunch

 

Seminar Content (Subject To Minor Changes)

In no particular order, the following Oracle Indexing “myths” (or misleading generalisations) are discussed throughout my seminar and are still prevalent out there in the Oracle universe:

  • Oracle B-Tree indexes become “unbalanced” over time and need to be rebuilt
  • Deleted space in index is “deadwood” and over time requires index to be rebuilt
  • If index height greater than ‘x’, it becomes inefficient and needs to be rebuilt
  • If index grows to 2x its height, it is 2x more costly to use
  • PCTFREE enables space for index entries to grow within current leaf block
  • If index has a poor (very high) Clustering Factor, rebuild the index
  • To improve the Clustering Factor, you have to rebuild the underling table
  • Clustering Factor “as good as it gets” after gathering 100% estimate index statistics
  • To improve performance, regularly rebuild indexes
  • You never have to rebuild an index to improve performance
  • Statistics from INDEX_STATS provides reliable metrics on when to rebuild an index
  • If delete rows as reported in INDEX_STATS > x%, rebuild
  • If delete rows as reported in INDEX_STATS < x%, don’t rebuild
  • Analyze Index Validate Structure is a safe method of collecting index metrics
  • Index rebuilds are inexpensive and unobtrusive
  • Primary/Unique Key constraints require a unique index
  • Drop/disable a constraint, unique index policing index is automatically dropped
  • All Foreign Key constraints must be indexed
  • Indexes should eliminate sorting
  • Only indexed columns require statistics
  • Bitmap Indexes only useful with low cardinality columns
  • Bitmap Index will be used when a B-tree is not for low cardinality columns
  • Null values are not indexed
  • Small tables (say < 100 rows) don’t benefit from indexing
  • Separating indexes from tables in tablespaces improves performance
  • Range scans not possible with Reverse Key indexes
  • Local indexes improve performance
  • Put most discriminating column first in concatenated indexes
  • If SQL references all columns in index, index column order is irrelevant
  • If leading column of index is not referenced in SQL, index not considered by CBO
  • Monitoring Indexes will highlight which indexes can be safely dropped
  • Indexing Tracking will highlight which indexes can be safely dropped
  • Index Compression make indexes smaller
  • B-Tree Index only useful with high cardinality columns
  • Pointless indexing a column with one distinct value
  • If more than x% rows returned, index is inappropriate, where x% between 0 & 100
  • Full Table Scan more efficient than index range scan with table access, when returning 100% of data
  • The CBO cost an internal value of no practical use for tuning/comparison purposes
  • Index is best solution to return 0% of data
  • You don’t need indexes in Exadata
  • Indexes less important in Exadata, as Storage Indexes can take over if database index is missing

It’s important to note it potentially only takes just the one bad row inserted in a table, one inaccurate statistic, one index being used inappropriately, one missing index not created during a application upgrade, to bring down a whole Oracle database, RAC nodes running on an Exadata included…

 

Session One: Overview of Oracle Index Structures and Options

  • Common Myths and Misconceptions
  • Oracle Indexing Structures
  • Oracle Indexing Options

Session Two: Introduction To B-Tree Indexes

  • Understanding B-Tree Index Structure and Navigation
  • B-Tree Index Structure Internals
  • Tree Dumps
  • Index Block Dump Examination
  • Study of DML Operations on Index Internals
  • Study of Concurrent Transactions on Index Internals
  • Index Block Split Internals
  • Index Root Block Internals
  • Index INITRANS

Session Three: Index Statistics

  • Available Index Statistics – DBA_INDEXES, INDEX_STATS, V$SEGMENT_STATS
  • Shortfalls with VALIDATE STRUCTURE
  • Clustering Factor Study: The Most Important Statistic
  • How To Ensure Clustering Factor Has Appropriate Value
  • Clustering Attribute
  • Table Re-Organisation And Index Importance
  • TABLE_CACHED_BLOCKS
  • Index Height
  • Statistics Collection

Session Four: Indexes and Constraints

  • Indexing PK and Unique Constraints
  • Important Differences Between Unique and Non-Unique Indexes
  • Indexing Options With Database Constraints
  • Impact of Constraint Options on Indexing
  • Indexing and Constraint Management
  • Use Cases For Multiple Indexes On Same Column List
  • IGNORE_ROW_ON_DUPKEY_INDEX Use Cases
  • Foreign Keys and Indexing Strategies
  • 18c Memoptimised Rowstore
  • 18c Scalable Indexes

Session Five: Rebuilding, Coalescing and Shrinking Indexes

  • Index PCTFREE
  • Deleted Index Space Management
  • Index Fragmentation Internals
  • How To Avoid Index Fragmentation
  • Index Rebuild Criteria: The Chosen Few
  • How To Determine Index Optimal Size
  • Dangers Of Index Rebuilds
  • Index BLEVEL
  • Index Rebuild Internals
  • Index Coalesce Internals
  • Index Shrink Internals
  • When to Rebuild or Coalesce or Shrink
  • Deferred Invalidation During Index Maintenance

Session Six: Indexes And The Cost Based Optimizer (CBO)

  • Indexes and CBO Case Studies
  • How CBO Calculates Selectivity
  • How CBO Costs Index Accesses
  • How to Use Index To Access 100% of Rows
  • Table Clustering Attribute
  • CBO and System Statistics
  • Indexes vs. Full Table Scans
  • Index Related Access Paths
  • Indexes and Sorting
  • Index Related CBO Parameters
  • Online Table Maintenance and Index Impact

Session Seven: Miscellaneous Index Tips, Tricks and Traps

  • Concatenated Index Column Order Study
  • Why Isn’t Oracle Using My Index ?
  • Outliers and Other Statistic Anomalies
  • Using B-Tree Indexes With Low Cardinality Columns
  • Indexing NULLs
  • Using Zero Sized Indexes
  • Index Compression Options
  • Index Monitoring
  • Tracking Index Usage
  • Indexing Small Tables
  • Indexing Extended Data Types
  • Indexing Encrypted Data
  • SKIP_UNUSABLE_INDEXES

Session Eight: Additional Indexing Options

  • Reverse Key Indexes Internals
  • Index Organized Tables Internals
  • Index Organized Table Secondary Indexes Internals
  • Function-Based Indexes and Virtual Columns
  • Fake Indexes
  • Invisible Indexes
  • Case In-Sensitive Indexes
  • Indexing JSON Document Store

Session Nine: Partitioned Indexes

  • Partitioning Options
  • Partitioned Index Block Dumps
  • Partition Pruning
  • Global Indexes (Partitioned / Non-Partitioned)
  • Local Indexes (Prefixed / Non-Prefixed)
  • Unique Partitioned Indexes
  • Partial Indexes
  • Partition Access Paths
  • Online Partition Index Conversion
  • Asynchronous Global Index Maintenance
  • Partition Statistics

Session Ten: Bitmap Indexes

  • Overview Of Bitmap Indexes
  • Bitmap Index Block Internals
  • Bitmap Index Misconceptions
  • Bitmap Index Size Considerations
  • Bitmap Index Access Paths
  • Star Transformations
  • OLTP and Bitmap Indexes: Locking Implications
  • Bitmap-Join Indexes
  • Bitmap Index Restrictions

 

Optional Section: Indexing with Exadata

  • Exadata Smart Scans vs. Index Accesses
  • Storage Indexes and their Limitations
  • Zone Maps