Oracle性能优化之虚拟索引

虚拟索引是定义在数据字典中的伪索引，但没有相关的索引段。虚拟索引的目的是模拟索引的存--而不用真实的创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响，并且不会增加存储空间的使用。如果我们观察到优化器生成了一个昂贵的执行计划并且SQL调整指导建议我们对某些的某列创建索引，但在生产数据库环境中创建索引与测试并不总是可以操作。我们需要确保创建的索引将不会对数据库中的其它查询产生负面影响，因此可以使用虚拟索引。

10年积累的成都网站制作、成都网站设计经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先制作网站后付款的网站建设流程，更有灯塔免费网站建设让你可以放心的选择与我们合作。

下面举例进行说明:

1.创建一个测试表test

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

2.从表test查询object_name等于standard的记录

 
 
 
    
  
  
  
  1. SQL> select * from test where object_name='STANDARD'; 
    2.   
  
  
  2.  
    3.   
  
  
  3. OWNER 
    4.   
  
  
  4.  
    5.   
  
  
  5. ------------------------------ 
    6.   
  
  
  6.  
    7.   
  
  
  7. OBJECT_NAME 
    8.   
  
  
  8.  
    9.   
  
  
  9. -------------------------------------------------------------------- 
    10.   
  
  
  10.  
    11.   
  
  
  11. SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE 
    12.   
  
  
  12.  
    13.   
  
  
  13. ------------------------------ ---------- -------------- ----------- 
    14.   
  
  
  14.  
    15.   
  
  
  15. CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S 
    16.   
  
  
  16.  
    17.   
  
  
  17. ------------ ------------ ------------------- ------- - - - 
    18.   
  
  
  18.  
    19.   
  
  
  19. SYS 
    20.   
  
  
  20.  
    21.   
  
  
  21. STANDARD 
    22.   
  
  
  22.  
    23.   
  
  
  23. 888 PACKAGE 
    24.   
  
  
  24.  
    25.   
  
  
  25. 19-APR-10 19-APR-10 2003-04-18:00:00:00 VALID N N N 
    26.   
  
  
  26.  
    27.   
  
  
  27. OWNER 
    28.   
  
  
  28.  
    29.   
  
  
  29. ------------------------------ 
    30.   
  
  
  30.  
    31.   
  
  
  31. OBJECT_NAME 
    32.   
  
  
  32.  
    33.   
  
  
  33. -------------------------------------------------------------------- 
    34.   
  
  
  34.  
    35.   
  
  
  35. SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE 
    36.   
  
  
  36.  
    37.   
  
  
  37. ------------------------------ ---------- -------------- ------------------- 
    38.   
  
  
  38.  
    39.   
  
  
  39. CREATED LAST_DDL_TIM TIMESTAMP STATUS T G S 
    40.   
  
  
  40.  
    41.   
  
  
  41. ------------ ------------ ------------------- ------- - - - 
    42.   
  
  
  42.  
    43.   
  
  
  43. SYS 
    44.   
  
  
  44.  
    45.   
  
  
  45. STANDARD 
    46.   
  
  
  46.  
    47.   
  
  
  47. 889 PACKAGE BODY 
    48.   
  
  
  48.  
    49.   
  
  
  49. 19-APR-10 19-APR-10 2010-04-19:10:22:58 VALID N N N 
    50.

3.查询上面查询的执行计划

 
 
 
    
  
  
  
  1. SQL> set autotrace traceonly explain 
    2.   
  
  
  2.  
    3.   
  
  
  3. SQL> select * from test where object_name='STANDARD'; 
    4.   
  
  
  4.  
    5.   
  
  
  5. Execution Plan 
    6.   
  
  
  6.  
    7.   
  
  
  7. ---------------------------------------------------------- 
    8.   
  
  
  8.  
    9.   
  
  
  9. Plan hash value: 1357081020 
    10.   
  
  
  10.  
    11.   
  
  
  11. -------------------------------------------------------------------------- 
    12.   
  
  
  12.  
    13.   
  
  
  13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
    14.   
  
  
  14.  
    15.   
  
  
  15. -------------------------------------------------------------------------- 
    16.   
  
  
  16.  
    17.   
  
  
  17. | 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | 
    18.   
  
  
  18.  
    19.   
  
  
  19. |* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | 
    20.   
  
  
  20.  
    21.   
  
  
  21. -------------------------------------------------------------------------- 
    22.   
  
  
  22.  
    23.   
  
  
  23. Predicate Information (identified by operation id): 
    24.   
  
  
  24.  
    25.   
  
  
  25. --------------------------------------------------- 
    26.   
  
  
  26.  
    27.   
  
  
  27. 1 - filter("OBJECT_NAME"='STANDARD') 
    28.   
  
  
  28.  
    29.   
  
  
  29. Note 
    30.   
  
  
  30.  
    31.   
  
  
  31. ----- 
    32.   
  
  
  32.  
    33.   
  
  
  33. - dynamic sampling used for this statement 
    34.

4.在表test的object_name列上创建一个虚拟索引

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

为了创建虚拟索引必须在create index语句中指定nosegment子句，并且不会创建索引段。

5.来验证虚拟索引不会创建索引段

 
 
 
    
  
  
  
  1. SQL> set autotrace off 
    2.   
  
  
  2.  
    3.   
  
  
  3. SQL> select index_name from dba_indexes where table_name = 'TEST' and index_name = 'TEST_INDEX'; 
    4.   
  
  
  4.  
    5.   
  
  
  5. no rows selected 
    6.   
  
  
  6.  
    7.   
  
  
  7. SQL> col OBJECT_NAME format a20; 
    8.   
  
  
  8.  
    9.   
  
  
  9. SQL> select object_name, object_type from dba_objects where object_name = 'TEST_INDEX'; 
    10.   
  
  
  10.  
    11.   
  
  
  11. OBJECT_NAME OBJECT_TYPE 
    12.   
  
  
  12.  
    13.   
  
  
  13. -------------------- ------------------- 
    14.   
  
  
  14.  
    15.   
  
  
  15. TEST_INDEX INDEX 
    16.

从上面的结果可以看到索引对象已经创建，但没有创建索引段。

6.重新执行sql查看创建的虚拟索引是否被使用

 
 
 
    
  
  
  
  1. SQL> set autotrace traceonly explainSQL> select * from test where object_name='STANDARD'; 
    2.   
  
  
  2.  
    3.   
  
  
  3. Execution Plan 
    4.   
  
  
  4.  
    5.   
  
  
  5. ---------------------------------------------------------- 
    6.   
  
  
  6.  
    7.   
  
  
  7. Plan hash value: 1357081020 
    8.   
  
  
  8.  
    9.   
  
  
  9. -------------------------------------------------------------------- 
    10.   
  
  
  10.  
    11.   
  
  
  11. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
    12.   
  
  
  12.  
    13.   
  
  
  13. -------------------------------------------------------------------- 
    14.   
  
  
  14.  
    15.   
  
  
  15. | 0 | SELECT STATEMENT | | 8 | 1416 | 155 (1)| 00:00:02 | 
    16.   
  
  
  16.  
    17.   
  
  
  17. |* 1 | TABLE ACCESS FULL| TEST | 8 | 1416 | 155 (1)| 00:00:02 | 
    18.   
  
  
  18.  
    19.   
  
  
  19. -------------------------------------------------------------------- 
    20.   
  
  
  20.  
    21.   
  
  
  21. Predicate Information (identified by operation id): 
    22.   
  
  
  22.  
    23.   
  
  
  23. --------------------------------------------------- 
    24.   
  
  
  24.  
    25.   
  
  
  25. 1 - filter("OBJECT_NAME"='STANDARD') 
    26.   
  
  
  26.  
    27.   
  
  
  27. Note 
    28.   
  
  
  28.  
    29.   
  
  
  29. ----- 
    30.   
  
  
  30.  
    31.   
  
  
  31. - dynamic sampling used for this statement 
    32.

从上面的执行计划可以清楚地看到创建的虚拟索引并没有被使用。

7.为了能使用所创建的虚拟索引，需要将_USE_NOSEGMENT_INDEXES设置为true

 
 
 
    
  
  
  
  1. SQL> alter session set "_USE_NOSEGMENT_INDEXES" = true;Session altered. 
    2.

8.重新执行sql查看创建的虚拟索引是否被使用

 
 
 
    
  
  
  
  1. SQL> set long 900SQL> set linesize 900 
    2.   
  
  
  2.  
    3.   
  
  
  3. SQL> select * from test where object_name='STANDARD'; 
    4.   
  
  
  4.  
    5.   
  
  
  5. Execution Plan 
    6.   
  
  
  6.  
    7.   
  
  
  7. ---------------------------------------------------------- 
    8.   
  
  
  8.  
    9.   
  
  
  9. Plan hash value: 2627321457 
    10.   
  
  
  10.  
    11.   
  
  
  11. -------------------------------------------------------------------- 
    12.   
  
  
  12.  
    13.   
  
  
  13. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 
    14.   
  
  
  14.  
    15.   
  
  
  15. -------------------------------------------------------------------- 
    16.   
  
  
  16.  
    17.   
  
  
  17. | 0 | SELECT STATEMENT | | 8 | 1416 | 5 (0)| 00:00:01 | 
    18.   
  
  
  18.  
    19.   
  
  
  19. | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8 | 1416 | 5 (0)| 00:00:01 | 
    20.   
  
  
  20.  
    21.   
  
  
  21. |* 2 | INDEX RANGE SCAN | TEST_INDEX | 238 | | 1 (0)| 00:00:01 | 
    22.   
  
  
  22.  
    23.   
  
  
  23. -------------------------------------------------------------------- 
    24.   
  
  
  24.  
    25.   
  
  
  25. Predicate Information (identified by operation id): 
    26.   
  
  
  26.  
    27.   
  
  
  27. --------------------------------------------------- 
    28.   
  
  
  28.  
    29.   
  
  
  29. 2 - access("OBJECT_NAME"='STANDARD') 
    30.   
  
  
  30.  
    31.   
  
  
  31. Note 
    32.   
  
  
  32.  
    33.   
  
  
  33. ----- 
    34.   
  
  
  34.  
    35.   
  
  
  35. - dynamic sampling used for this statement 
    36.

从上面的执行计划可以看到当设置隐含参数_USE_NOSEGMENT_INDEXES后，优化器将会使用创建的虚拟索引。在使用虚拟索引需要注意，我们可以分析虚拟索引，但不能重建虚拟索引，如果重建虚拟索引会收到ORA-8114: "User attempted to alter a fake index"错误提示，可以删除虚拟索引。

网页名称：Oracle性能优化之虚拟索引
分享路径：http://www.gawzjz.com/qtweb/news17/188567.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

用户体验知识

同城分类信息

乐山服务器托管　　 　四川平武网站建设　　 　重庆网站建设网络公司　　 　乐山主机托管　　 　成都艾瑞格　　 　成都虚拟主机　　 　成都网站设计　　 　成都网站推广　　 　成都网站建设　　 　成都品牌官网设计公司　　 　成都app制作　　 　成都木托盘　　 　梦港之夜　　 　全网品牌营销　　 　简阳服务器托管　　 　蜜朵婚庆　　 　产品包装设计　　 　和县翔豪网站　　 　鸿艺文化　　 　达州服务器托管