查詢中讓優化器使用複合索引

-- 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索引掃描來查詢

是以當建立複合索引後如果查詢要想使用這個複合索引就必須在查詢條件中

使用複合索引的前導列才會讓優化器使用這個複合索引