Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about how to implement virtual index in Oracle. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Create a test table test

SQL > create table test as select * from dba_objects; Table created.

two。 Query records with object_name equals standard from table test

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 VALID N N 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 PACKAGE BODY 19-APR-10 19-APR-10 10 22 VALID N N N

3. Query the execution plan of the above query

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,155 (1) | 00:00:02 | | * 1 | TABLE ACCESS FULL | TEST | 8 | 1416 | 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 table test

SQL > create index test_index on test (object_name) nosegment;Index created.

In order to create a virtual index, the nosegment clause must be specified in the create index statement, and no index segment is created.

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, you can see that the index object has been created, but no index segment has been created.

6. Re-execute sql to see if the virtual index created is in use

SQL > set autotrace traceonly explainSQL > 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 | 00:00:02 |-Predicate Information (identified by operation id):- -1-filter ("OBJECT_NAME" = 'STANDARD') Note-- dynamic sampling used for this statement

It is clear from the above execution plan that the virtual index created is not used.

7. In order to use the virtual index created, you need to set _ USE_NOSEGMENT_INDEXES to true

SQL > alter session set "_ USE_NOSEGMENT_INDEXES" = true;Session altered.

8. Re-execute sql to see if the virtual index created is in use

SQL > set long 900SQL > set linesize 900SQL > select * from test where object_name='STANDARD' Execution Plan-Plan hash value: 2627321457- | | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |-| 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 |- -Predicate Information (identified by operation id):-2-access ("OBJECT_NAME" = 'STANDARD') Note-- dynamic sampling used for this statement read the above Have you mastered how to implement virtual index in Oracle? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.