虚拟索引virtua indexing

最新推荐文章于 2025-02-26 20:28:16 发布

bamuta

最新推荐文章于 2025-02-26 20:28:16 发布

阅读量931

点赞数

分类专栏： Oracle Oracle 优化

本文链接：https://blog.youkuaiyun.com/bamuta/article/details/8746084

版权

Oracle 同时被 2 个专栏收录

173 篇文章

订阅专栏

Oracle 优化

13 篇文章

订阅专栏

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

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.