oracle中复合索引的创建剖析—包含in的三个条件SQL语句复合索引的创建

之前文章中提过复合索引的创建思路：
1 前导列尽可能让更多的核心业务SQL能够使用
2 单个SQL语句索引的前导列尽量选择等值条件做为索引的前导列

这里我们如果在对in的谓词、三个条件的SQL语句复合索引的创建做一些更深入的分析，详细的例子如下：

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> create table t09 as select * from dba_objects;

Table created.

SQL> create index ind_owner_type_objid on t09(owner,object_type,object_id);

Index created.

SQL> create index ind_type_owner_objid on t09(object_type,owner,object_id);

Index created.

SQL> set autotrace traceonly;
SQL> analyze table t09 compute statistics for all indexes;

Table analyzed.
select /*+index(t09 ind_owner_type_objid)*/* from t09 where owner=’SYS’ and object_type in (‘TABLE’,’INDEX’) and object_id>30000 and object_id<310000;该sql语句需要在owner、object_type、object_id上创建复合索引，这个复合索引创建顺序如何，这里我们只考虑让该sql的执行计划最优秀，不用考虑别的SQL能够共用该索引，下面我们来看看两种复合索引的性能和执行计划。

索引(owner+object_type+object_id):
SQL> select /*+index(t09 ind_owner_type_objid)*/* from t09 where owner='SYS' and object_type in ('TABLE','INDEX') and object_id>30000 and object_id<310000;

60 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1730993038

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   207 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T09                  |     1 |   207 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_OWNER_TYPE_OBJID |     1 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   3 - access("OWNER"='SYS' AND ("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE') AND
              "OBJECT_ID">30000 AND "OBJECT_ID"<310000)

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

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed
索引(object_type+owner+object_id):
SQL> select /*+index(t09 ind_type_owner_objid)*/* from t09 where owner='SYS' and object_type in ('TABLE','INDEX') and object_id>30000 and object_id<310000;

60 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1925664837

-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   207 |     5   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |                      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T09                  |     1 |   207 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_TYPE_OWNER_OBJID |     1 |       |     4   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   3 - access(("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE') AND "OWNER"='SYS' AND
              "OBJECT_ID">30000 AND "OBJECT_ID"<310000)

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

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed
看出来对于where后面的谓词条件owner=’SYS’ and object_type in (‘TABLE’,’INDEX’) and object_id>30000 and object_id<310000，无论是选择(owner+object_type+object_id)还是(object_type+owner+object_id)的复合索引，优化器在索引范围扫描过程中都可以直接对谓词条件走access，而不需要走filter，这里优化器做了INLIST ITERATOR执行计划，这个类似一个oracle index skip range的执行计划，当对in条件中的第一组做index range scan后，会重新跳跃到分支块上再做index range scan，这个相比oracle的另一种执行计划CONCATENATION要更加高效点，因为不用再从根节点来重新走分支块最后到叶块。

如果我们优化器回到8I，这两个SQL的执行计划依然一样
SQL> select /*+optimizer_features_enable('8.1.7') index(t09 ind_owner_type_objid)*/* from t09 where owner='SYS' and object_type in ('TABLE','INDEX') and object_id>30000 and object_id<310000;

60 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1730993038

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   207 |     4 |
|   1 |  INLIST ITERATOR             |                      |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T09                  |     1 |   207 |     4 |
|*  3 |    INDEX RANGE SCAN          | IND_OWNER_TYPE_OBJID |     1 |       |     3 |
-------------------------------------------------------------------------------------

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

   3 - access("OWNER"='SYS' AND ("OBJECT_TYPE"='INDEX' OR
              "OBJECT_TYPE"='TABLE') AND "OBJECT_ID">30000 AND "OBJECT_ID"<310000)

Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed

SQL> select /*+optimizer_features_enable('8.1.7') index(t09 ind_type_owner_objid)*/* from t09 where owner='SYS' and object_type in ('TABLE','INDEX') and object_id>30000 and object_id<310000;

60 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1925664837

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |   207 |     4 |
|   1 |  INLIST ITERATOR             |                      |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T09                  |     1 |   207 |     4 |
|*  3 |    INDEX RANGE SCAN          | IND_TYPE_OWNER_OBJID |     1 |       |     3 |
-------------------------------------------------------------------------------------

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

   3 - access(("OBJECT_TYPE"='INDEX' OR "OBJECT_TYPE"='TABLE') AND
              "OWNER"='SYS' AND "OBJECT_ID">30000 AND "OBJECT_ID"<310000)

Note
-----
   - cpu costing is off (consider enabling it)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         28  consistent gets
          0  physical reads
          0  redo size
       7609  bytes sent via SQL*Net to client
        556  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         60  rows processed

时间： 2024-11-05 16:40:26

oracle中复合索引的创建剖析—包含in的三个条件SQL语句复合索引的创建的相关文章

两张表中添加一天一条记录 求大神 帮忙看下 sql 语句该如何去写

问题描述 求大神帮忙解决一个问题比如说有三张表A,B,C三张表,我想将A中的某条数据点击添加按钮的时候同时加到B和C两张表,如何添加?A和B是一对多关系B和C是一对多关系sql语句该如何去写 解决方案 解决方案二:分开逻辑写呗.解决方案三:用触发器可以吗解决方案四:触发器没经常用,的忘记中写了解决方案五:还有其他办法吗

c# 水晶报表 dataset中使用SQL语句创建了 tableadapter 但是没有数据没有传递到表里面

问题描述 先是在数据源里面创建了一个空的DataSet然后用SQL语句在数据集里面创建了tableadapter在tableadapter预览了一下数据也是可以看到的,但是在水晶报表数据库专家添加了之前创建的数据集之后在里面没有查看到数据把字段拉进去水晶报表里面之后显示的东西也只是自带的测试数据不是我要的数据 解决方案 解决方案二:初步觉得应该是Fill即获取数据后填充的时候没有填充好,但是我用的是它自动生成的代码...

Oracle中创建临时表、外部表和分区表实例

临时表 在Oracle中,临时表是"静态"的,它与普通的数据表一样只需要一次创建,其结构从创建到删除的整个期间都是有效的.相对于其他类型的表,临时表只有在用户实际向表中添加数据时,才会为其分配空间,并且分配的空间来自临时表空间.这就避免了与永久对象的数据争用存储空间. 创建临时表的语法如下: CREATE GLOBAL TEMPORARY TABLE table_name( column_name data_type,[column_name data_type,...] )ON CO

oracle中12c比10g索引回表消耗增多的问题

问题是这样的: 在12c中,我们测试了2种情况: 第一种是加了hint,使得12c的执行计划和10g类似,只是由于12c的nlj_batching,多了一次nestloop.但是执行计划本质是相同的,都是索引S_CONTACT_X_U1返回表查询. 第二种是使用了10g的outline hint,OFE=10g的,执行计划完全一样. 但是我们发现,无论是在12c中的哪一种情况,驱动表S_SRV_REQ的索引PA_S_SRV_REQ_1_X的full index scan返回结果差异这么大?  

Oracle中的并行

Oracle中的并行 一Oracle中的并行 首先Oracle会创建一个进程用于协调并行服务进程之间的信息传递这个协调进程将需要操作的数据集例如表的数据块分割成很多部分称为并行处理单元然后并行协调进程给每个并行进程分配一个数据单元.例如有四个并行服务进程它们就会同时处理各自分配的单元当一个并行服务进程处理完毕后协调进程就会给它们分配另外的单元如此反复直到表上的数据都处理完毕最后协调进程负责将每个小的集合合并为一个大集合作为最终的执行结果返回给用户.并行处理的机制实际上就是把一个要扫描的数据集分成

创建索引对SQL语句执行的影响

一.创建索引对执行计划的影响 在SQL开始执行之前,Oracle会确定SQL语句的执行计划,并按照执行计划的步骤访问相应的表和索引. 一旦执行计划确定下来,Oracle会按照这个执行计划完成SQL语句的执行,在SQL语句执行开始之后建立的索引不会改变SQL语句的执行计划. 因此,创建索引不会对执行计划有任何的影响,也就不会对运行中的SQL语句有影响.下面通过一个例子简单验证一下: SQL> CREATE TABLE TEST (ID NUMBER, FID NUMBER, NAME VARCHA

Oracle中怎样用自治事务保存日志表

数据库事务是一种单元操作,要么是全部操作都成功,要么全部失败.在Oracle中,一个事务是从执行第一个数据治理语言(DML)语句开始,直到执行一个COMMIT语句,提交保存这个事务,或者执行一个ROLLBACK语句,放弃此次操作结束. 事务的"要么全部完成,要么什么都没完成"的本性会使将错误信息记入数据库表中变得很困难,因为当事务失败重新运行时,用来编写日志条目的INSERT语句还未完成. 针对这种困境,Oracle提供了一种便捷的方法,即自治事务.自治事务从当前事务开始,在其自身的语

Oracle中的游标、硬解析、软解析、软软解析、解析失败

Oracle中的游标.硬解析.软解析.软软解析.解析失败 一. 游标的分类及共享游标 游标(Cursor)是Oracle数据库中SQL解析和执行的载体,它可以分为共享游标(Shared Cursor)和会话游标(Session Cursor).共享游标可以细分为父游标(Parent Cursor)和子游标(Child Cursor),可以通过视图V$SQLAREA和V$SQL来查看当前缓存在库缓存(Library Cache)中的父游标和子游标,其中V$SQLAREA用于查看父游标,V$SQL用

oracle中动态SQL使用详细介绍_oracle

1．静态SQLSQL与动态SQL Oracle编译PL/SQL程序块分为两个种:其一为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型:另外一种是后期联编(late binding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理.通常,静态SQL采用前一种编译方式,而动态SQL采用后一种编译方式.