Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Virtual Indexes

The process of tuning SQL statements often requires the testing of alternate indexing strategies to see the affect on execution plans. Creating additional indexes is not without its problems. Adding extra indexes to large tables can take a considerable amount of time and disk space. The additional indexes are available for use by other sessions, which may affect the performance of other parts of your application that you are not currently testing. This can be problematic when you are trying to identify problems on a production system.

In contrast to conventional indexes, a virtual index has no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn't affect the normal running of your system. This article presents a simple example of how virtual indexes are used.

First, we create and populate a table.

CREATE TABLE objects_tab AS SELECT * FROM all_objects;

ALTER TABLE objects_tab ADD (
  CONSTRAINT objects_tab_pk PRIMARY KEY (object_id)
);
  
EXEC DBMS_STATS.gather_table_stats(USER, 'objects_tab', cascade=>TRUE);

If we query the table using the primary key, we can see this reflected in the execution plan.

SQL> SET AUTOTRACE TRACEONLY EXP
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 |    92 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB    |     1 |    92 |     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)

SQL>

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 |   184 |   207   (5)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| OBJECTS_TAB |     2 |   184 |   207   (5)| 00:00:03 |
---------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='USER_TABLES')

SQL>

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

SQL>

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 |   184 |   207   (5)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| OBJECTS_TAB |     2 |   184 |   207   (5)| 00:00:03 |
---------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME"='USER_TABLES')

SQL>

To make the virtual index available we must set the _use_nosegment_indexes parameter.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered

SQL>

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 |   184 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB                |     2 |   184 |     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')

SQL>

The virtual index does not appear in the USER_INDEXES view, but it present in the USER_OBJECTS view.

SQL> SET AUTOTRACE OFF
SQL> SELECT index_name FROM user_indexes;

INDEX_NAME
------------------------------
OBJECTS_TAB_PK

1 row selected.

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX';

OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECTS_TAB_PK
OBJECTS_TAB_OBJECT_NAME_VI

2 rows selected.

SQL>

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.

SQL>

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.

SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;
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.

SQL>

Hope this helps. Regards Tim...

Back to the Top.