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索引扫描来查询
所以当创建复合索引后如果查询要想使用这个复合索引就必须在查询条件中
使用复合索引的前导列才会让优化器使用这个复合索引