组合索引

最新推荐文章于 2022-09-02 10:41:06 发布
最新推荐文章于 2022-09-02 10:41:06 发布
阅读量4.3k 收藏 5
点赞数 1
分类专栏： oracle sql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yftianxia/article/details/14052835
版权

组合索引（concatenated index）：由多个列构成的索引，

创建组合索引create index idx_detp on detp（col1，col2,col3，....），则我们称idx_emp索引为组合索引。

在组合索引中有一个重要的概念：引导列（leading column），在上面的例子中，col1列为引导列。

当我们进行查询时where 限制条件必须有引导列.

create index idx_detp on detp（col1，col2,col3，....） 在idx_detp组合索引，col1为引导列

上面的例子，

一，使用组合索引的情况（where 条件中有引导列）

1，where col1 =

2，,where col1 = and col2=

3，,where col2 = and col1=

二，不会使用该索引（where 条件中没有引导列）

where col2= ”查询就不会使用该索引。

所以限制条件中包含先导列时，该限制条件才会使用该组合索引。

下面是测试案例

一，以为dba_objects表的数据来测试，建一个测试表

SQL> create table t as select * from dba_objects;


Table created.

SQL> select count(*) from t;

  COUNT(*)
----------
     72766

二，创建组合索引object_id列为引导列

SQL> create index object_id_t on t(object_id,data_object_id);

Index created.

SQL> show parameter sql_trace;

NAME           TYPE             VALUE
--------- ------------  ------------------
sql_trace      boolean     FALSE


SQL> alter system set sql_trace=true;


System altered.

SQL> set autotrace traceonly;
SQL> select * from t where object_id=20;      --------从执行计划可以看出，走的是索引

Execution Plan
----------------------------------------------------------
Plan hash value: 46312585

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

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

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

|   0 | SELECT STATEMENT            |             |     1 |   207 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |   207 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | OBJECT_ID_T |     1 |       |     2   (0)| 00:00:01 |

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


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

   2 - access("OBJECT_ID"=20)

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


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         71  consistent gets
          0  physical reads
          0  redo size
       1389  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from t where data_object_id=2;    --- data_object_id不是引导了，在查找中因为where条件，没有引导列，虽然建了索引，但是没有起到作用         

18 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    11 |  2277 |   283   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    11 |  2277 |   283   (1)| 00:00:04 |             ----------走的是全表索引
--------------------------------------------------------------------------

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

   1 - filter("DATA_OBJECT_ID"=2)

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


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       1104  consistent gets
       1035  physical reads
          0  redo size
       2065  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed

SQL> select * from t where data_object_id=2 and object_id=20;         -------where条件有引导列，在查询中，执行计划走的是组合索引


Execution Plan
----------------------------------------------------------
Plan hash value: 46312585

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

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

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

|   0 | SELECT STATEMENT            |             |     1 |   207 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |     1 |   207 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | OBJECT_ID_T |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("OBJECT_ID"=20 AND "DATA_OBJECT_ID"=2)

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


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
         71  consistent gets
          0  physical reads
          0  redo size
       1389  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

          1  rows processed


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

在组合索引中使用不等于操作符（<>、!=） 不走索引，


测试案例，
SQL> select * from t where data_object_id=2 and object_id<>20;   --------没有走索引

17 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    33 |  6831 |   283   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    33 |  6831 |   283   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("DATA_OBJECT_ID"=2 AND "OBJECT_ID"<>20)

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


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       1104  consistent gets
       1035  physical reads
          0  redo size
       2042  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         17  rows processed


SQL> select * from t where data_object_id=2 and object_id !=20;  ----使用了！=不等符，没有走索引

17 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    33 |  6831 |   283   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    33 |  6831 |   283   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("DATA_OBJECT_ID"=2 AND "OBJECT_ID"<>20)

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


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       1104  consistent gets
       1035  physical reads
          0  redo size
       2042  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         17  rows processed


修改sql语句，使它走组合索引（通过把不等于操作符改成OR条件，就可以使用索引，以避免全表扫描）
SQL> select * from t where data_object_id=2 and  (object_id <20 or object_id>20);

17 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 396627435

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

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

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

|   0 | SELECT STATEMENT             |             |    65 | 13455 |     8   (0)| 00:00:01 |

|   1 |  CONCATENATION               |             |       |       ||          |

|   2 |   TABLE ACCESS BY INDEX ROWID| T           |    33 |  6831 |     4   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | OBJECT_ID_T |     3 |       |     3   (0)| 00:00:01 |

|   4 |   TABLE ACCESS BY INDEX ROWID| T           |    32 |  6624 |     4   (0)| 00:00:01 |

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

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


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

   3 - access("OBJECT_ID">20 AND "DATA_OBJECT_ID"=2 AND "OBJECT_ID" IS NOT NULL)
       filter("DATA_OBJECT_ID"=2)
   5 - access("DATA_OBJECT_ID"=2 AND "OBJECT_ID"<20)
       filter("DATA_OBJECT_ID"=2 AND LNNVL("OBJECT_ID">20))

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


Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
        446  consistent gets
          0  physical reads
          0  redo size
       2086  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         17  rows processed




















人生没有后悔
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
数据库组合索引
LMAKE_nbsp的博客
07-07 1806
2018、03、12面试中的问题 组合索引是什么？组合索引建立的原则。b树和b+树的区别。 一次查询只能用到一个索引，所以 首先枪毙 a，b各建索引方案 a还是b？ 谁的区分度更高（同值的最少），建谁！ 当然，联合索引也是个不错的方案，ab，还是ba，则同上，区分度高者，在前 索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始...
【MySQL精通之路】SQL优化(1)-查询优化(3)-索引合并
最新发布
Anakki的博客
05-22 1042
Index Merge访问方法检索具有的行，并将其结果合并为一个。博主PS。也就是说数据库在where条件查询数据时，如果a条件查询完成后，a条件过滤出的主键去聚簇索引查找数据，回表查询。b条件，c条件同理。最后在通过条件之间是and还是or对结果进行过滤，索引合并后，就提前做这个操作了。先把二级索引上过滤出的主键，看是and还是or取交集，并集，或差集，得出过滤后的主键，再用过滤后的主键进行回表查询，大大减少回表次数。此查询，而不合并。合并可以生成其基础扫描的或。博主PS。
什么是组合索引？在哪些场景中，组合索引会失效？
热门推荐
码瘾的空间
02-17 1万+
什么是组合索引？   给多个字段一个索引叫组合索引。 问题：在哪些场景中，组合索引会失效？ 场景： 数据表：job_status_trace_log【说明:id是主键】 数据量：35w 创建索引：ALTER table job_status_trace_log add INDEX creation_time_index(creation_time,job_name,source) 例子：组合索引...
Oralce 组合索引
u010738038的专栏
09-02 2432
Oralce 组合索引，没有前导列时不一定不走索引，量大时，必须加上前导列，否则全表扫描
mysql 组合索引 in_mysql组合索引与字段顺序
weixin_35859737的博客
01-18 1195
转自：http://www.cnblogs.com/sunss/archive/2010/09/14/1826112.html很多时候，我们在mysql中创建了索引，但是某些查询还是很慢，根本就没有使用到索引！一般来说，可能是某些字段没有创建索引，或者是组合索引中字段的顺序与查询语句中字段的顺序不符。看下面的例子：假设有一张订单表(orders)，包含order_id和product_id二个字段...
MySql组合索引
qq_32300143的博客
07-07 4313
1.创建组合索引 create index index_meterwarn_fuhe on t_iot_meter_warn(METERNO,TRIGNUM,WARNTYPECODE) 2.查看索引使用情况 explain select a.*from t_iot_meter_warn a where a.TRIGNUM = 22 and a.WARNTYPECODE = 01 说明没有用到索引，以下几个个语句均用到： explain select a.*from t_iot_meter_wa
MongoDB组合索引
duzm200542901104的专栏
08-17 1万+
MongoDB支持组合索引，就是一个索引结构里面包括一个集合文档的多个字段。下图展示了一个拥有两个字段的组合索引： 组合索引的字段不能超过31个 组合索引查询的时候支持多个字段的匹配   一、创建一个组合索引 创建组合索引的语法格式如下： db.collection.createIndex({field1:type,field2:type2,***}) 跟在字段后面的索引声明描述...
oracle如何组合索引,Oracle中组合索引的使用
weixin_31983133的博客
04-04 3591
Oracle中组合索引的使用Oracle中组合索引的使用一、什么是索引？数据库索引类似一本书的目录，目录中将书的内容先分为第一层(第一章、第二章……第N章的标题和对应页码)，第二层(1.1XXX，1.2XXX……N.nXXX)具体某个小章节的标题和对应页码。读者可以根据目录快速查找到自己想看的内容，不用一页一页翻阅书本查找。Oracle主要是使用了与二叉平衡树类似的B数的来存储索引数据，在之前写的...
单个索引和组合索引（联合索引）谁效率高
养歌的博客
01-26 1万+
首先我们创建第一张表为组合索引，如下所示： 第二种表为单列索引，如下所示： 创建完表之后我们开始
MySQL 组合索引及覆盖索引
z13197919100的博客
05-11 5875
MySQL 组合索引及覆盖索引
Mysql之组合索引方法详解
09-08
在MySQL中，除了单一列的索引，还可以创建组合索引，也就是在一个索引中包含多个列。组合索引的创建和使用策略对于优化数据库性能至关重要。 组合索引的顺序非常重要，因为MySQL只能对索引中最左边列的前缀进行有效...
MongoDB性能篇之创建索引，组合索引，唯一索引，删除索引和explain执行计划
09-10
本篇将深入探讨MongoDB中的索引创建、组合索引、唯一索引、删除索引以及`explain`执行计划。 1. **基础索引** MongoDB在创建集合时，默认为`_id`字段创建一个唯一的升序索引，这是无法删除的。创建其他索引，例如...
MySQL组合索引与最左匹配原则详解
09-09
MySQL中的组合索引和最左匹配原则是数据库优化的关键概念，尤其在处理大量数据时，它们可以帮助提升查询性能。本文将深入探讨这两个概念，并通过示例进行解释。 **组合索引**是在多列上创建的单个索引，而不是单独...
索引跳跃扫描
11-13 1924
从Oracle9i开始，索引跳跃式扫描特性可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。索引跳跃式扫描比全索引扫描要快的多 SQL> create table t as select * from all_objects; Table created. SQL> select count(*) from t;   COUNT(*)
利用dbms_stats收集统计信息（待续）
11-04 1401
统计收集的权限 普通用户必须有execute_catalog_role权限 grant execute_catalog_role to hr; grant connect,resource,analyze any to hr; 统计收集的时间考虑 当参数STATISTICS_LEVEL设置为TYPICAL或者ALL，系统会在夜间自动收集统计信息。 表统计； 统计表的行
讨论B树索引中的 is null/is not null
11-02 1370
为什么在查询中使用IS NULL 或IS NOT NULL同样会限制索引的使用 简答的说因为索引键值不会存储空值 Oracle的CBO并不会因为SQL语句中指定了IS NOT NULL或IS NULL操作就不再使用索引。CBO选择索引的条件只有正确性和代价，只要满足这两个条件就可以了。 测试案例 以dba_objects表的数据建一个测试表为T   create table
oracle CBO优化器何时会选择全表扫描
11-02 989
全表扫描将读取高水位线(HWM)之下的所有数据块从数据文件读到缓冲区，然后读取每项行数据，看是否满足WHERE子句过滤条件。 当Oracle执行全表扫描时会按顺序读取每个块且只读一次，如果能够一次读取多个块，可以有效的提高效率， 初始化参数DB_FILE_MULTIBLOCK_READ_COUNT用来设置在一次I/O中可以读取多少个数据块。 通常我们认为应该避免全表扫描，但是在检
oracle组合索引
09-13
Oracle中的组合索引是一种包含多个列的索引，它可以加快多列查询的性能。通过创建组合索引，可以同时对多个列进行索引，从而提高查询的效率。 在创建组合索引时，需要考虑查询语句中经常使用的多个列，并将这些列...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值