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

-- 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 |

   2 - access("a"."hospital_id"='4307000009')

        345  consistent gets

     102775  bytes sent via sql*net to client

        400  bytes received via sql*net from client

         25  sql*net roundtrips to/from client

        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

|   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 |

   2 - access("a"."hospital_id"='4307000009' and "a"."serial_no"='15485197')

          3  consistent gets

       2818  bytes sent via sql*net to client

        232  bytes received via sql*net from client

          1  sql*net roundtrips to/from client

给出的执行计划是使用index unique scan索引扫描来查询

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

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