Virtual indexes for Oracle Performance Optimization

Source: Internet
Author: User

Virtual indexes for Oracle Performance Optimization

A virtual index is a pseudo index defined in the data dictionary, but there is no relevant index segment. The purpose of a virtual index is to simulate the storage of indexes, instead of creating a complete index. This allows developers to create virtual indexes to view the corresponding execution plan, without having to wait until the actual index is created to view the impact of the index on the execution plan, and does not increase the storage space usage. If we observe that the optimizer generates an expensive Execution Plan and the SQL adjustment Guide suggests that we create indexes for certain columns, however, creating indexes and testing in the production database environment is not always feasible. We need to ensure that the created index will not have a negative impact on other queries in the database, so we can use virtual indexes.

The following is an example.
1. Create a test table
SQL> create table test as select * from dba_objects;

Table created.

2. query records whose object_name is equal to standard from the test table
SQL> select * from test where object_name = 'standard ';

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------------------------------------------------------------
CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S
-----------------------------------------------------
SYS
STANDARD
888 PACKAGE
19-APR-10 19-APR-10 2003-04-18: 00: 00: 00 VALID N


OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------------------------------------------------------------
CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S
-----------------------------------------------------
SYS
STANDARD
889 PACKAGE BODY
19-APR-10 19-APR-10 2010-04-19: 10: 22: 58 VALID N

3. query the execution plan queried above
SQL> set autotrace traceonly explain
SQL> select * from test where object_name = 'standard ';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 8 | 1416 | 155 (1) | 00:00:02 |
| * 1 | table access full | TEST | 8 | 1416 | 155 (1) | 00:00:02 |
--------------------------------------------------------------------------

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

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

Note
-----
-Dynamic sampling used for this statement

 

4. Create a virtual index on the object_name column of the test table.
SQL> create index test_index on test (object_name) nosegment;

Index created.


To create a virtual index, you must specify the nosegment clause in the create index statement, and do not create index segments.

5. to verify that the virtual index does not create an index segment
SQL> set autotrace off
SQL> select index_name from dba_indexes where table_name = 'test' and index_name = 'test _ Index ';

No rows selected

SQL> col OBJECT_NAME format a20;
SQL> select object_name, object_type from dba_objects where object_name = 'test _ Index ';

OBJECT_NAME OBJECT_TYPE
---------------------------------------
TEST_INDEX INDEX


From the above results, we can see that the index object has been created, but no index segment has been created.

6. Run the SQL statement again to check whether the created virtual index is used.
SQL> set autotrace traceonly explain
SQL> select * from test where object_name = 'standard ';

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 8 | 1416 | 155 (1) | 00:00:02 |
| * 1 | table access full | TEST | 8 | 1416 | 155 (1) | 00:00:02 |
--------------------------------------------------------------------------

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

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

Note
-----
-Dynamic sampling used for this statement


The preceding Execution Plan clearly shows that the created virtual index is not used.

7. To use the created virtual index, SET _ USE_NOSEGMENT_INDEXES to true.
SQL> alter session set "_ USE_NOSEGMENT_INDEXES" = true;

Session altered.


8. Run the SQL statement again to check whether the created virtual index is used.
SQL> set long 900
SQL> set linesize 900
SQL> select * from test where object_name = 'standard ';

Execution Plan
----------------------------------------------------------
Plan hash value: 2627321457

Bytes ------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ------------------------------------------------------------------------------------------
| 0 | select statement | 8 | 1416 | 5 (0) | 00:00:01 |
| 1 | table access by index rowid | TEST | 8 | 1416 | 5 (0) | 00:00:01 |
| * 2 | index range scan | TEST_INDEX | 238 | 1 (0) | 00:00:01 |
Bytes ------------------------------------------------------------------------------------------

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

2-access ("OBJECT_NAME" = 'standard ')

Note
-----
-Dynamic sampling used for this statement


From the preceding execution plan, we can see that when the implicit parameter _ USE_NOSEGMENT_INDEXES is set, the optimizer will use the created virtual index. Note that when using a virtual index, we can analyze the virtual index, but we cannot recreate the virtual index. If you recreate the virtual index, we will receive the ORA-8114: "User attempted to alter a fake index" error message, you can delete virtual indexes.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.