查询中让优化器使用复合索引

-- Create table

create table MT_BIZ

(

  HOSPITAL_ID      VARCHAR2(20) not null,

  SERIAL_NO        VARCHAR2(16) not null,

  FEE_BATCH        NUMBER(5) default 1 not null,

  CASE_ID          NUMBER(12),

  BIZ_TYPE         VARCHAR2(2) not null,

  ORDINAL_NO       NUMBER(2) default 0 not null,

  DISTRICT_CODE    VARCHAR2(6),

  INDI_ID          NUMBER(12) not null,

  NAME             VARCHAR2(20) not null,

  SEX              CHAR(1) not null,

  PERS_TYPE        VARCHAR2(3) not null,

  OFFICE_GRADE     VARCHAR2(3) default '000' not null,

  IDCARD           VARCHAR2(25),

  IC_NO            VARCHAR2(25),

  BIRTHDAY         DATE,

  TELEPHONE        VARCHAR2(30),

  CORP_ID          NUMBER(10) not null,

  CORP_NAME        VARCHAR2(70) not null,

  TREATMENT_TYPE   VARCHAR2(3) not null,

  BIZ_TIMES        NUMBER(3),

  RELA_HOSP_ID     VARCHAR2(20),

  RELA_SERIAL_NO   VARCHAR2(16),

  SERIAL_APPLY     NUMBER(12),

  REG_DATE         DATE not null,

  REG_STAFF        VARCHAR2(8) not null,

  REG_MAN          VARCHAR2(20),

  REG_FLAG         CHAR(1) default '0' not null,

  BEGIN_DATE       DATE not null,

  REG_INFO         VARCHAR2(10),

  IN_DEPT          VARCHAR2(10),

  IN_DEPT_NAME     VARCHAR2(20),

  IN_AREA          VARCHAR2(10),

  IN_AREA_NAME     VARCHAR2(20),

  IN_BED           VARCHAR2(10),

  BED_TYPE         CHAR(1),

  PATIENT_ID       VARCHAR2(20),

  IN_DISEASE       VARCHAR2(20) not null,

  FOREGIFT         NUMBER(10,2),

  DIAGNOSE_DATE    DATE,

  DIAGNOSE         VARCHAR2(20),

  IN_DAYS          NUMBER(4),

  FIN_DISEASE      VARCHAR2(20),

  END_DATE         DATE,

  END_STAFF        VARCHAR2(8),

  END_MAN          VARCHAR2(30),

  FIN_INFO         VARCHAR2(10),

  IC_FLAG          CHAR(1) default '0' not null,

  REIMBURSE_FLAG   CHAR(1) default '0' not null,

  BIZ_END_DATE     DATE,

  FINISH_FLAG      CHAR(1) default '0' not null,

  POS_CODE         VARCHAR2(10),

  LOCK_FLAG        CHAR(1) default '0' not null,

  INJURY_BORTH_SN  NUMBER(12),

  REMARK           VARCHAR2(500),

  TRANS_FLAG       CHAR(1) default '0' not null,

  CENTER_ID        VARCHAR2(100) default 0 not null,

  PERS_TYPE_DETAIL VARCHAR2(3),

  CORP_TYPE_CODE   VARCHAR2(3),

  SPECIAL_CODE     VARCHAR2(3),

  DOCTOR_NO        VARCHAR2(20),

  DOCTOR_NAME      VARCHAR2(20),

  FIN_DISEASE1     VARCHAR2(20),

  FIN_DISEASE2     VARCHAR2(20),

  CASE_INFO        VARCHAR2(100),

  BILL_NO          VARCHAR2(20),

  HOS_SERIAL       VARCHAR2(30),

  DISEASE_TYPE     CHAR(1),

  DISEASE_FLAG     CHAR(1) default '0',

  RECUR_FLAG       CHAR(1) default '0',

  INJURY_TYPE      VARCHAR2(2) default '01'

);

-- Add comments to the table

comment on table MT_BIZ

  is '医疗业务表';

-- Add comments to the columns

comment on column MT_BIZ.HOSPITAL_ID

  is '医疗机构编号';

comment on column MT_BIZ.SERIAL_NO

  is '业务序列号';

comment on column MT_BIZ.FEE_BATCH

  is '费用批次';

comment on column MT_BIZ.CASE_ID

  is '病例分型序号';

comment on column MT_BIZ.BIZ_TYPE

  is '业务类别编号';

comment on column MT_BIZ.ORDINAL_NO

  is '内部序数';

comment on column MT_BIZ.DISTRICT_CODE

  is '社区编码(指个人所属行政区编码)';

comment on column MT_BIZ.INDI_ID

  is '个人编号';

comment on column MT_BIZ.NAME

  is '姓名';

comment on column MT_BIZ.SEX

  is '性别';

comment on column MT_BIZ.PERS_TYPE

  is '人员类别待遇代码';

comment on column MT_BIZ.OFFICE_GRADE

  is '公务员级别';

comment on column MT_BIZ.IDCARD

  is '公民身份号码';

comment on column MT_BIZ.IC_NO

  is 'IC卡号';

comment on column MT_BIZ.BIRTHDAY

  is '出生日期';

comment on column MT_BIZ.TELEPHONE

  is '联系电话';

comment on column MT_BIZ.CORP_ID

  is '单位编码';

comment on column MT_BIZ.CORP_NAME

  is '单位名称';

comment on column MT_BIZ.TREATMENT_TYPE

  is '待遇类别(用于区分同一业务类型的不同情况，比如生育门诊的三个月以上和三个月以上流产,不区分时为0)';

comment on column MT_BIZ.BIZ_TIMES

  is '本年业务次数';

comment on column MT_BIZ.RELA_HOSP_ID

  is '关联医疗机构编码';

comment on column MT_BIZ.RELA_SERIAL_NO

  is '关联业务序列号';

comment on column MT_BIZ.SERIAL_APPLY

  is '申请序列号';

comment on column MT_BIZ.REG_DATE

  is '业务登记日期';

comment on column MT_BIZ.REG_STAFF

  is '登记人工号';

comment on column MT_BIZ.REG_MAN

  is '登记人';

comment on column MT_BIZ.REG_FLAG

  is '登记标志(0：正常 1：转院 2：二次返院（审批通过后RELA_SERIAL_NO为空） 3：急诊留观转住院 4：90天或180天结算（处理后RELA_HOSP_ID为空，RELA_SERIAL_NO不为空）)';

comment on column MT_BIZ.BEGIN_DATE

  is '业务开始时间';

comment on column MT_BIZ.REG_INFO

  is '业务开始情况（FR：提取冻结费用的零报业务  MW：医疗转工伤的零报业务）';

comment on column MT_BIZ.IN_DEPT

  is '入院科室';

comment on column MT_BIZ.IN_DEPT_NAME

  is '入院科室名称';

comment on column MT_BIZ.IN_AREA

  is '入院病区';

comment on column MT_BIZ.IN_AREA_NAME

  is '入院病区名称';

comment on column MT_BIZ.IN_BED

  is '入院床位号';

comment on column MT_BIZ.BED_TYPE

  is '床位类型';

comment on column MT_BIZ.PATIENT_ID

  is '医院业务号';

comment on column MT_BIZ.IN_DISEASE

  is '入院疾病诊断';

comment on column MT_BIZ.FOREGIFT

  is '预付款总额';

comment on column MT_BIZ.DIAGNOSE_DATE

  is '确诊日期';

comment on column MT_BIZ.DIAGNOSE

  is '确诊疾病诊断';

comment on column MT_BIZ.IN_DAYS

  is '住院天数';

comment on column MT_BIZ.FIN_DISEASE

  is '出院疾病诊断';

comment on column MT_BIZ.END_DATE

  is '业务终结日期';

comment on column MT_BIZ.END_STAFF

  is '终结人工号';

comment on column MT_BIZ.END_MAN

  is '终结人';

comment on column MT_BIZ.FIN_INFO

  is '业务终结情况';

comment on column MT_BIZ.IC_FLAG

  is '用卡标志';

comment on column MT_BIZ.REIMBURSE_FLAG

  is '中心报帐标志';

comment on column MT_BIZ.BIZ_END_DATE

  is '诊次结束时间';

comment on column MT_BIZ.FINISH_FLAG

  is '完成标志';

comment on column MT_BIZ.POS_CODE

  is 'POS机编号';

comment on column MT_BIZ.LOCK_FLAG

  is '锁定标志';

comment on column MT_BIZ.INJURY_BORTH_SN

  is '对应的工伤生育业务号';

comment on column MT_BIZ.REMARK

  is '备注';

comment on column MT_BIZ.TRANS_FLAG

  is '传输标志(0:未传输 1:已成功传输 2:未成功传输）';

comment on column MT_BIZ.CENTER_ID

  is '医保中心编码';

comment on column MT_BIZ.PERS_TYPE_DETAIL

  is '人员类别详细代码(bs_person.PERS_TYPE)';

comment on column MT_BIZ.CORP_TYPE_CODE

  is '单位类型';

comment on column MT_BIZ.SPECIAL_CODE

  is '特殊人群编码';

comment on column MT_BIZ.DOCTOR_NO

  is '医生编号';

comment on column MT_BIZ.DOCTOR_NAME

  is '医生姓名';

comment on column MT_BIZ.FIN_DISEASE1

  is '第一副诊断';

comment on column MT_BIZ.FIN_DISEASE2

  is '第二副诊断';

comment on column MT_BIZ.CASE_INFO

  is '病历信息';

comment on column MT_BIZ.BILL_NO

  is '单据号';

comment on column MT_BIZ.HOS_SERIAL

  is '医院交易流水号';

comment on column MT_BIZ.DISEASE_TYPE

  is '病种分型(A：病种单纯 B：严重 C：严重并发 D：危重)';

comment on column MT_BIZ.DISEASE_FLAG

  is '职业病标志（0 不是职业病，1 是职业病）';

comment on column MT_BIZ.RECUR_FLAG

  is '工伤复发标志（0 不是工伤复发，1 是工伤复发）';

comment on column MT_BIZ.INJURY_TYPE

  is '工伤类别（01：新工伤,02老工伤,对应新增wi_injury_type码表）';

-- Create/Recreate primary, unique and foreign key constraints

alter table MT_BIZ

  add constraint PK_MT_BIZ primary key (HOSPITAL_ID, SERIAL_NO)

  using index;

-- Create/Recreate indexes

create index IDX_MT_BIZ_BEGIN_DATE on MT_BIZ (BEGIN_DATE);

create index IDX_MT_BIZ_BIZ_END_DATE on MT_BIZ (BIZ_END_DATE);

create index IDX_MT_BIZ_CORP_ID on MT_BIZ (CORP_ID);

create index IDX_MT_BIZ_IB on MT_BIZ (INJURY_BORTH_SN);

create index IDX_MT_BIZ_INDI_ID on MT_BIZ (INDI_ID);

create index IDX_MT_BIZ_RELA on MT_BIZ (RELA_HOSP_ID, RELA_SERIAL_NO);

在mt_biz表中有一个复合主键是由hospital_id与serial_no组成的

其中serial_no是唯一值是由序列生成的，所以在查询数据时有些语句只使用serial_no

造成了使用不上索引的问题

因为如果索引是建立在多个列上, 只有在它的第一个列也叫前导列(leading

column)被where子句引用时,优化器才会选择使用该索引.

例如,不使用合主键的唯一索引中的前导列hospital_id时的语句执行计划如下

SQL> set autotrace traceonly;

SQL> select * from mt_biz a where a.serial_no='15485197';

执行计划

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

Plan hash value: 3513793642

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

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

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

|   0 | SELECT STATEMENT  |        |     1 |   244 |    42   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| MT_BIZ |     1 |   244 |    42   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - filter("A"."SERIAL_NO"='15485197')

统计信息

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

          1  recursive calls

          0  db block gets

        190  consistent gets

          0  physical reads

          0  redo size

       2852  bytes sent via SQL*Net to client

        239  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

从显示的执行计划可以看出当没有使用复合索引中的前导列hospital_id时

是执行的全表扫描

当使用复合索引中的前导列hospital_id时

SQL> select * from mt_biz a where a.hospital_id='4307000009';

已选择348行。

执行计划

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

Plan hash value: 3033165289

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

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

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

|   0 | SELECT STATEMENT            |           |    18 |  4392 |     8   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MT_BIZ    |    18 |  4392 |     8   (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

   2 - access("A"."HOSPITAL_ID"='4307000009')

统计信息

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

          1  recursive calls

          0  db block gets

        345  consistent gets

          0  physical reads

          0  redo size

     102775  bytes sent via SQL*Net to client

        400  bytes received via SQL*Net from client

         25  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

        348  rows processed

给出的执行计划是通过INDEX RANGE SCAN来执行查询

当使用复合索引中所有列时

SQL> select * from mt_biz a where a.hospital_id='4307000009' and a.serial_no='15485197';

执行计划

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

Plan hash value: 2316229530

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

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

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

|   0 | SELECT STATEMENT            |           |     1 |   244 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MT_BIZ    |     1 |   244 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | PK_MT_BIZ |     1 |       |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("A"."HOSPITAL_ID"='4307000009' AND "A"."SERIAL_NO"='15485197')

统计信息

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

          1  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

       2818  bytes sent via SQL*Net to client

        232  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

给出的执行计划是使用INDEX UNIQUE SCAN索引扫描来查询

所以当创建复合索引后如果查询要想使用这个复合索引就必须在查询条件中

使用复合索引的前导列才会让优化器使用这个复合索引

时间： 2024-10-10 14:32:53

查询中让优化器使用复合索引的相关文章

Oracle中的优化器--CBO和RBO

Oracle中的优化器--CBO和RBO Oracle数据库中的优化器又叫查询优化器(Query Optimizer).它是SQL分析和执行的优化工具,它负责生成.制定SQL的执行计划.Oracle的优化器有两种,基于规则的优化器(RBO)与基于代价的优化器(CBO)          RBO: Rule-Based Optimization 基于规则的优化器          CBO: Cost-Based Optimization 基于代价的优化器 RBO自ORACLE 6以来被采用,一直沿

mysql-Left Join在大批量数据查询中如何优化

问题描述 Left Join在大批量数据查询中如何优化 Left Join在大批量数据查询中如何优化?具体表现为删除left join 时间大概为3秒,加上以后的时间为13秒. 解决方案 简历索引,比如 on a.id = b.aid 在给b的aid建立索引,索引是int类型效率会更好写,varchar类型没看出来效果. 解决方案二: 分成两步来做.先找出符合条件,再删除.然后就就是看索引有没有用处

MySQL数据库中与优化器有关的事宜

MySQL采用了基于开销的优化器,以确定处理查询的最解方式.在很多情况下,MySQL能够计算最佳的可能查询计划,但在某些情况下,MySQL没有关于数据的足够信息,不得不就数据进行"有教养"的估测. 当MySQL未能做"正确的"事时,可使用下述工具来帮助MySQL: 使用EXPLAIN语句获取关于MySQL如何处理查询的信息.要想使用它,可在SELECT语句前添加关键字EXPLAIN: mysql> EXPLAIN SELECT * FROM t1, t2 WH

如何对Oracle中的优化器进行评估优化

Oracle优化器在任何可能的时候都会对表达式进行评估,并且把特定的语法结构转换成等价的结构,这么做的原因是: ·要么结果表达式能够比源表达式具有更快的速度. ·要么源表达式只是结果表达式的一个等价语义结构. 不同的SQL结构有时具有同样的操作(例如:= ANY (subquery) and IN (subquery)),Oracle会把他们映射到一个单一的语义结构. 下面将讨论优化器如何评估优化如下的情况和表达式: 常量 LIKE 操作符 IN 操作符 ANY和SOME 操作符 ALL 操作符

解决MySQL数据库中与优化器有关的问题

MySQL采用了基于开销的优化器,以确定处理查询的最解方式.在很多情况下,MySQL能够计算最佳的可能查询计划,但在某些情况下,MySQL没有关于数据的足够信息,不得不就数据进行"有教养"的估测. 当MySQL未能做"正确的"事时,可使用下述工具来帮助MySQL: 使用EXPLAIN语句获取关于MySQL如何处理查询的信息.要想使用它,可在SELECT语句前添加关键字EXPLAIN: mysql> EXPLAIN SELECT * FROM t1, t2 WH

案例：MySQL优化器如何选择索引和JOIN顺序

我们知道,MySQL优化器只有两个自由度:顺序选择:单表访问方式:这里将详细剖析下面的SQL,看看MySQL优化器如何做出每一步的选择. explain select * from employee as A,department as B where A.LastName = 'zhou' and B.DepartmentID = A.DepartmentID and B.DepartmentName = 'TBX'; 1. 可能的选择 这里看到JOIN的顺序可以是A|B或者B|A,单表访问方

Oracle中建立索引并强制优化器使用

当WHERE子句对某一列使用函数时,除非利用这个简单的技术强制索引,否则Oracle优化器不能在查询中使用索引. 通常情况下,如果在WHERE子句中不使用诸如UPPER.REPLACE 或SUBSTRD等函数,就不能对指定列建立特定的条件.但如果使用了这些函数,则会出现一个问题:这些函数会阻碍Oracle优化器对列使用索引,因而与采用索引的情况相比较,查询会花费更多的时间. 庆幸的是,如果在使用函数的这些列中包含了字符型数据,可以用这样一种方法修改查询语句,以达到强制性使用索引,更有效地运行查询

使用索引的误区之一：没有使用复合索引的前导列导致查询不使用索引

索引 使用索引的误区之一:没有使用复合索引的前导列导致查询不使用索引在oracle中,我们经常以为建立了索引,sql查询的时候就会如我们所希望的那样使用索引,事实上,oracle只会在一定条件下使用索引,这里我们总结数第一点:oracle会在条件中包含了前导列时使用索引,即查询条件中必须使用索引中的第一个列,请看下面的例子 SQL> select * from tab;   TNAME                          TABTYPE  CLUSTERID -----------

MySQL · 捉虫动态 · order by limit 造成优化器选择索引错误

问题描述 bug 触发条件如下: 优化器先选择了 where 条件中字段的索引,该索引过滤性较好: SQL 中必须有 order by limit 从而引导优化器尝试使用 order by 字段上的索引进行优化,最终因代价问题没有成功. 复现case 表结构 create table t1( id int auto_increment primary key, a int, b int, c int, key iabc (a, b, c), key ic (c) ) engine = innod