Application of Oracle virtual index, oracle virtual Index

Application of Oracle virtual index, oracle virtual Index

When performing SQL optimization, you may need to add an index to test whether it can help improve the performance. If the table is very large at this time, it will be very troublesome to create an index, in this scenario, the virtual index should be launched. Here is an experiment:

SQL> select * from v $ version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bi
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production
TNS for 64-bit Windows: Version 10.2.0.1.0-Production
NLSRTL Version 10.2.0.1.0-Production
SQL> drop table test purge;
SQL> create table test as select * from user_objects;

SQL> select count (1 ),
Count (distinct object_name) c_oname,
Count (distinct object_type) s_otype
From test;
COUNT (1) C_ONAME S_OTYPE
------------------------------
11500 11430 14

SQL> create index ind_test_name on test (object_name) nosegment;

SQL> create index ind_test_otype on test (object_type) nosegment;

SQL> exec dbms_stats.gather_table_stats (user, 'test', cascade => true );

SQL> -- you must set the implicit parameter "_ use_nosegment_indexes" = true (the default value is false) before CBO can use virtual indexes.

SQL> alter session set "_ use_nosegment_indexes" = true;

SQL> set autotrace trace exp

SQL> select * from test where object_name = 'test ';

Execution Plan
----------------------------------------------------------
Plan hash value: 3675505035
Bytes ---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ---------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 86 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | TEST | 1 | 86 | 2 (0) | 00:00:01 |
| * 2 | index range scan | IND_TEST_NAME | 1 | 1 (0) | 00:00:01 |
Bytes ---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-access ("OBJECT_NAME" = 'test ')

SQL> select * from test where object_type = 'test ';
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 821 | 70606 | 36 (0) | 00:00:01 |
| * 1 | table access full | TEST | 821 | 70606 | 36 (0) | 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------

1-filter ("OBJECT_TYPE" = 'test ')

SQL> set autotrace off

SQL> select index_name from user_indexes s where s. table_name = 'test ';
Unselected row

SQL> col object_name format a15;
SQL> select object_name, object_id, object_type from user_objects s
Where s. object_name in ('ind _ TEST_NAME ', 'ind _ TEST_OTYPE ');
OBJECT_NAME OBJECT_ID OBJECT_TYPE
--------------------------------------------
IND_TEST_NAME 670666 INDEX
IND_TEST_OTYPE 670667 INDEX

SQL> select segment_name, s. bytes from user_segments s where s. segment_name
In ('ind _ TEST_NAME ', 'ind _ TEST_OTYPE ');
Unselected row

Principle:After a virtual index is added, _ use_nosegment_indexes is enabled at the same time. When CBO evaluates the execution plan, it regards the virtual index as a real index, which is somewhat deceiving CBO. It cannot be found in user_indexes and user_segments, but it can be found in user_objects. It means that the data dictionary is added, and no actual data is generated. Therefore, it should not be able to operate like a real index. Let's try again:

SQL> alter index ind_test_name rebuild;
Alter index ind_test_name rebuild
*
Row 3 has an error:
ORA-08114: cannot change fake Index

Finally:The virtual index is only suitable for Performance Tuning. Remember to delete it after adding it.

How to use oracle indexes after they are created

Creating indexes is not used in SQL statements, but can greatly improve system performance.
First, you can create a unique index to ensure the uniqueness of each row of data in the database table.
Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.
Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity.
Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.
Fifth, by using indexes, you can use the optimizer during the query process to improve system performance.

Oracle index usage is not a concept! Statements to be executed

If your SQL statement is suitable for using this index, oracle's CBO will use the index method when executing the SQL statement. You do not need to restrict it to use by yourself, it is dangerous for oracle to forcibly change the execution plan of oracle. Of course, if you must use it in your SQL statement, add an Hint to the SQL statement. The specific SQL statement is:
Select/* + index (emp non_deptno) */* from emp
It is strongly recommended that you do not write this statement as described above.