While adding some indexes to production table to see some performance improvements; Virtual Index creation is very handy. It doesn’t occupy spaces and you could compare query plan to see if that going to help query performance. Here is a demonstration about how that can be achieved.
Create a sample table where we’ll test Virtual Index impact.
SQL> CREATE TABLE objects_tab AS SELECT * FROM all_objects;
Table created.
SQL> ALTER TABLE objects_tab ADD (
CONSTRAINT objects_tab_pk PRIMARY KEY (object_id)
); 2 3
Table altered.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'objects_tab', cascade=>TRUE);
PL/SQL procedure successfully completed.
If we query the table using the primary key, we can see this reflected in the execution plan.
SQL> SELECT * FROM objects_tab WHERE object_id = 10;
Execution Plan
----------------------------------------------------------
Plan hash value: 2097082964
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | OBJECTS_TAB_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10)
Now If we query the table using a non-indexed column, we see a full table scan.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Execution Plan
----------------------------------------------------------
Plan hash value: 821620785
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 163 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 2 | 196 | 163 (1)| 00:00:03 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='USER_TABLES')
To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.
SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;
Index created.
If we repeat the previous query we can see the virtual index is not visible to the optimizer.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Execution Plan
----------------------------------------------------------
Plan hash value: 821620785
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 163 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 2 | 196 | 163 (1)| 00:00:03 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='USER_TABLES')
*** NO REFLECTION
To make the virtual index available we must set the _use_nosegment_indexes parameter.
SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;
Session altered.
If we repeat the query we can see that the virtual index is now used.
SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';
Execution Plan
----------------------------------------------------------
Plan hash value: 4006507992
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 196 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 2 | 196 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_TAB_OBJECT_NAME_VI | 2 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='USER_TABLES')
The virtual index does not appear in the USER_INDEXES view, but it present in the USER_OBJECTS view.
SQL> select index_name from user_indexes where index_name = 'OBJECTS_TAB_OBJECT_NAME_VI';
no rows selected
SQL> SELECT object_name FROM user_objects WHERE object_name = 'OBJECTS_TAB_OBJECT_NAME_VI';
OBJECT_NAME
----------------------------------------------------------------OBJECTS_TAB_OBJECT_NAME_VI
Statistics can be gathered on virtual indexes in the same way as regular indexes, but as we have seen previously, there will be no record of this in the USER_INDEXES view.
SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'objects_tab_object_name_vi');
PL/SQL procedure successfully completed.
Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.
CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
*
ERROR at line 1:
ORA-01408: such column list already indexed
SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);
Index created.
