当前位置: 开发笔记 > 数据库 > 正文

Oraclesql调优：使用虚拟索引在生产环境测试创建索引对数据库

作者：艾琳冰_699 | 来源：互联网 | 2017-05-12 15:45

虚拟索引是一种“假”索引，其定义存在于数据字典中，但不具有相应的索引段，也就是不会分配任何存储空间。利用虚拟索引，开发人员可以无需等待索引创建完成，也不需要额外的索引存储空间，就可以当做索引已经存在，累测试SQL语句的执行计划。如果优化器

虚拟索引是一种“假”索引，其定义存在于数据字典中，但不具有相应的索引段，也就是不会分配任何存储空间。利用虚拟索引，开发人员
可以无需等待索引创建完成，也不需要额外的索引存储空间，就可以当做索引已经存在，累测试 SQL 语句的执行计划。如果优化器为某个
SQL 语句创建的执行计划代价很高，SQL tuning advisor 可能会建议在某个列上创建索引，但是在生产环境下，我们是没法随意
来创建索引和测试这些更改的。我们需要确保要创建的索引不会对数据库中运行的其他查询的执行计划产生任何影响。虚拟索引的出现就
是为了解决这个问题的：

下面我们来做一个测试来介绍虚拟索引的用法

1) 创建示例表

SQL> create table test as select * from dba_objects;

2) 对该表执行任意的查询

16:43:55 system@PROD> select * from test where object_name = 'EMP';

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME
------------------------------ -------------------- ------------------------------ ---------- -------------- ------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------------------------
SCOTT EMP 75315 75315 TABLE 2011-09-18 18:03:42 2013-03-10 17:07:42 2011-09-18:18:03:42 VALID N N N 1

3) 查看上述查询的执行计划

16:44:31 system@PROD> set autotrace traceonly explain
16:44:42 system@PROD> select * from test where object_name = 'EMP';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 293 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 12 | 2484 | 293 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='EMP')

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

4) 在 test 表的 object_name 字段上面创建虚拟索引

16:45:44 system@PROD> create index test_index on test(object_name) nosegment;

Index created.

注意，在创建虚拟索引时需要在 CREATE INDEX 语句中指定 nosegment 子句，执行上述语句后，实际上数据库中
并未创建索引段，也就是并未给 test_index 对象分配存储空间，这点我们可以通过下面步骤来验证。

5) 通过 dba_objects 可以查看到刚刚创建的 test_index 对象

16:46:02 system@PROD> set autotrace off

16:50:16 system@PROD> col object_name for a20;
16:50:26 system@PROD> select object_name,object_type from dba_objects where object_name = 'TEST_INDEX';

OBJECT_NAME OBJECT_TYPE
-------------------- -------------------
TEST_INDEX INDEX

但是通过 dba_indexes、dba_segments 和 dba_extents 我们查看不到该对象

16:53:06 system@PROD> select index_name,index_type,table_name from dba_indexes where index_name = 'TEST_INDEX';

no rows selected

16:55:50 system@PROD> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where segment_name = 'TEST_INDEX';

no rows selected

16:56:46 system@PROD> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_extents where segment_name = 'TEST_INDEX';

no rows selected

通过上述的查询可以看出，数据库中创建了该对象，但未创建相应的 segment ，分配存储空间。

6) 再次查看之前 sql 的执行计划，看看是否使用了刚刚创建的虚拟索引

16:57:11 system@PROD> set autotrace traceonly explain
16:58:47 system@PROD> select * from test where object_name = 'EMP';
Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 293 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 12 | 2484 | 293 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_NAME"='EMP')

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

--我们看到创建虚拟索引后，执行计划并未改变

7) 我们需要修改数据库的隐含参数 _USE_NOSEGMENT_INDEXES 来强制session使用虚拟索引

17:01:24 system@PROD> alter session set "_USE_NOSEGMENT_INDEXES"=true;

Session altered.

8) 再次查看执行计划

17:02:06 system@PROD> select * from test where object_name = 'EMP';
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2627321457

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 2484 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 12 | 2484 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_INDEX | 309 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_NAME"='EMP')

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

设置 _USE_NOSEGMENT_INDEXES 隐含参数后，优化器将使用在此表上创建的虚拟索引。在其他session中运行该查询时，不会使用
此虚拟索引，因为我们只是修改了 session 级别的隐含参数。

虚拟索引使用的注意事项：

1、可以对虚拟索引执行 analyze 操作

17:07:13 system@PROD> analyze index TEST_INDEX compute statistics;

Index analyzed.

2、无法对虚拟索引执行 rebuild 操作，否则会报 ORA-8114: "User attempted to alter a fake index" 错误

17:07:52 system@PROD> alter index TEST_INDEX rebuild;
alter index TEST_INDEX rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

3、可以像普通索引那样删除虚拟索引

17:19:20 system@PROD> drop index test_index;

Index dropped.

4、在Oracle 9.2 to 11.1 中利用 DBMS_METADATA.get_ddl 来获取虚拟索引的 DDL 脚本时，不会输出虚拟索引的 nosegment 子句
我个人测试的环境是 11.2.0.3.5 可以输出 nosegment 子句

17:13:46 system@PROD> select dbms_metadata.get_ddl('INDEX','TEST_INDEX','SYSTEM') DDL from dual;

DDL
--------------------------------------------------------------------------------

CREATE INDEX "SYSTEM"."TEST_INDEX" ON "SYSTEM"."TEST" ("O
BJECT_NAME")

PCTFREE 10 INITRANS 2 MAXTRANS 255 NOSEGMENT

http://blog.csdn.net/xiangsir/article/details/8693814

推荐阅读

sql
如何实现织梦DedeCms全站伪静态

本文介绍了如何通过修改织梦DedeCms源代码来实现全站伪静态，以提高管理和SEO效果。全站伪静态可以避免重复URL的问题，同时通过使用mod_rewrite伪静态模块和.htaccess正则表达式，可以更好地适应搜索引擎的需求。文章还提到了一些相关的技术和工具，如Ubuntu、qt编程、tomcat端口、爬虫、php request根目录等。 ... [详细]

蜡笔小新 2023-12-14 19:45:47
sql
SQL查询分组后每组行数的统计方法

本文介绍了在SQL中查询分组后每组行数的统计方法。通过使用count()函数和GROUP BY子句可以统计每组的行数，但是如何统计所有组的行数呢？本文提供了一种实现方法，并给出了相应的SQL查询语句。 ... [详细]

蜡笔小新 2023-12-14 19:37:41
sql
mysql分组排序_在MySQL中实现分组排序功能

在数据分析工作中，我们通常会遇到这样的问题，一个业务部门由若干业务组构成，需要筛选出每个业务组里业绩前N名的业务员。这其实是一个分组排序的 ... [详细]

蜡笔小新 2023-12-14 14:41:26
sql
如何限制php数据库链接数和连接超时时间？

本文介绍了如何使用php限制数据库插入的条数并显示每次插入数据库之间的数据数目，以及避免重复提交的方法。同时还介绍了如何限制某一个数据库用户的并发连接数，以及设置数据库的连接数和连接超时时间的方法。最后提供了一些关于浏览器在线用户数和数据库连接数量比例的参考值。 ... [详细]

蜡笔小新 2023-12-14 14:06:10
database
Oracle Database 10g许可授予信息及高级功能详解

本文介绍了Oracle Database 10g许可授予信息及其中的高级功能，包括数据库优化数据包、SQL访问指导、SQL优化指导、SQL优化集和重组对象。同时提供了详细说明，指导用户在Oracle Database 10g中如何使用这些功能。 ... [详细]

蜡笔小新 2023-12-14 13:12:10
sql
adg架构设置及其在企业数据治理中的应用

本文介绍了adg架构设置在企业数据治理中的应用。随着信息技术的发展，企业IT系统的快速发展使得数据成为企业业务增长的新动力，但同时也带来了数据冗余、数据难发现、效率低下、资源消耗等问题。本文讨论了企业面临的几类尖锐问题，并提出了解决方案，包括确保库表结构与系统测试版本一致、避免数据冗余、快速定位问题等。此外，本文还探讨了adg架构在大版本升级、上云服务和微服务治理方面的应用。通过本文的介绍，读者可以了解到adg架构设置的重要性及其在企业数据治理中的应用。 ... [详细]

蜡笔小新 2023-12-14 13:05:22
sql
Hibernate基础映射

在说Hibernate映射前，我们先来了解下对象关系映射ORM。ORM的实现思想就是将关系数据库中表的数据映射成对象，以对象的形式展现。这样开发人员就可以把对数据库的操作转化为对 ... [详细]

蜡笔小新 2023-12-14 10:57:47
sql
postman测试登录后的接口_使用postman进行接口测试的方法(测试用户管理模块)

本文介绍了使用postman进行接口测试的方法，以测试用户管理模块为例。首先需要下载并安装postman，然后创建基本的请求并填写用户名密码进行登录测试。接下来可以进行用户查询和新增的测试。在新增时，可以进行异常测试，包括用户名超长和输入特殊字符的情况。通过测试发现后台没有对参数长度和特殊字符进行检查和过滤。 ... [详细]

蜡笔小新 2023-12-14 10:29:45
database
MysqlDump_mysqldump全库备份相关知识详解

本文详细介绍了MysqlDump和mysqldump进行全库备份的相关知识，包括备份命令的使用方法、my.cnf配置文件的设置、binlog日志的位置指定、增量恢复的方式以及适用于innodb引擎和myisam引擎的备份方法。对于需要进行数据库备份的用户来说，本文提供了一些有价值的参考内容。 ... [详细]

蜡笔小新 2023-12-14 10:03:27
database
使用 Ubuntu 中的 Python 获取浏览器历史记录

使用Ubuntu中的Python获取浏览器历史记录原文: ... [详细]

蜡笔小新 2023-12-14 08:57:59
insert
PHP中的MySQL函数库及其常用函数介绍

本文由编程笔记小编整理，介绍了PHP中的MySQL函数库及其常用函数，包括mysql_connect、mysql_error、mysql_select_db、mysql_query、mysql_affected_row、mysql_close等。希望对读者有一定的参考价值。 ... [详细]

蜡笔小新 2023-12-14 08:19:53
database
Oracle中tnsnames.ora的作用和配置方法

本文介绍了Oracle数据库中tnsnames.ora文件的作用和配置方法。tnsnames.ora文件在数据库启动过程中会被读取，用于解析LOCAL_LISTENER，并且与侦听无关。文章还提供了配置LOCAL_LISTENER和1522端口的示例，并展示了listener.ora文件的内容。 ... [详细]

蜡笔小新 2023-12-14 07:44:06
sql
Spring特性实现接口多类的动态调用详解

本文详细介绍了如何使用Spring特性实现接口多类的动态调用。通过对Spring IoC容器的基础类BeanFactory和ApplicationContext的介绍，以及getBeansOfType方法的应用，解决了在实际工作中遇到的接口及多个实现类的问题。同时，文章还提到了SPI使用的不便之处，并介绍了借助ApplicationContext实现需求的方法。阅读本文，你将了解到Spring特性的实现原理和实际应用方式。 ... [详细]

蜡笔小新 2023-12-14 03:24:19
insert
Java String与StringBuffer的区别及其应用场景

本文主要介绍了Java中String和StringBuffer的区别，String是不可变的，而StringBuffer是可变的。StringBuffer在进行字符串处理时不生成新的对象，内存使用上要优于String类。因此，在需要频繁对字符串进行修改的情况下，使用StringBuffer更加适合。同时，文章还介绍了String和StringBuffer的应用场景。 ... [详细]

蜡笔小新 2023-12-13 19:21:06
sql
Oracle分析函数first_value()和last_value()的用法及原理

本文介绍了Oracle分析函数first_value()和last_value()的用法和原理，以及在查询销售记录日期和部门中的应用。通过示例和解释，详细说明了first_value()和last_value()的功能和不同之处。同时，对于last_value()的结果出现不一样的情况进行了解释，并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。 ... [详细]

蜡笔小新 2023-12-13 19:07:23

艾琳冰_699

这个家伙很懒，什么也没留下！

Tags | 热门标签

RankList | 热门文章