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