oracle的联合索引,oracle 优化之组合索引

最新推荐文章于 2024-04-23 21:45:50 发布
最新推荐文章于 2024-04-23 21:45:50 发布
阅读量4.4k 收藏 3
点赞数
文章标签： oracle的联合索引

组合索引适用场景：

1.适用在单独查询返回记录很多，组合查询后忽然返回记录很少的情况:

比如where 学历=硕士以上 返回不少的记录

比如where 职业=收银员 同样返回不少的记录

于是无论哪个条件查询做索引，都不合适。

可是，如果学历为硕士以上，同时职业又是收银员的，返回的就少之又少了。

于是联合索引就可以这么开始建了。

2.组合查询的组合顺序，要考虑单独的前缀查询情况(否则单独前缀查询的索引不能生效或者只能用到跳跃索引)

比如你在建id,object_type的联合索引时，要看考虑是单独where id=xxx查询的多，还是单独where object_type查询的多。

这里细节就暂时略去了，在案例的部分中还有描述

3.仅等值无范围查询时，组合索引顺序不影响性能(比如where col1=xxx and col2=xxx，无论COL1+COL2组合还是COL2+COL1组合)

drop table t purge;

create table t as select * from dba_objects;

insert into t select * from t;

insert into t select * from t;

insert into t select * from t;

update t set object_id=rownum ;

commit;

create index idx_id_type on t(object_id,object_type);

create index idx_type_id on t(object_type,object_id);

set autotrace off

alter session set statistics_level=all ;

set linesize 366

create index idx_id_type on t(object_id,object_type);

create index idx_type_id on t(object_type,object_id);

set autotrace off

alter session set statistics_level=all ;

set linesize 200

select /*+index(t,idx_id_type)*/ * from t where object_id=20 and object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  7qydm6x641kx4, child number 1

-------------------------------------

select /*+index(t,idx_id_type)*/ * from  t  where object_id=20  and

object_type='TABLE'

Plan hash value: 1470938839

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |      1 |        |      1 |00:00:00.01 |       5 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |      1 |      1 |      1 |00:00:00.01 |       5 |

|*  2 |   INDEX RANGE SCAN          | IDX_ID_TYPE |      1 |      1 |      1 |00:00:00.01 |       4 |

-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')

Note

-----

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

- cardinality feedback used for this statement

24 rows selected.

可以看出走的是索引范围扫描，并且回表。

--4.组合索引最佳顺序一般是将列等值查询的列置前。

(测试组合索引在条件是不等的情况下的情况，条件经常是不等的，要放在后面，让等值的在前面)

Execution Plan

----------------------------------------------------------

Plan hash value: 1470938839

-------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |    16 |  3312 |    52   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |    16 |  3312 |    52   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_ID_TYPE |    50 |       |    51   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">=20 AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<2000)

filter("OBJECT_TYPE"='TABLE')

Note

-----

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

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

89  consistent gets

0  physical reads

0  redo size

51596  bytes sent via SQL*Net to client

875  bytes received via SQL*Net from client

34  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

493  rows processed

通过索引快速扫面获得行ID进行回表。

5.注意组合索引与组合条件中关于IN 的优化

案例1

UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;

UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;

COMMIT;

set linesize 1000

set pagesize 1

alter session set statistics_level=all ;

select  /*+index(t,idx1_object_id)*/ * from t  where object_TYPE='TABLE'  AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;

Execution Plan

----------------------------------------------------------

Plan hash value: 3420768628

-------------------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             | 20755 |  4195K|  1186   (1)| 00:00:15 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           | 20755 |  4195K|  1186   (1)| 00:00:15 |

|*  2 |   INDEX RANGE SCAN          | IDX_TYPE_ID | 20755 |       |   218   (0)| 00:00:03 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID">=20 AND "OBJECT_ID"<=21)

Note

-----

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

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

5059  consistent gets

0  physical reads

0  redo size

1191984  bytes sent via SQL*Net to client

18464  bytes received via SQL*Net from client

1633  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

24472  rows processed

AAffA0nNPuCLAAAAAElFTkSuQmCC

select  /*+index(t,idx1_object_id)*/ * from t  where object_TYPE='TABLE'  AND OBJECT_ID in (20,21);

AAffA0nNPuCLAAAAAElFTkSuQmCC

以上两个语句：

select  /*+index(t,idx1_object_id)*/ * from t  where object_TYPE='TABLE'  AND OBJECT_ID >= 20 AND OBJECT_ID<= 21;     --CPU cost消耗1186

select /*+index(t,idx1_object_id)*/ * from t where object_TYPE='TABLE' AND OBJECT_ID in (20,21);               -- CPU cost消耗仅仅为5

为何消耗的CPU成本差距这么大？

在人为的思考中，我们人为认为 (OBJECT_ID >= 20 AND OBJECT_ID<= 21) = in (20,21),而其实oracle 不这么认为in (20,21) 只有两个值，而(OBJECT_ID >= 20 AND OBJECT_ID<= 21)走索引中间有无穷个键值。所以第二句消耗的CPU COST仅仅为5。

6.依然是关于IN的优化 (col1,col2,col3的索引情况，如果没有为COL2赋予查询条件时，COL3只能起到检验作用)

drop table t purge;

create table t as select * from dba_objects;

UPDATE t SET OBJECT_ID=20 WHERE ROWNUM<=26000;

UPDATE t SET OBJECT_ID=21 WHERE OBJECT_ID<>20;

Update t set object_id=22 where rownum<=10000;

COMMIT;

create index idx_union on t(object_type,object_id,owner);

set autotrace traceonly

select * from t where object_type='VIEW' and OWNER='SYS';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Execution Plan

----------------------------------------------------------

Plan hash value: 1570829420

-----------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |           |  3369 |   681K|    20   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T         |  3369 |   681K|    20   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_UNION |    14 |       |    19   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_TYPE"='VIEW' AND "OWNER"='SYS')

filter("OWNER"='SYS')

Note

-----

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

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

686  consistent gets

0  physical reads

0  redo size

157650  bytes sent via SQL*Net to client

3405  bytes received via SQL*Net from client

264  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

3938  rows processed

SQL>

23 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3713220770

----------------------------------------------------------------------------------------------------

| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |                |  8168 | 16336 |    29   (0)| 00:00:01 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |  8168 | 16336 |    29   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

27  recursive calls

0  db block gets

136  consistent gets

0  physical reads

0  redo size

1925  bytes sent via SQL*Net to client

534  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

23  rows processed

select /*+INDEX(T,idx_union)*/ * from t T where object_type='VIEW' and OBJECT_ID IN (20,21,22) AND OWNER='SYS';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Execution Plan

----------------------------------------------------------

Plan hash value: 306189815

------------------------------------------------------------------------------------------

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |           |  3369 |   681K|     6   (0)| 00:00:01 |

|   1 |  INLIST ITERATOR             |           |       |       |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T         |  3369 |   681K|     6   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IDX_UNION |     1 |       |     5   (0)| 00:00:01 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("OBJECT_TYPE"='VIEW' AND ("OBJECT_ID"=20 OR "OBJECT_ID"=21 OR

"OBJECT_ID"=22) AND "OWNER"='SYS')

Note

-----

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

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

687  consistent gets

0  physical reads

0  redo size

157650  bytes sent via SQL*Net to client

3405  bytes received via SQL*Net from client

264  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

3938  rows processed

SQL>

23 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3713220770

----------------------------------------------------------------------------------------------------

| Id  | Operation                         | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |                |  8168 | 16336 |    29   (0)| 00:00:01 |

|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |  8168 | 16336 |    29   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Statistics

----------------------------------------------------------

15  recursive calls

0  db block gets

0  consistent gets

0  physical reads

0  redo size

1862  bytes sent via SQL*Net to client

534  bytes received via SQL*Net from client

3  SQL*Net roundtrips to/from client

2  sorts (memory)

0  sorts (disk)

23  rows processed

可以看出，select * from t where object_type='VIEW' and OWNER='SYS'; 这一条语句跟select /*+INDEX(T,idx_union)*/ * from t T where object_type='VIEW' and OBJECT_ID IN (20,21,22) AND OWNER='SYS';这一条语句代价等价，因为此案例中object_type='VIEW' 且OBJECT_ID 只有20,21,22 这三条记录，所以如果没有为COL2赋予查询条件时，COL3只能起到检验作用。

weixin_39670545
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle联合索引命中规则,索引优化原则及Oracle中索引总结
weixin_39745013的博客
04-03 2070
Oracle索引建立原则确定针对该表的操作是大量的查询操作还是大量的增删改操作。尝试建立索引来帮助特定的查询。检查自己的sql语句，为那些频繁在where子句中出现的字段建立索引。where语句中不得不对查询列采用函数查询，如upper函数，最好建立相应函数索引；在SQL语句中经常进行GROUP BY、ORDER BY的字段上建立索引用于联接的列(主健/外健)上建立索引；在经常存取的多个列上建立复...
oracle predicate information,Oracle 执行计划（Explain Plan） 说明
weixin_35699471的博客
04-03 935
如果要分析某条SQL的性能问题，通常我们要先看SQL的执行计划，看看SQL的每一步执行是否存在问题。如果一条SQL平时执行的好好的，却有一天突然性能很差，如果排除了系统资源和阻塞的原因，那么基本可以断定是执行计划出了问题。看懂执行计划也就成了SQL优化的先决条件。这里的SQL优化指的是SQL性能问题的定位，定位后就可以解决问题。一．查看执行计划的三种方法1.1设置autotrace序号命令解释...
较为深入的解析联合索引最左匹配原则
lyk1184919379的博客
02-20 3172
mysql最左匹配原则的一些分析
（Oracle）SQL优化案例：组合索引优化
最新发布
但行好事，莫问前程
04-23 1343
SQL优化之组合索引真实案例，真实执行计划。
Oracle中sql怎么判断联合索引是否生效
zz_ll9023的博客
01-10 1471
在Oracle数据库中，你可以使用/*+ INDEX(table_name index_name) */提示来强制Oracle使用特定的索引。在上面的示例中，emp_idx_name是你要强制使用的索引的名称。通过在查询中添加/*+ INDEX(employees emp_idx_name) */提示，你告诉Oracle优化器考虑使用emp_idx_name索引来执行查询。在某些情况下，你可以使用/*+ INDEX(table_name index_name) */提示来强制Oracle使用特定的索引。
Oracle索引详解 --纯理论（好）
一个只求实际的程序猿
06-28 2675
低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。每个本地索引的分区只包含了它所关联的表分区的键和ROWID。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。全局分区索引在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。无前缀的索引并没有把分区键的前导列作为索引的前导列。1)如果Clustering Factor列的值接近于索引中的树叶块(leaf block)的数目,表中的数据就越有序。
Oracle中组合索引的使用详解
旅行人生
11-15 1644
在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。在组合索引的使用方面，Oracle有以下特点：    1、 当使用基于规则的优化器（RBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引；    2、 在使用Oracle9i之前的基于成本的优化器（CBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才可能会使用到
Oracle中的索引
m0_71406734的博客
06-10 8555
了解Oracle中的索引概念，有哪些类型，如何使用索引，清楚索引的作用！
Oracle数据库中索引的基本使用
Keep_Trying_Go的博客
10-26 4027
Oracle数据库中索引的基本使用
Oracle索引梳理系列（八）- 索引扫描类型及分析（高效索引必备知识）
weixin_30527423的博客
10-20 236
版权声明：本文发布于http://www.cnblogs.com/yumiko/，版权由Yumiko_sunny所有，欢迎转载。转载时，请在文章明显位置注明原文链接。若在未经作者同意的情况下，将本文内容用于商业用途，将保留追究其法律责任的权利。如果有问题，请以邮箱方式联系作者（793113046@qq.com）。 理解oracle索引扫描类型的特点以及具体触发的条件，对于通过合理地使用...
SQL语句优化技巧
小凳子的专栏
06-08 746
SQL语句优化技巧　 　4）避免索引列上使用函数 　　-->下面是一个来自实际生产环境的例子 　　-->表acc_pos_int_tbl上business_date列存在索引，由于使用了SUBSTR函数，此时索引失效，使用全表扫描 SELECT acc_num      , curr_cd      , DECODE( '20110728'
【Oracle 优化器】自适应执行计划(Adaptive Execution Plans)
SQLplusDB的小院
07-09 4529
本文介绍Oracle 12c 推出的自适应连接方法（Adaptive Join Methods）和自适应并行分布方法（Adaptive Parallel Distribution Methods APDM）
Oracle 索引 详解
无双小宝
01-09 3814
<br />一．索引介绍<br /> 1.1 索引的创建语法： <br />CREATE UNIUQE | BITMAP INDEX <schema>.<index_name><br />      ON <schema>.<table_name><br />           (<column_name> | <expression> ASC | DESC,<br />            <column_name> | <expression> ASC | DESC,...)<br />     T
索引字段顺序对效率的影响
Enjoy Life
10-25 6903
个人总结几点： 1、驱动表中的索引要将区间字段（sendtime之类）放到固定值（orgid等）的后面 2、驱动表的连接字段可以放在索引最后，以避免读取rowid 3、连接表的连接字段要放在索引最前面。 举个例子： selectcount(*) as col_0_0_   from REC_BANKINSTRUCTION batchrecba0_  crossjoin
oracle之索引
zhuangyangoal的专栏
03-23 353
1.索引分类a) 唯一索引,    作用是数据约束，保证数据唯一，还有就是数据索引，提高查询效率b)一般索引，只有数据索引的作用， 2.唯一索引的建立create unique index 索引名 on    表名(字段名)ok,假设有一个Emploeyy表，里面有一个empName字段，我们来为empName添加唯一索引cr
oracle组合索引
iechenyb专栏
08-23 3035
组合索引的使用情况分析
常用SQL汇总
Derek7117的博客
04-21 3992
常用sql汇总
Oracle数据库优化：分区与索引详解
"Oracle炼石成金系列中关于分区与索引的内容深入浅出地探讨了数据库性能优化的关键要素。该资源首先介绍了索引在Oracle数据库中的核心作用，即提高数据访问效率，使查询能够直接定位到所需数据，从而提升查询速度。...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值