NOVALIDATE Constraints Part II – Does It Matter ? July 30, 2008
Posted by Richard Foote in Constraints, Novalidate Constraints, Oracle Cost Based Optimizer, Oracle Indexes, Primary Key, Unique Indexes.13 comments
As promised, more on NOVALIDATE constraints.
As previously discussed, a Primary Key or a Unique Key constraint that’s not validated is by default policed by a Unique index regardless. If there are no duplicate values, then no worries (yes, I’m Australian), Oracle will create the Unique index and enable the constraint in a NOVALIDATE state. If there are duplicate values, Oracle will complain about creating the Unique Index to police the constraint and you must either explicitly create a Non-Unique index when creating the constraint or use an existing Non-Unique index.
So what are the implications, if any, of having a Primary key constraint in a NOVALIDATE state, especially if a Unqiue index is used to police the constraint ? The data must really be unique else the Unique Index could not have been created, right ? Also Oracle can take advantage of all the benefits associated with having a Unique index such as less consistent reads and latching overheads as previously discussed.
Following on from the demo in Part I, if we have a table with a Primary Key in a NOVALIDATE state, policed by a Unique Index:
SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) ENABLE NOVALIDATE;
Table altered.
SQL> SELECT constraint_name, validated, uniqueness
FROM user_constraints c, user_indexes i
WHERE c.constraint_name = i.index_name AND c.table_name= ‘ZIGGY’;
CONSTRAINT_NAME VALIDATED UNIQUENES --------------- ------------- --------- ZIGGY_PK NOT VALIDATED UNIQUE
Oracle will be able to use the Unique index to perform a nice, efficient, low latching Unique Scan with the index:
SQL> SELECT * FROM ziggy WHERE id = 42;
------------------------------------------ |Id|Operation |Name | ------------------------------------------ | 0|SELECT STATEMENT | | | 1| TABLE ACCESS BY INDEX ROWID|ZIGGY | |*2| INDEX UNIQUE SCAN |ZIGGY_PK| ------------------------------------------
Everything’s perfect regardless of the PK constraint not being validated, right ?
Well, not exactly.
Remember, a PK constraint requires the data to be Unique AND Not Null. Now the Unique Index guarantees the data is indeed unique but it does nothing to protect us from having possible NULL values in our data. The index will simply ignore and not index any index entries that are fully NULL, therefore the PK column(s) could potentially, just maybe, contain NULLS. Brian Tkatch in a comment in Part I has a nice example of how this is possible.
This mean Oracle can not guarantee the index has index entries for every row in the table as any rows with a NULL PK will not be indexed. This can have serious reprecussions for the CBO when deciding an optimal execution plan.
For example, a query such as the following COUNT(*) query which could potentially be serviced via a “smaller” PK index segment can not use the Unique index and is forced to use either another index or a Full Table Scan:
SQL> select count(*) from ziggy;
--------------------------------- | Id| Operation | Name | --------------------------------- | 0| SELECT STATEMENT | | | 1| SORT AGGREGATE | | | 2| TABLE ACCESS FULL| ZIGGY| ---------------------------------
Another example, this query with an ORDER BY clause could potentially use the Unique index to retrieve the data and so avoid the sort operation as the Clustering Factor of the index is very good. However, it can’t as again, the CBO can’t guarantee all data will be retrieved via the index:
SQL> select * from ziggy order by id;
10000 rows selected.
--------------------------------- | Id| Operation | Name | --------------------------------- | 0| SELECT STATEMENT | | | 1| SORT ORDER BY | | | 2| TABLE ACCESS FULL| ZIGGY| ---------------------------------
However, if only we just validate the constraint, everything changes:
SQL> ALTER TABLE ziggy ENABLE VALIDATE PRIMARY KEY;
Table altered.
The COUNT(*) query suddenly starts using the index as a cheaper alternative as now, there can’t be any null values and so the index must reference all possible rows:
SQL> select count(*) from ziggy;
------------------------------------- |Id|Operation | Name | ------------------------------------- | 0|SELECT STATEMENT | | | 1| SORT AGGREGATE | | | 2| INDEX FAST FULL SCAN| ZIGGY_PK| -------------------------------------
The ORDER BY query suddenly starts using the index and avoids performing the sort operation as again, the index will now guarantee all rows are returned in a sorted order:
SQL> select * from ziggy order by id;
10000 rows selected.
------------------------------------------ |Id|Operation |Name | ------------------------------------------ | 0|SELECT STATEMENT | | | 1| TABLE ACCESS BY INDEX ROWID|ZIGGY | | 2| INDEX FULL SCAN |ZIGGY_PK| ------------------------------------------
The moral of the story. Provide the CBO with as much information as possible, as it can potentially use the information to determine a more optimal execution plan. Having a NOVALIDATE constraint possibly hides valuable information from the CBO and so needs to be used with caution.
NOVALIDATE Constraints – No really … July 28, 2008
Posted by Richard Foote in Constraints, Indexing Tricks, Novalidate Constraints, Oracle Indexes, Primary Key, Unique Indexes.28 comments
There have been a number of posts recently on the OTN database forum regarding the whole topic of NOVALIDATE of constraints and the associated indexes so I thought it might be worth going over a couple of interesting little quirks with all this.
A NOVALIDATE constraint is basically a constraint which can be enabled but for which Oracle will not check the existing data to determine whether there might be data that currently violates the constraint.
This is useful if we know there’s data that violates the constraint but we want to quickly put on a constraint to prevent further violations, with the intention to clean up any possible violations at some future point in time.
It’s also potentially useful if we know the data is clean and so want to prevent the potentially significant overheads of Oracle having to check all the data to ensure there are indeed no violations.
I previously discussed the use of Non-Unique Indexes for manageing Primary and Unique Key Constraints but there are a few little traps one can easily fall into if one doesn’t understand these two very important fundamentals:
- By default, Oracle will attempt to create a Unique Index to police a PK or UK constraint
- A NOVALIDATE constraint requires a Non-Unique Index for the constraint to really be “Novalidated”
Get these two concepts confused and things can easily get a little difficult to follow …
Here’s a little example of how things can start to get confusing. First, let’s create a simple little table and populate it with a few rows.
SQL> CREATE TABLE ZIGGY (id NUMBER, text VARCHAR2(20));
Table created.
SQL> INSERT INTO ziggy SELECT rownum , ‘Ziggy’ FROM dual CONNECT BY LEVEL <= 10000;
10000 rows created.
Note that the ID column is populated with unique values. However, let’s now introduce a duplicate value, 42:
SQL> INSERT INTO ziggy VALUES (42, ‘DUPLICATE’);
1 row created.
SQL> COMMIT;
Commit complete.
OK, we now want to add a Primary Key to this table but because we suspect there might be some duplicate values which we intend to clean up at some future point in time, we want to create the constraint with NOVALIDATE:
SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) ENABLE NOVALIDATE;
ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) ENABLE NOVALIDATE
*
ERROR at line 1:
ORA-02437: cannot validate (BOWIE.ZIGGY_PK) – primary key violated
Now what the hell is going on here ?
We clearly stated we want to create a NOVALIDATE constraint but Oracle appears to be ignoring this and is validating the constraint regardless and so generating an error because of the duplicate entry.
Why ?
Because by default Oracle will attempt to create a Unique index when creating a PK constraint. A Unique index MUST always contain unique values and so complains when it stumbles across our duplicate 42 ID value. The constraint is being effectively validated because the unique index will only be created providing there are indeed no duplicate values.
Not how I would have designed things but there you go …
However, if we either have an existing Non-Unique index which Oracle can use or we explicitly create a Non-Unique index, then we can proceed with creating the NOVALIDATE constraint as required:
SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id)
USING INDEX(CREATE INDEX ziggy_pk ON ziggy(id)) ENABLE NOVALIDATE;
Table altered.
If we look at the status of the constraint and the type of index used to police the constraint, we notice that the index is indeed a Non-Unique index and the constraint has not been validated:
SQL> SELECT constraint_name, validated, uniqueness
FROM user_constraints c, user_indexes i
WHERE c.constraint_name = i.index_name AND c.table_name= ‘ZIGGY’;
CONSTRAINT_NAME VALIDATED UNIQUENES --------------- ------------- --------- ZIGGY_PK NOT VALIDATED NONUNIQUE
We have a PK constraint even though there are currently duplicate values of the PK column in the data.
OK, let’s now drop and the constraint, the Unique Index and delete the duplicate row:
SQL> ALTER TABLE ziggy DROP PRIMARY KEY;
Table altered.
SQL> DROP INDEX ZIGGY_PK;
Index dropped.
SQL> DELETE ziggy WHERE id = 42 and rownum <= 1;
1 row deleted.
SQL> COMMIT;
Commit complete.
The data is now clean and we have no existing constraint or index on the ID column:
SQL> SELECT constraint_name, validated FROM user_constraints WHERE table_name= ‘ZIGGY’;
no rows selected
Let’s now do something that based on our understanding might appear to be a little odd, let’s try and recreate the constraint in a NOVALIDATE state but with a Unique index. This of course should now work as there are indeed no duplicates within the data:
SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) ENABLE NOVALIDATE;
Table altered.
Success !! Let’s now look at the state of the constraint and the type of index created:
SQL> SELECT constraint_name, validated, uniqueness
FROM user_constraints c, user_indexes i
WHERE c.constraint_name = i.index_name AND c.table_name= ‘ZIGGY’;
CONSTRAINT_NAME VALIDATED UNIQUENES --------------- ------------- --------- ZIGGY_PK NOT VALIDATED UNIQUE
As expected, we have a constraint that’s policed by a Unique index that has not been validated.
This might appear be a little odd, because the question you might well now ask is why bother no validating a constraint that has effectively been validated anyways as the use of the Unique index has guaranteed there can not possibly be any duplicate values else the creation of the Unique index would have failed ?
We effectively have a validated constraint which Oracle is still classifying as being not validated 🙂
Then again, maybe not …
More later.
Time For A Catch Up … July 23, 2008
Posted by Richard Foote in Music, Travel.6 comments
Well, it’s certainly been a hectic time these past few weeks so I thought it might be time to catch up on a few things.
I’ve just returned from attending and presenting at the Oracle Days 2008 Conference held by the Puget Sound Oracle User Group in Seattle, USA (well Bellevue actually). I had a great time, met some really nice people, caught up with a few old faces and generally had a ball. The conference had some great presentations and deserved a much better turn out although I suspect the beautiful weather made many people consider outdoor activities. My presentations were well attended and received great feedback so I was really pleased with my personal contributions. I would strongly recommend people check out this event when it’s held again next year. Thanks to Daniel Morgan and the gang for looking after me so incredibly well and to Tom Kyte for teaching me the difference between a torch and a flashlight !!
The results are in for the Oracle Mix nominated presentations for this year’s Oracle OpenWorld and my Indexing Secrets presentation has been selected, finally coming in at Number 7. I’m still not sure I can get to OpenWorld this year as finances are somewhat tight at the moment but fingers crossed, I can somehow get myself across the Pacific Ocean again this year and do this presentation for those of you attending. Still working on that one, here’s hoping …
Enrollments for my Index Internals Seminar “Australian Tour” in September are going well. Currently I’m featured on the Australian Oracle University home page so that dreadful photo can now be seen all over the place. There are still places available but don’t leave it too late …
David Bowie has released probably one of his finest concerts on CD this week, David Bowie Live Santa Monica ’72:
I remember when I first bought this concert featuring David Bowie as Ziggy Stardust during his first US tour in 1972 on a bootleg many many years ago. I loved it then and I love it now with some great packaging and a nice clean sound. David Bowie at his absolute best !! Been listening to it ever since I got back from the US.
Finally, a big thank you to all those who made comments and suggestions for the blog moving forward. Will definitely try and incorporate as many of them as I can in the coming weeks, months, years 🙂
Request Time (Join The Gang) July 11, 2008
Posted by Richard Foote in Richard's Musings.16 comments
I’ve just passed yet another milestone since starting this Blog. I’ve just deleted my 500th spam comment.
How exciting is that !!
To celebrate this momentous occasion, I thought I might open up the Blog to some requests and suggestions.
Are there any particular topics you would like covered ? Is there something I might already have previously touched upon you think would be worth expanding ? Do you think I go into too much detail or too little ? Are you still annoyed I got you on April Fool’s day or are you simply hanging out to find out what my favourite top 10 movies of all time might be 🙂
Or maybe it’s simply just a case of more of the same please ?
There’s still a whole heap to index related topics I haven’t even touched yet however I thought it might be worth touching base with those of you who read these humble little posts regarding what might be of interest going forward.
I won’t promise to be able to meet each and every request but it’s an opportunity for you to at least get me thinking about what might be worth covering and how to best to discuss them.
Last time I did something similar, I received far more emails than blog comments and that’s fine if you prefer to make suggestions offline.
Empty Leaf Blocks and Statistics (Sense Of Doubt) July 8, 2008
Posted by Richard Foote in Index Access Path, Index Block Splits, Index Delete Operations, Index statistics, Oracle Cost Based Optimizer, Oracle Indexes, Performance Tuning.15 comments
I’ve recently been discussing how empty index blocks or those blocks that contain nothing but deleted index entries are placed on the index freelist and can potentially be recycled during subsequent index block split operations.
A point that’s not so well known about such empty index blocks is how Oracle considers them when calculating index related statistics and the possible implications this may have on the CBO.
Let’s set the scene with an example I’ve used previously where we load a table/index with 10000 entries and then subsequently delete the vast majority of them.
SQL> create table rich as select rownum id, ‘Bowie’ text from dual connect by level <= 10000;
Table created.
SQL> create index rich_i on rich(id);
Index created.
OK, so we now have an index with 10000 entries. Let’s just check to see how many leaf blocks we currently have:
SQL> analyze index rich_i validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS ---------- ---------- ----------- 10000 21 0
So we currently have 10000 LF_ROWS and 21 LK_BLKS with no deleted index rows at this stage.
Let’s now deleted the vast majority of rows from the table and hence index row entries from the index:
SQL> delete rich where id <= 9990;
9990 rows deleted.
SQL> commit;
Commit complete.
OK, so now we have an index with the vast majority of the index entries having been deleted and with all but one index leaf block effectively empty.
Let’s start by looking at how the ANALYZE INDEX … VALIDATE STRUCTURE deals with empty leaf blocks and index entries:
SQL> analyze index rich_i validate structure;
Index analyzed.
SQL> select lf_rows, lf_blks, del_lf_rows from index_stats;
LF_ROWS LF_BLKS DEL_LF_ROWS ---------- ---------- ----------- 10000 21 9990
The first thing we notice is that the LF_ROWS statistics still has a value of 10000. It still counts index entries, even if they’ve been deleted.
We also notice that the LF_BLKS value is 21 so those leaf blocks that are effectively empty are still counted as well.
Let’s now collect statistics using DBMS_STATS as currently recommended by Oracle:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> ‘RICH’, cascade => true, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1’);
If we now look at the index statistics:
SQL> select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘RICH_I’;
INDEX_NAME NUM_ROWS LEAF_BLOCKS ---------- -------- ----------- RICH_I 10 1
We notice a couple of important differences. Firstly, the NUM_ROWS value is 10, highlighting that only non-deleted index entries are counted. We also notice that the number of LEAF_BLOCKS is only 1, highlighting that only those index leaf blocks that contain non-deleted index entries are counted. Although there are 20 other leaf blocks within the index structure, these are not counted and considered by the CBO when statistics are calculated using DBMS_STATS.
If we run the following simple little query that effectively selects all remaining rows from the table, we notice the following execution plan:
SQL> select * from rich where id between 1 and 10000;
ID TEXT ---------- ----- 9991 Bowie 9992 Bowie 9993 Bowie 9994 Bowie 9995 Bowie 9996 Bowie 9997 Bowie 9998 Bowie 9999 Bowie 10000 Bowie Execution Plan -------------------------------------------- |Id | Operation | Name | -------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS BY INDEX ROWID| RICH | |*2 | INDEX RANGE SCAN | RICH_I | --------------------------------------------
The index is actually used to select all the remaining 10 rows, in part because the index related costs are so low.
Let’s see what would happens if we were to use the old, ANALYZE command to calculate the index statistics:
SQL> analyze index rich_i compute statistics;
Index analyzed.
First, let’s see if the index statistics are any different …
select index_name, num_rows, leaf_blocks from dba_indexes where index_name = ‘RICH_I’;
INDEX_NAME NUM_ROWS LEAF_BLOCKS ---------- -------- ----------- RICH_I 10 21
OK, a big big difference here. Where previously, DBMS_STATS didn’t include the empty leaf blocks in it’s statistics, we now notice that using the ANALYZE command does include such empty leaf blocks. The LEAF_BLOCKS value is now 21, not 1 as it was previously. Note though that the number of NUM_ROWS is still 10, so it still doesn’t count the deleted index entries themselves, just the empty leaf blocks.
But leaf blocks is one of the key statistics used by the CBO when calculating the cost of using an index related access path. Could this all make a difference in how our previous query is costed by the CBO ?
SQL> select * from rich where id between 1 and 10000;
ID TEXT ---------- ----- 9991 Bowie 9992 Bowie 9993 Bowie 9994 Bowie 9995 Bowie 9996 Bowie 9997 Bowie 9998 Bowie 9999 Bowie 10000 Bowie 10 rows selected. Execution Plan ---------------------------------- | Id | Operation | Name | ---------------------------------- | 0 | SELECT STATEMENT | | |* 1 | TABLE ACCESS FULL| RICH | ----------------------------------
Oh yes indeed. Now the CBO has decided to use a Full Table Scan, in large part because of the additional calculated costs associated with using the index.
Note these tests work the same on all supported versions of Oracle.
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.
Yes, there are differences between the ANALYZE command and DBMS_STATS. This is one of the more subtle differences …
Ad: Register For Index Internals Seminar European Autumn Dates July 4, 2008
Posted by Richard Foote in Oracle Index Seminar.3 comments
Just a quick note to say you can now book a place on my Oracle Index Internals Seminar as part of the Oracle University Celebrity Seminar Series scheduled for various European locations in November.
Dates and links are:
Hope to catch up with many of you at one of these events.
Also, hoping that Finland is not as cold in November as I’m lead to believe …
3 Steps To Performance Tuning (Working Class Hero) July 3, 2008
Posted by Richard Foote in Oracle Opinion, Performance Tuning, Richard's Musings.8 comments
Last night, I answered a question on the Database OTN forum regarding Database Re-Org and Performance Tuning. I thought it might be worthwhile sharing my response here as it’s something I feel quite strongly about.
Basically my response to the question of what basic steps one should follow when performing performance tuning was:
1) Identify an actual problem that needs addressing, one that’s problematic to the business, not one that only exists in some statistic or in one’s imagination
2) Determine what’s actually causing the problem as identified in Step 1.
3) Address the specific issue as identified in Step 2.
It all sounds rather obvious but it’s amazing how many don’t follow these 3 basic steps and attempt to jump straight to Step 3.
Unless you perform Step 1, you can’t accurately perform Step 2 which means you’ll only be guessing when performing Step 3.
The secret to performance tuning is not to guess …
I’ve lost count of the number of times I see people guessing at what a problem might be and hence get it all wrong …
The number of times people waste time and resources on problems that aren’t really problems and hence make no measurable difference …
The number of times people throw hardware at a problem without fully considering whether additional hardware will actually resolve the problem and hence waste money and resources for no measurable benefit …
The number of times people jump straight to applying a solution to a problem that they haven’t properly or correctly diagnosed and hence don’t actually solve the issue …
The number of times people attempt to resolve a problem by focusing on the symptoms rather than the root cause, only to fail dismally …
The number of times people are lucky and fix a problem by guesswork and by fumbling around in the dark without understanding why it fixed the problem, only to attempt the same thing again at another time and for it to fail dismally …
Like I said, the secret to performance tuning is not to guess.
John Lennon was once quoted as saying his secret to writing music was to:
1) Say what you want to say
2) Make it rhyme
3) Put a back beat to it
Three basic, fundamentally important steps. He would have made a good DBA 🙂
Deleted Index Entries Part V (Trouble) July 1, 2008
Posted by Richard Foote in Index Delete Operations, Oracle General, Oracle Indexes.Tags: Add new tag
14 comments
As discussed in Part IV, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled in subsequent index block split operations. This of course is a good thing as it helps to keep the index as small and efficient as possible without having to perform relatively expensive index maintenance activities such as index rebuilds, coalesces or shrinks.
However, an important issue is that although the effectively empty index block is free and available to be reused, it remains in place in it’s current logical location within the index structure until it’s been recycled. It’s only logically unlinked and re-positioned within the index structure at the time of the block being recycled.
This simple demo highlights this issue.
First create a table and associated index and populate it with a 10000 rows:
SQL> CREATE TABLE test_1 (id NUMBER, name VARCHAR2(30));
Table created.
SQL> INSERT INTO test_1 SELECT rownum, ‘BOWIE’ FROM dual CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
Commit complete.
SQL> CREATE INDEX test_i ON test_1(id);
Index created.
Next delete most of the rows to so that we have a number of index blocks that contain nothing but deleted index entries which can potentially be subsequently recycled:
SQL> DELETE test_1 WHERE id between 1 and 9990;
9990 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> ANALYZE INDEX test_i VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT lf_blks, del_lf_rows FROM index_stats;
LF_BLKS DEL_LF_ROWS ------- ----------- 21 9990
If we now run a select statement that forces the use of the index and which only returns the 10 remaining rows, the high number of logical I/Os highlights how Oracle still accesses the now emptied index blocks during the index range scan operation:
SQL> SELECT /*+ index (test_1) */ * FROM test_1
WHERE id BETWEEN 1 and 10000;
Execution Plan -------------------------------------------- | Id| Operation | Name | -------------------------------------------- | 0| SELECT STATEMENT | | | 1| TABLE ACCESS BY INDEX ROWID| TEST_1 | |* 2| INDEX RANGE SCAN | TEST_I | -------------------------------------------- Statistics ---------------------------------------------- 0 recursive calls 0 db block gets 25 consistent gets 0 physical reads 0 redo size 577 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
If there’s a significant time lag between index blocks being emptied and subsequently recycled, there could be some scenarios where accessing many of these effectively empty index blocks can be problematic.
Classic case is when we’re interested in the minimum value of a specific column. Oracle can potentially use an index to navigate to the first index leaf block in the index structure to quickly find the minimum value of an indexed column. However, if the first or left-most index leaf block contains no current index entries, Oracle will be forced to navigate to the next leaf block and if it’s also empty, to the next leaf block and so on until it finally comes across the first non-deleted index entry.
Similar demo, but this time lets make the table somewhat bigger for effect:
SQL> CREATE TABLE ziggy (id NUMBER, value VARCHAR2(30)) ;
Table created.
SQL> INSERT INTO ziggy SELECT rownum, ‘BOWIE’ FROM dual CONNECT BY level <=1000000;
1000000 rows created.
SQL> COMMIT;
Commit complete.
SQL> CREATE INDEX ziggy_i ON ziggy(id);
Index created.
Let’s now deleted 1/2 the index entries, all those on the left-hand side of the index structure:
SQL> DELETE ziggy WHERE id <=500000;
500000 rows deleted.
SQL> COMMIT;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(ownname=>’BOWIE’, tabname=>’ZIGGY’, estimate_percent=> null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);
PL/SQL procedure successfully completed.
We now run the following simply little SELECT statement featuring the MIN function:
SQL> SELECT MIN(id) FROM ziggy;
MIN(ID) ------- 500001 Execution Plan -------------------------------------------- | Id| Operation | Name | -------------------------------------------- | 0| SELECT STATEMENT | | | 1| SORT AGGREGATE | | | 2| INDEX FULL SCAN (MIN/MAX)| ZIGGY_I | -------------------------------------------- Statistics 0 recursive calls 0 db block gets 1115 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Notice the high numbers of consistent gets, 1115 !! Wow.
Until the empty index blocks get recycled, all those effectively empty index blocks will make this MIN select statement run poorly. Classic example where an index rebuild (or coalesce or shrink) would be benefical to improve the performance of this specific query.
SQL> ALTER INDEX ziggy_i REBUILD ONLINE;
Index altered.
SQL> SELECT MIN(id) FROM ziggy;
MIN(ID) ------- 500001 Execution Plan ------------------------------------------- | Id| Operation | Name | ------------------------------------------- | 0| SELECT STATEMENT | | | 1| SORT AGGREGATE | | | 2| INDEX FULL SCAN (MIN/MAX)| ZIGGY_I| ------------------------------------------- Statistics 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 412 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Now the consistent gets have dramatically reduced from 1115 to just 3 making the select statement run noticeably faster.
Yes, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled by Oracle but until they’ve been reused, they remain linked in place in the index structure. In some scenarios, these empty index blocks can be problematic and is a classic example of when an index rebuild might be beneficial.
Richard Foote's Oracle Blog 