📋Steps in this guide1/4

Virtual Indexes

Use virtual indexes to test alternate indexing strategies with no impact on the rest of your system.

oracle miscconfigurationintermediate

by OracleDba

13 views

Overview

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. If we query the table using the primary key, we can see this reflected in the execution plan.

Code/Command (click line numbers to comment):

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);

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>

Section 2

If we query the table using a non-indexed column, we see a full table scan. To create the virtual index on this column, simply add the clause to the statement. If we repeat the previous query we can see the virtual index is not visible to the optimizer.

Code/Command (click line numbers to comment):

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>

SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;

Index Created

SQL>

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>

Section 3

To make the virtual index available we must set the parameter. If we repeat the query we can see that the virtual index is now used. The virtual index does not appear in the view, but it present in the view.

Code/Command (click line numbers to comment):

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered

SQL>

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>

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>

Section 4

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 view. 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. Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'objects_tab_object_name_vi');

PL/SQL procedure successfully completed.

SQL>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!