In my previous post I discussed when deleting rows from parent tables, how Bitmap Indexes based on the FK constraint can prevent the expensive Full Tables Scans (FTS) on the child tables but not the associated exclusive table locks.

With only a visible Bitmap Index in place, does that mean we’ll have table locking issues if we delete a parent row with current transactions in place ? Let’s check it out.

In one session, we have a current transaction on the child table:

SQL> insert into bowie_kid values (1000001, 'LOW', 4);
1 row created.

In another session, we attempt to delete a parent row (with an ID = 1 which doesn’t currently exist with the child table):

SQL> delete bowie_dad where id = 1;
1 row deleted.

We note the DML was successful and didn’t hang. This means the B-Tree index is clearly being used to police this constraint, even though it’s currently invisible.

In a third session, we now attempt to insert a child row using a FK value that’s in the process of being deleted:

SQL> insert into bowie_kid values (1000003, 'HEROES', 1);

As expected, it hangs as it’s currently effectively waiting on the row level lock made possible by the index entry in the B-Tree index as invisible indexes are still maintained behind the scenes. If we look at a fresh block dump of both indexes, beginning with the Bitmap Index:

It has been updated and lists a new index entry C1 02 as the minimum value now in the index.

So the B-Tree index can be used to successfully police the FK index and prevent the possible table level locking issues associated with deleting parent rows, even though it’s invisible and there is an equivalent visible Bitmap index in place. Invisible indexes are simply not considered as viable execution paths by the Cost Based Optimizer, but may still be “visible” in a variety of scenarios such as quietly policing constraints behind the scenes.

Do I recommend creating two such indexes in Oracle Database 12c. Well, no as the costs of maintaining both indexes need to be considered. But I certainly do caution simply making indexes invisible and expecting the database to behave in exactly the same manner if the index were to be subsequently dropped.

Because rolling back all the above and then dropping the invisible index:

12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their use online and far less intrusive. The following commands now have a new ONLINE option:

DROP INDEX ONLINE

ALTER INDEX INVISIBLE/VISIBLE ONLINE

ALTER INDEX UNUSABLE ONLINE

So if we look at a little example (initially on 11g R2), where we create a table and associated index on the CODE column:

After spending the last few weeks traveling throughout Europe, it’s about time I wrote a new post on the Blog !!

I’ve previously posted a simple demo on Invisible Indexes, a new 11g feature that allows you to quickly make an index “invisible” to the CBO.

Recently, a comment on the above post by Michael Sorensen mentions a nice post by Christian Antognini in which he demonstrates how so-called Invisible Indexes on Foreign Keys can still be used by Oracle to prevent locking and performance related issues when delete/update operations are performed on the parent records.

I previously posted how Index Monitoring on FK indexes doesn’t pick up the fact so-called “Unused Indexes” can actually be “used” by Oracle for exactly this reason, quickly determining whether there’s an existing FK record that needs to prevent the update or delete operation on the parent record from proceeding.

It should therefore come as no surprise that Invisible Indexes might not be so invisible after all when it comes to the CBO potentially using the index statistics of an Invisible Index to determine the correct cardinality of a query.

This demo on Invisible Indexes and Index Statistics clearly shows that Oracle can still use the statistics of an index, even after they’ve been made invisible, to determine a more accurate cardinality for a query.

This means you need to be very careful how you use and apply Invisible Indexes and have a clear understanding on how Invisible Indexes are implemented by Oracle and the purpose for their use.

As I explain in my Indexing Seminar, Invisible Indexes are primarily intended to be applied when an application has suddenly and inexplicably started to use an index inappropriately and the database has significant performance issues as a result. The scenario is such that the performance of the database or application is so bad, it has caused the drain of resources to such an extent that normal business functions can’t continue and drastic action is required to address the issue.

The drastic action is to make the problematic index invisible so that the CBO can no longer see and use the index within an execution plan. This is drastic because it means other queries that could currently be using the index appropriately will also be prevented from using the index but performance is so bad that such a consequence can’t make matters any worse. By making the index invisible at least the resource drain can be prevented and hopefully most of the other business activities can now continue within the database.

By making the index invisible rather than simply dropping it, it means some sessions can investigate what the problem might be that’s causing the inappropriate use of the index (by altering sessions to OPTIMIZER_USE_INVISIBLE_INDEXES = true). Once the issue has been solved and addressed, the index can quickly be made visible again, rather than having to recreate the index from scratch.

Invisible indexes could prove to be a handy option in such scenarios.

Invisible indexes are also often viewed as being a useful method of determining whether it’s safe to drop an existing index that may not actually be used by an application. An unused index is a waste of both storage and resources in maintaining the thing but it’s often difficult to determine what the ramifications might be if an index were to be dropped. By making an index invisible, it may be possible to “test the waters” so to speak and determine how an application may suddenly behave without the index.

However, making an index invisible may not necessarily provide an accurate determination of the ramifications of dropping such an index because as has already been demonstrated, there are a number of important and key areas in which invisible indexes are still being used by Oracle. Dropping such an index is not the same as simply making an index invisible and may result in the application suddenly behaving in an unexpected manner that could cause significant performance issues.

Invisible indexes are not entirely invisible if you see what I mean ;)

New in 11g are “Invisible Indexes”, which are basically indexes that exist and are maintained by Oracle but are “invisible” to the CBO. Specific sessions can be set to see these invisible indexes as necessary.

Potentially useful if one has a problematic (and very large) index causing performance issues that you want to make invisible until the specific issue is addressed without the expensive of having to drop and latter recreate the index. Also useful if you want to introduce a new index but want it to be invisible until it’s been given a workout first in a specific “test” session.