Are invisible indexes special? (oracle)

Indexes in a database are not visible to our bare eyes but we have a logical idea that index is a brief record that contain the copy of the specific rowid of tables.

If god come in front of you and offer a wish to make you invisible ! what would it be like ?

will you use it for positive or negative purpose ? 😉

So lets get into invisible index

Invisible index is very useful in case of testing the efficiency of an index which you are not sure if that index performs well in a production or screw up the performance

  • When a normal index is created ,the optimizer starts its cost estimates and analyse the index if it can be used efficiently
  • Occasionally there are situations where we need to influence the optimizer to use index only during specific time
  • The invisible index can be switched visible or invisible like a button whenever we require
  • It is warned that we should be aware of the invisible indexes in our database while testing the applciation performance because oracle has to handle the index when DML operations occur
  • There is no major difference between a normal visible index and invisible index as both has to be maintained in the same way but only difference is their visibility to optimizer

Take a scenario,where we have to run a select query with join on another table

In this case,my select query takes 5.25 seconds complete the query without any index

SQL> set timing on
set autot traceonly
select a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
order by a.total_profit desc;SQL> SQL>   2    3    4

452432 rows selected.

Elapsed: 00:00:05.25

Execution Plan
----------------------------------------------------------
Plan hash value: 2839474500

--------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |   615K|    38M|       | 34681   (1)| 00:00:02 |
|   1 |  SORT ORDER BY      |        |   615K|    38M|    42M| 34681   (1)| 00:00:02 |
|*  2 |   HASH JOIN         |        |   615K|    38M|  7640K| 25133   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| SALES  |   111K|  6328K|       |  3393   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SALES1 |  5242K|    35M|       | 16635   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ORDER_ID"="B"."ORDER_ID")
   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)

I create a functional index on column trunc(order_date) on sales table to check if performance is good but im not sure if functional index improves the performance

SQL> create index ind_func_date on sales(trunc(order_id));

Index created.

Lets make the index visible first to test query ,already visible here

SQL> select index_name,visibility from dba_indexes where index_name='IND_FUNC_DATE';

INDEX_NAME                               VISIBILITY
---------------------------------------- --------------------
IDX_FUNC_DATE                            VISIBLE

Note: we can create an index as invisible 

SQL> create index ind_func_date on sales(trunc(order_id)) invisible;

Gather stats on table

SQL> exec dbms_stats.gather_table_stats(ownname=>'test1',tabname=>'sales',cascade=>true,degree=>4,method_opt=>'for all columns size auto');

PL/SQL procedure successfully completed.

Hint is used to force the optimizer to use the index

The query perform worse than full table scan after adding an index

SQL> set timing on
set autot traceonly
select /*+ index(a ind_sales_order_id) */ a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
order by a.total_profit desc;SQL> SQL>   2    3    4

452432 rows selected.

Elapsed: 00:00:08.53

Execution Plan
----------------------------------------------------------
Plan hash value: 4235492740

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time      |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |   615K|    42M|       |  1083K  (1)| 00:00:43 |
|   1 |  SORT ORDER BY                        |                    |   615K|    42M|    47M|  1083K  (1)| 00:00:43 |
|*  2 |   HASH JOIN                           |                    |   615K|    42M|  8408K|  1073K  (1)| 00:00:42 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| SALES              |   111K|  7092K|       |  1051K  (1)| 00:00:42 |
|   4 |     INDEX FULL SCAN                   | IND_SALES_ORDER_ID |  1048K|       |       |  2637   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL                  | SALES1             |  5242K|    35M|       | 16635   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ORDER_ID"="B"."ORDER_ID" AND TRUNC("ORDER_ID")=TRUNC("B"."ORDER_ID"))
   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)

Lets add another index on order id column on sales table

SQL> create index ind_sales_order_id on sales(order_id);

Index created.

Gather stats for table for our satisfaction

SQL> exec dbms_stats.gather_table_stats(tabname=>'sales',ownname=>'test1',cascade=>true);

PL/SQL procedure successfully completed.

Even after forcing the second index,still full table scan is better than using index here

We know that full table scans are not always evil !

SQL> set timing on
set autot traceonly
select /*+ index(a ind_sales_order_id) */ a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
order by a.total_profit desc;SQL> SQL>   2    3    4

452432 rows selected.

Elapsed: 00:00:08.53

Execution Plan
----------------------------------------------------------
Plan hash value: 4235492740

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time      |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |   615K|    42M|       |  1083K  (1)| 00:00:43 |
|   1 |  SORT ORDER BY                        |                    |   615K|    42M|    47M|  1083K  (1)| 00:00:43 |
|*  2 |   HASH JOIN                           |                    |   615K|    42M|  8408K|  1073K  (1)| 00:00:42 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| SALES              |   111K|  7092K|       |  1051K  (1)| 00:00:42 |
|   4 |     INDEX FULL SCAN                   | IND_SALES_ORDER_ID |  1048K|       |       |  2637   (1)| 00:00:01 |
|   5 |    TABLE ACCESS FULL                  | SALES1             |  5242K|    35M|       | 16635   (1)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ORDER_ID"="B"."ORDER_ID" AND TRUNC("ORDER_ID")=TRUNC("B"."ORDER_ID"))
   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)

So here our testing says that both indexes are useless for this particular query

Better to make indexes invisible to optimizer,but not us as we already know there are indexes hiding behind the wall

SQL> alter index IND_SALES_ORDER_ID invisible;

Index altered.

SQL> alter index IND_FUNC_DATE invisible;

Index altered.

After forcing the optimizer to use index,still optimizer would not use index as they are unusable

In the below execution plan,the outline information shows that the index is not used eventhough we forced the index with hints

SQL> set timing on
set autot traceonly
select /*+ index(a ind_func_date) */ a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a
inner join sales1 b on a.order_id = b.order_id
where trunc(a.order_date) > trunc(sysdate) - 1
ordSQL> er by a.total_profit desc;SQL>   2    3    4

452432 rows selected.

Elapsed: 00:00:05.79

Execution Plan
----------------------------------------------------------
Plan hash value: 2839474500

--------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |   615K|    38M|       | 34681   (1)| 00:00:02 |
|   1 |  SORT ORDER BY      |        |   615K|    38M|    42M| 34681   (1)| 00:00:02 |
|*  2 |   HASH JOIN         |        |   615K|    38M|  7640K| 25133   (1)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| SALES  |   111K|  6328K|       |  3393   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| SALES1 |  5242K|    35M|       | 16635   (1)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ORDER_ID"="B"."ORDER_ID")
   3 - filter(TRUNC(INTERNAL_FUNCTION("A"."ORDER_DATE"))>TRUNC(SYSDATE@!)-1)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   3 -  SEL$58A6D7F6 / A@SEL$1
         U -  index(a ind_func_date)

Again i want to test the index for some other query

SQL> alter index ind_sales_order_id visible;

Index altered.

In this case,i need only single row to be retrieved from million rows

Hence,full table scan is not the correct access path here

The better way here is to use the index which we had created earlier for order_id column

SQL> set autot traceonly
SQL> select a.country,a.item_type,a.unit_price,a.units_sold,a.total_profit from sales a where a.order_id =1120;

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 562010242

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |     1 |    50 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SALES              |     1 |    50 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IND_SALES_ORDER_ID |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

This is one of the smart way to reduce the application downtime

Monitoring the index usage

This is one way to monitor the index usage efficiently rather than investigating the execution plans

SQL> alter index IND_SALES_ORDER_ID monitoring usage;

Index altered.

SQL> alter index IND_FUNC_DATE monitoring usage;

Index altered.

SQL> select index_name,monitoring,used from v$object_usage;

INDEX_NAME                               MONITORING           USED
---------------------------------------- -------------------- ----------------------------------------
IND_SALES_ORDER_ID                       YES                  NO
IND_FUNC_DATE                            YES                  NO

Once the index is tested,we can turn off the auto monitor

SQL> alter index IND_FUNC_DATE nomonitoring usage;

Index altered.

SQL> alter index IND_SALES_ORDER_ID nomonitoring usage;

Index altered.

SQL> select index_name,monitoring,used from v$object_usage;

INDEX_NAME                               MONITORING           USED
---------------------------------------- -------------------- ----------------------------------------
IND_SALES_ORDER_ID                       NO                   NO
IND_FUNC_DATE                            NO                   NO

we can enable this parameter to make index visible to optimizer at session or system level using “OPTIMIZER_USE_INVISIBLE_INDEXES” 

Now we can force the invisible index to optimizer using hints

SQL> alter system set OPTIMIZER_USE_INVISIBLE_INDEXES=true;

System altered.

SQL> show parameter invisible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE
  • To conclude this article,obviously invisible indexes are special for adhoc query and to solve resource intensive operations like dropping of an index or make index unusable like in old times.
  • Before 11G ,index had to be tested by making them unusable.If the index performs well then index rebuild is required else drop index
  • we can keep application performance under observation for couple of days to determine if the application needs the index or not.
  • Multiple invisible indexes can be created to choose the index which is best.
  • For adhoc queries,an index might be useful suddenly but that index might not be useful in other application module

Sometimes rebuilding an index on a mammoth table takes huge time and are overhead to a production database.There are always better ways to deal with these kind of hurdles where one of them is here in front of you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s