查詢中讓優化器使用複合索引
-- 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索引掃描來查詢
所以當建立複合索引後如果查詢要想使用這個複合索引就必須在查詢條件中
使用複合索引的前導列才會讓優化器使用這個複合索引
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-750981/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【oracle 效能優化】組合索引查詢。Oracle優化索引
- SUM優化（複合索引）優化索引
- 使用with子句優化程式碼中重複查詢優化
- MySQL索引與查詢優化MySql索引優化
- 複合索引中前導列對sql查詢的影響索引SQL
- MySQL-效能優化-索引和查詢優化MySql優化索引
- 一個複合索引的優化案例索引優化
- 查詢優化器優化
- 複合索引與函式索引優化一例索引函式優化
- SQL優化案例一則--複合索引沒有被使用SQL優化索引
- 使用點陣圖連線索引優化OLAP查詢索引優化
- MySQL 索引及查詢優化總結MySql索引優化
- MySQL索引原理及慢查詢優化MySql索引優化
- MongoDB範圍查詢的索引優化MongoDB索引優化
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- Elasticsearch複合查詢——boosting查詢Elasticsearch
- msyql千萬級別查詢優化之索引優化索引
- MS SQL SERVER索引優化相關查詢SQLServer索引優化
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- 使用點陣圖索引和星形轉換優化OLAP查詢索引優化
- 為何在查詢中索引未被使用索引
- SQL優化（二）（聯合索引的使用）SQL優化索引
- 增加複合索引優化SQL的簡單過程索引優化SQL
- ORACLE 9i資料庫優化案例(2) --- 單列索引變複合索引的優化Oracle資料庫優化索引
- MySQL進階【五】—— MySQL查詢優化器是如何選擇索引的MySql優化索引
- 【SQL優化器查詢變換器】檢視合併(View Merging)SQL優化View
- 使用組合索引處理包含空值的查詢索引
- 優化-mysql子查詢索引失效問題解決優化MySql索引
- 查詢優化優化
- MySQL: 使用explain 優化查詢效能MySqlAI優化
- 淺談查詢優化器中的JOIN演算法優化演算法
- MongoDB中複合索引結構MongoDB索引
- Elasticsearch 複合查詢——多字串多欄位查詢Elasticsearch字串
- mongodb索引及查詢優化分析MongoDB索引優化
- mysql千萬級資料量根據索引優化查詢速度MySql索引優化
- SQL使用模糊查詢like的優化SQL優化
- pgsql查詢優化之模糊查詢SQL優化
- Oracle in 查詢優化Oracle優化