Introduction to OracleVirtualIndex virtual Index

Adding indexes is a frequently used performance optimization method. When you encounter a problem, try to add an index to see if the execution plan can be changed. This is why we have analyzed and solved the problem.

Adding indexes is a frequently used performance optimization method. When you encounter a problem, try to add an index to see if the execution plan can be changed. This is why we have analyzed and solved the problem.

Traditional performance optimization and adjustment work is mostly carried out by the O & M team after the system is launched. When the amount of data in the system reaches a certain level, some hidden problems will emerge. Therefore, SQL Optimization in big data and emergency scenarios is often a problem that O & M teams often encounter.

Adding indexes is a frequently used performance optimization method. When you encounter a problem, try adding an index to see if the execution plan can be changed. This is the process of analyzing and solving the problem. However, it is difficult to quickly create indexes for large data tables. At this time, we can use the virtual index Technology of Oracle.

1. Environment Introduction and data preparation

Virtual Index appears very early. I can see the technical material of virtual index from 9i documents. We chose Oracle 11gR2 for testing.

SQL> select * from v $ version;

BANNER

----------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

PL/SQL Release 11.2.0.1.0-Production

CORE11.2.0.1.0Production

We create a data table T as the experiment object and create both normal and virtual indexes.

SQL> show user;

User is "scott"

SQL> create table t as select * from dba_objects;

Table created

SQL> set timing on;

-- Create a common index

SQL> create index idx_t_owner on t (owner );

Index created

Executed in0.687seconds

SQL> select count (*) from t;

COUNT (*)

----------

72792

Executed in 0.015 seconds

To create a virtual index, use the nosegment keyword.

SQL> create index idx_t_obj on t (object_id) nosegment;

Index created

Executed in0.047seconds

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

PL/SQL procedure successfully completed

Executed in 1.716 seconds

Here, we need to pay attention to the details, that is, creating indexes on more than 70 thousand basic data. The nosegment virtual index takes a short time.

2. view the virtual index at the data dictionary level

We have created the virtual index idx_t_obj and the idx_t_owner as the reference. Next we can look at the content of the virtual index at the data dictionary level.

All Oracle index information is recorded in the dba_indexes view.

SQL> select index_name, index_type from dba_indexes where wner = 'Scott 'and table_name = 'T ';

INDEX_NAMEINDEX_TYPE

---------------------------------------------------------

IDX_T_OWNERNORMAL

Executed in 0.031 seconds

SQL> select segment_name from dba_segments where wner = 'Scott 'and segment_name in ('idx _ T_OWNER', 'idx _ T_OBJ ');

SEGMENT_NAME

--------------------------------------------------------------------

IDX_T_OWNER

Executed in 0.062 seconds

From dba_indexes and dba_segments, we can only see information about the idx_t_owner of a common index. The created virtual index idx_t_obj has no trace. The nosegment option allows us to guess that there is no index segment object creation process. However, the dba_indexes information used as a dictionary is confusing.

Verify our ideas and use the dbms_metadata.get_ddl method to extract the dictionary definition of data table t. Here, we can see the idx_t_obj information.

Create index "SCOTT". "IDX_T_OBJ" ON "SCOTT". "T" ("OBJECT_ID ")

PCTFREE 10 INITRANS 2 MAXTRANS 255 NOSEGMENT;

Create index "SCOTT". "IDX_T_OWNER" ON "SCOTT". "T" ("OWNER ")

PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 freelist groups 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS ";

Compared with idx_t_owner, the full text of virtual index definition is very simple, and only nosegment is very conspicuous.

What about dba_objects as a summary of all things?

SQL> select owner, object_name, object_id, data_object_id, object_type from dba_objects where object_name in ('idx _ T_OWNER ', 'idx _ T_OBJ ');

OWNER OBJECT_NAMEOBJECT_ID DATA_OBJECT_ID OBJECT_TYPE

---------------------------------------------------------------

SCOTT IDX_T_OWNER7801978019 INDEX

SCOTT IDX_T_OBJ7802078020 INDEX

Executed in 0.047 seconds