虚拟索引virtua indexing

来源：互联网发布：管家婆软件怎样做库存编辑：程序博客网时间：2024/05/29 02:20

虚拟索引定义的索引没有物理上实际创建，虚拟索引可以用在调优中。

1.通过xplan查询执行计划：

SQL> explain plan for
2 select * from sales where cust_id > 100;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'basic +cost'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 781590677

------------------------------------------------
| Id | Operation         | Name | Cost (%CPU)|
------------------------------------------------
|   0 | SELECT STATEMENT |       | 3182   (2)|
|   1 | TABLE ACCESS FULL| SALES | 3182   (2)|
------------------------------------------------

8 rows selected.

2.创建虚拟索引。

SQL> alter session set "_use_nosegment_indexes"=true;

Session altered.

SQL> create index sales_id on sales(cust_id) nosegment;

Index created.

3.查看创建虚拟索引后的执行计划：

SQL> explain plan for
2 select * from sales where cust_id > 100;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'BASIC +COST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2830526242

-------------------------------------------------------------
| Id | Operation                   | Name     | Cost (%CPU)|
-------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     5   (0)|
|   1 | TABLE ACCESS BY INDEX ROWID| SALES    |     5   (0)|
|   2 |   INDEX RANGE SCAN          | SALES_ID |     2   (0)|
-------------------------------------------------------------

9 rows selected.