Shulou(Shulou.com)06/01 Report--

I. introduction

When DBA maintains and manages the database for low-performance SQL analysis, sometimes we need to optimize SQL by creating indexes, but sometimes can we use the indexes we create? The effect can only be seen after it is created, but in practice, indexing on large tables has a great impact on system performance, so we have to avoid business peak hours. but is there a way to create an index without affecting performance? Yes, that's the virtual index.

The virtual index does not exist physically, it does not create an actual index segment, but adds an index record to the data dictionary to make the optimizer aware of the existence of an index. to determine whether to use the index as the access path. The function is only for use when DBA is optimized for SQL, and DBA decides whether to create a physical index based on the optimization effect of the virtual index.

II. Virtual index type

Virtual indexes support B-TREE indexes and BIT bitmap indexes. In CBO mode, the ORACLE optimizer considers virtual indexes, but in RBO mode, you need to add hint.

Third, virtual index creation instance

SQL > create table scott.t as select * from dba_objects

Table created.

SQL > alter session set "_ use_nosegment_indexes" = true

Session altered.

SQL > create index scott.ix_t_id on scott.t (object_id) nosegment

Index created.

SQL > set autot traceonly

SQL > select * from scott.t where object_id=1

No rows selected

Execution Plan

Plan hash value: 206018885

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 14 | 2898 | 5 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | T | 14 | 2898 | 5 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IX_T_ID | 330 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("OBJECT_ID" = 1)

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

4 recursive calls

0 db block gets

1308 consistent gets

1239 physical reads

0 redo size

1343 bytes sent via SQL*Net to client

512 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

The following is the real execution plan, which obviously does not need an index.

SQL > set autot off

SQL > alter session set statistics_level=all

Session altered.

SQL > select * from scott.t where object_id=1

No rows selected

SQL > select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

PLAN_TABLE_OUTPUT

-

SQL_ID 2qhwh0nzrzx2r, child number 1

-

Select * from t where object_id=1

Plan hash value: 1601196873

-

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |

-

| | 0 | SELECT STATEMENT | | 1 | | 0 | 000.52 | 1242 | 1239 |

| | * 1 | TABLE ACCESS FULL | T | 1 | 14 | 0 | 000.52 | 1242 | 1239 |

-

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 1)

Note

-

-dynamic sampling used for this statement (level=2)

22 rows selected.

This index cannot be found from the data field.

SQL > select index_name,status from dba_indexes where table_name='T'

No rows selected

IV. Characteristics of virtual index

4.1. virtual index cannot execute alter index option

SQL > alter index scott.IX_T_ID rebuild

Alter index scott.IX_T_ID rebuild

*

ERROR at line 1:

ORA-08114: can not alter a fake index

4.2.When using the Recycle Bin feature, the virtual index must be drop before an index with the same name can be created.

SQL > drop table scott.t

Table dropped.

SQL > flashback table scott.t to before drop

Flashback complete.

SQL > create index scott.idx_t_id on scott.t (object_id) nosegment

Create index scott.idx_t_id on scott.t (object_id) nosegment

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

4.3. Cannot create an actual index with the same name as the virtual index

4.4. You can create an actual index with the same column but different names as a virtual index

4.5. The virtual index cannot be seen in the data dictionary

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.