Oracle SQL optimization: test the impact of creating indexes in the production environment on database performance using virtual Indexes

A virtual index is a "false" index, which is defined in a data dictionary, but does not have the corresponding index segment, that is, it does not allocate any storage space. Using Virtual indexes, developers
You do not need to wait for the index to be created, or you do not need additional index storage space, you can use it as an index that already exists and test the execution plan of the SQL statement. If the optimizer is
The execution plan for SQL statement creation is very expensive. SQL Tuning Advisor may recommend creating indexes on a column, but in the production environment, we cannot arbitrarily
To create indexes and test these changes. We need to ensure that the index to be created does not affect the execution plan of other queries running in the database. When a virtual index appears
To solve this problem:

The following is a test to introduce the usage of virtual indexes.

1) create a sample table

SQL> Create Table Test as select * From dba_objects;

2) execute any query on the table

16:43:55 system @ prod> select * from test where object_name = 'emp ';

Owner object_name subobject_name object_id data_object_id object_type created last_ddl_time timestamp status T g S namespace edition_name
Certificate -------------------- certificate ---------- -------------- --------------------- ----------------- -------------------------------- Certificate --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Scott EMP 75315 75315 table 18:03:42 2013-03-10 17:07:42: 18: 03: 42 valid N 1

3) view the execution plan of the preceding Query

16:44:31 system @ prod> set autotrace traceonly explain
16:44:42 system @ prod> select * from test where object_name = 'emp ';
Elapsed: 00:00:00. 01

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

--------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------
| 0 | SELECT statement | 12 | 2484 | 293 (1) | 00:00:04 |
| * 1 | table access full | test | 12 | 2484 | 293 (1) | 00:00:04 |
--------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

1-filter ("object_name" = 'emp ')

Note
-----
-Dynamic sampling used for this statement (Level = 2)

4) create a virtual index on the object_name field of the test table.

16:45:44 system @ prod> Create index test_index on test (object_name) nosegment;

Index created.

Note: When creating a virtual index, you must specify the nosegment clause in the create index statement. After the preceding statement is executed
No index segment is created, that is, no storage space is allocated to the test_index object. We can verify this by following the steps below.

5) You can view the created test_index object through dba_objects.

16:46:02 system @ prod> set autotrace off

16:50:16 system @ prod> Col object_name for A20;
16:50:26 system @ prod> select object_name, object_type from dba_objects where object_name = 'test _ Index ';

Object_name object_type
---------------------------------------
Test_index Index

However, we cannot view this object through dba_indexes, dba_segments, and dba_extents.

16:53:06 system @ prod> select index_name, index_type, table_name from dba_indexes where index_name = 'test _ Index ';

No rows selected

16:55:50 system @ prod> select segment_name, segment_type, tablespace_name from dba_segments where segment_name = 'test _ Index ';

No rows selected

16:56:46 system @ prod> select segment_name, segment_type, tablespace_name from dba_extents where segment_name = 'test _ Index ';

No rows selected

The above query shows that this object is created in the database, but no corresponding segment is created to allocate storage space.

6) Check the execution plan of the preceding SQL statement again to see if the created virtual index is used.

16:57:11 system @ prod> set autotrace traceonly explain
16:58:47 system @ prod> select * from test where object_name = 'emp ';
Elapsed: 00:00:00. 04

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

--------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
--------------------------------------------------------------------------
| 0 | SELECT statement | 12 | 2484 | 293 (1) | 00:00:04 |
| * 1 | table access full | test | 12 | 2484 | 293 (1) | 00:00:04 |
--------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

1-filter ("object_name" = 'emp ')

Note
-----
-Dynamic sampling used for this statement (Level = 2)

-- We can see that the execution plan has not changed after the virtual index is created.

7) We need to modify the implicit parameter _ use_nosegment_indexes of the database to force the session to use virtual indexes.

17:01:24 system @ prod> alter session set "_ use_nosegment_indexes" = true;

Session altered.

8) view the execution plan again

17:02:06 system @ prod> select * from test where object_name = 'emp ';
Elapsed: 00:00:00. 02

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

Bytes ------------------------------------------------------------------------------------------
| ID | operation | Name | rows | bytes | cost (% CPU) | time |
Bytes ------------------------------------------------------------------------------------------
| 0 | SELECT statement | 12 | 2484 | 5 (0) | 00:00:01 |
| 1 | table access by index rowid | test | 12 | 2484 | 5 (0) | 00:00:01 |
| * 2 | index range scan | test_index | 309 | 1 (0) | 00:00:01 |
Bytes ------------------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

2-access ("object_name" = 'emp ')

Note
-----
-Dynamic sampling used for this statement (Level = 2)
Bytes -----------------------------------------------------------------------------------------

After the implicit parameter _ use_nosegment_indexes is set, the optimizer uses the virtual index created on this table. This query is not used when it is run in other sessions.
This virtual index, because we only modified the implicit parameters at the session level.

Notes for using virtual indexes:

1. You can perform the analyze operation on the virtual index.

17:07:13 system @ prod> analyze index test_index compute statistics;

Index analyzed.

2. A rebuild operation cannot be performed on the virtual index, otherwise the ORA-8114: "user attempted to alter a fake Index" error will be reported

17:07:52 system @ prod> alter index test_index rebuild;
Alter index test_index rebuild
*
Error at line 1:
ORA-08114: Can not alter a fake Index

3. Virtual indexes can be deleted as normal indexes do.

17:19:20 system @ prod> drop index test_index;

Index dropped.

4. When dbms_metadata.get_ddl is used in Oracle 9.2 to 11.1 to obtain the DDL script of the virtual index, the nosegment clause of the virtual index is not output.
In my personal testing environment, 11.2.0.3.5 can output the nosegment clause.

17:13:46 system @ prod> select dbms_metadata.get_ddl ('index', 'test _ Index', 'system') DDL from dual;

DDL
--------------------------------------------------------------------------------

Create Index "system". "test_index" on "system". "test" ("O
Bject_name ")

Pctfree 10 initrans 2 maxtrans 255 nosegment

For reprint, please indicate the author's source and original article link:

Http://blog.csdn.net/xiangsir/article/details/8693814