建立合理的索引避免order by操作提升SQL效能的案例一
某醫保系統的查詢一年內一箇中心所有醫療費登賬記錄的彙總情況執行3000多秒才能出結果，下面TOP SQL中執行時間最長的SQL語句它的功能是查詢一年內一箇中心所有醫療費登賬記錄的彙總情況，執行時間3286秒,則且這條SQL就是我們要找的SQL語句。
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text 3,286 2,015 1 3,286 18.59 8aw9tm6w83usm JDBC Thin Client select t1.account_id, ... 622 316 17 36.60 3.52 ggrctzgtcg14s JDBC Thin Client select t.pay_type as pay_... 222 4 1 221.64 1.25 fvy1hnauapb8s JDBC Thin Client select (select center_name fro... 204 5 1 203.60 1.15 fcm5wrxny5b2u JDBC Thin Client select count(1) cs from bs_ho... 198 125 31 6.38 1.12 cfuhdxk446uzv JDBC Thin Client select * from ( select a.hosp... 177 20 1 177.00 1.00 42zd8jamnw4b4 PL/SQL Developer begin dbms_sqltune.execute_tu... 158 88 1 158.21 0.89 17t35rynaz40v JDBC Thin Client select u.center_name, x.hospit... 142 89 97 1.46 0.80 a7dkwg8uhrwkj JDBC Thin Client select * from ( select a.hosp... 135 48 1,417 0.10 0.76 aay99a855zv3u JDBC Thin Client SELECT distinct c.NAME, ... 116 73 303 0.38 0.66 31tf0tfn8nv6f JDBC Thin Client select a.pay_batch_no, a.pay_b...
其SQL如下所示:
select t1.account_id,
t1.hospital_id,
(select hosp_level
from bs_hospital a
where t1.hospital_id = a.hospital_id
and a.valid_flag = '1') as hosp_level,
t1.serial_no,
t1.icd,
(select t4.disease
from bs_disease t4
where t1.icd = t4.icd(+)
and t4.center_id = nvl(center.catalog_center, center.center_id)) as disease,
t1.biz_type,
t1.treatment_type,
t1.disease_type,
t1.reimburse_flag,
t1.corp_id,
t1.corp_type_code,
t2.corp_name,
t1.indi_id,
t1.pers_type,
t2.center_id,
t2.district_code,
decode(t2.center_id, t2.district_code, 0, 1) as out_type,
t1.biz_flag,
t1.biz_flag_old,
t2.name,
t2.idcard,
t2.sex,
t2.begin_date,
t2.end_date,
t1.fin_date,
t2.reg_date,
t1.disease_fee,
t1.violate_flag,
t1.patient_id,
t1.in_days,
decode(t1.allow_treat, '0', 0, 1) As allow_treat,
Decode(T1.Treatment_Type_Last,
'',
T1.Treatment_Type,
Decode(T1.Treatment_Type,
'120',
'120',
Decode(T1.Biz_Type,
'41',
'扣減已記帳金額',
'42',
'扣減已記帳金額',
T1.Treatment_Type_Last))) As Treatment_Type_Last,
t1.violate,
t1.pay_money,
t1.pay_fund,
t1.audit_money,
'0' as finish_flag,
t1.MONTH_DECL_SN,
t1.deal_flag,
t1.audit_deal_flag,
t1.declare_icd_guide,
t1.declare_audit_fee,
t1.declare_audit_fee_cl,
t1.first_icd_guide,
t1.first_audit_fee,
t1.first_audit_fee_cl,
t1.second_icd_guide,
t1.second_audit_fee,
t1.second_audit_fee_cl,
t1.PAY_otherfee,
t1.PAY_FUND003,
t1.PAY_FUND001,
t1.PAY_FUND201,
t1.PAY_FUND301,
t1.PAY_FUND999,
t1.PAY_FUND996,
t1.PAY_begin
from (select a.account_id,
a.hospital_id,
a.serial_no,
a.center_id,
a.icd,
a.biz_type,
a.treatment_type,
a.reimburse_flag,
a.corp_id,
a.corp_type_code,
a.indi_id,
a.pers_type,
a.biz_flag,
a.biz_flag_old,
a.fin_date,
a.violate_flag,
(select mbf.disease_type
from mt_biz_fin mbf
where mbf.valid_flag = '1'
and mbf.hospital_id = a.hospital_id
and mbf.serial_no = a.serial_no) disease_type,
(Select aa1.allow_treat
From pm_account_scene aa1
where aa1.hospital_id = a.hospital_id
and aa1.serial_no = a.serial_no
and aa1.valid_flag = '1') as allow_treat,
(select distinct bb1.patient_id
from mt_biz_fin bb1
where bb1.hospital_id = a.hospital_id
and bb1.serial_no = a.serial_no
and bb1.valid_flag = '1') as patient_id,
(select distinct bb1.in_days
from mt_biz_fin bb1
where bb1.hospital_id = a.hospital_id
and bb1.serial_no = a.serial_no
and bb1.valid_flag = '1') as in_days,
(select Max(audit_treat_value)
from pm_treat_audit aa
where aa.account_id = a.account_id
and aa.audit_staff_id = '2208'
and aa.audit_phase = '1'
and aa.valid_flag = '1') as treatment_type_last,
a.violate,
nvl((SELECT sum(pm_account_fund.pay_money)
FROM pm_account_fund
WHERE a.account_id = pm_account_fund.account_id
AND pm_account_fund.valid_flag = '1'),
0) as pay_money,
nvl((SELECT sum(pm_account_fund.pay_money)
FROM pm_account_fund
WHERE a.account_id = pm_account_fund.account_id
AND pm_account_fund.valid_flag = '1'
AND pm_account_fund.fund_id not in
('003', '999', '996', '997')),
0) as pay_fund,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '003'),
0) AS PAY_FUND003,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID in ('801', '001')),
0) AS PAY_FUND001,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '201'),
0) AS PAY_FUND201,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '301'),
0) AS PAY_FUND301,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '999'),
0) AS PAY_FUND999,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '996'),
0) AS PAY_FUND996,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID not in
('801', '001', '003', '201', '999')),
0) AS PAY_otherfee,
NVL((SELECT SUM(mprf.real_pay)
FROM mt_pay_record_fin mprf
WHERE A.hospital_id = mprf.hospital_id
AND A.serial_no = mprf.serial_no
AND mprf.policy_item_code = 'S01'),
0) AS PAY_begin,
(select nvl(sum(b.AUDIT_MONEY), 0)
from pm_fee_audit b
where a.account_id = b.account_id
and b.audit_staff_id = '2208'
and b.AUDIT_TYPE = '1'
and b.AUDIT_PHASE = '1'
and b.valid_flag = '1') as audit_money,
a.MONTH_DECL_SN,
a.deal_flag,
decode(nvl(a.disease_fee, 0),
0,
nvl((select nvl(max(ration), 0)
from pm_ration pr
where pr.center_id = a.center_id
and pr.hospital_id = a.hospital_id
and pr.treatment_type = a.treatment_type
and pr.year = to_char(a.fin_date, 'yyyy')
and (decode(a.pers_type,
'21',
'21',
'22',
'21',
'23',
'21',
'24',
'21',
'41',
'21',
'3',
'12',
'4',
'12',
'5',
'12',
'7',
'12',
'2') = pr.insr_no or
pr.insr_no = '0')
and rownum < 2
and pr.biz_flag = a.biz_flag),
0),
a.disease_fee) as disease_fee,
a.audit_flag as audit_deal_flag,
a.declare_icd_guide as declare_icd_guide,
a.declare_audit_fee as declare_audit_fee,
a.declare_audit_fee_cl as declare_audit_fee_cl,
a.first_icd_guide as first_icd_guide,
a.first_audit_fee as first_audit_fee,
a.first_audit_fee_cl as first_audit_fee_cl,
decode(a.biz_type,
'12',
decode(a.biz_flag_old, 5, '4304001', a.second_icd_guide),
a.second_icd_guide) second_icd_guide,
a.second_audit_fee as second_audit_fee,
a.second_audit_fee_cl as second_audit_fee_cl
from pm_account_biz a
where 1 = 1
and ('0' = 'A' or a.reimburse_flag ='0')
and ('00' = '00' or a.biz_type ='00')
and a.center_id in ('430722')
and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
and a.valid_flag = '1'
and a.deal_flag in ('0', '1', '2')
and (nvl(a.staff1_finish_flag, '0') ='0' and
nvl(a.staff2_finish_flag, '0') = '0' and
nvl(a.staff3_finish_flag, '0') = '0')
group by a.account_id,
a.hospital_id,
a.serial_no,
a.center_id,
a.icd,
a.biz_type,
a.treatment_type,
a.reimburse_flag,
a.corp_id,
a.corp_type_code,
a.indi_id,
a.pers_type,
a.biz_flag,
a.biz_flag_old,
a.disease_fee,
a.fin_date,
a.violate_flag,
a.violate,
a.MONTH_DECL_SN,
a.deal_flag,
a.audit_flag,
a.declare_icd_guide,
a.declare_audit_fee,
a.declare_audit_fee_cl,
a.first_icd_guide,
a.first_audit_fee,
a.first_audit_fee_cl,
a.second_icd_guide,
a.second_audit_fee,
a.second_audit_fee_cl) t1,
mt_biz_fin t2,
bs_center center
where t1.serial_no = t2.serial_no
AND t1.hospital_id = t2.hospital_id
AND t2.valid_flag = '1'
and t1.center_id = center.center_id(+)
order by t1.hospital_id, t1.serial_no
上面SQL的執行計劃如下：
---------------------------------------------------------------------------------------------------------------------------- | Id | Order | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------------------- | 0 | 67 | SELECT STATEMENT | | | | 56871 (100)| | | 1 | 2 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 16 | 1 (0)| 00:00:01 | | 2 | 1 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | | 3 | 4 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | 1 (0)| 00:00:01 | | 4 | 3 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | 1 (0)| 00:00:01 | | 5 | 6 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | | 6 | 5 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 7 | 10 | SORT AGGREGATE | | 1 | 33 | | | | 8 | 9 | COUNT STOPKEY | | | | | | | 9 | 8 | TABLE ACCESS BY INDEX ROWID | PM_RATION | 1 | 33 | 1 (0)| 00:00:01 | | 10 | 7 | INDEX RANGE SCAN | IDX_PM_RATION_1 | 1 | | 1 (0)| 00:00:01 | | 11 | 13 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 | | 12 | 12 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | | 13 | 11 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 14 | 16 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 | | 15 | 15 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | | 16 | 14 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 17 | 18 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_SCENE | 1 | 28 | 1 (0)| 00:00:01 | | 18 | 17 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_SCENE_1 | 1 | | 1 (0)| 00:00:01 | | 19 | 21 | SORT AGGREGATE | | 1 | 37 | | | | 20 | 20 | TABLE ACCESS BY INDEX ROWID | PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 | | 21 | 19 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 | | 22 | 24 | SORT AGGREGATE | | 1 | 12 | | | | 23 | 23 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 12 | 1 (0)| 00:00:01 | | 24 | 22 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 25 | 27 | SORT AGGREGATE | | 1 | 16 | | | | 26 | 26 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 27 | 25 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 28 | 30 | SORT AGGREGATE | | 1 | 21 | | | | 29 | 29 | TABLE ACCESS BY INDEX ROWID | PM_FEE_AUDIT | 1 | 21 | 1 (0)| 00:00:01 | | 30 | 28 | INDEX RANGE SCAN | PK_PM_FEE_AUDIT | 1 | | 1 (0)| 00:00:01 | | 31 | 33 | SORT AGGREGATE | | 1 | 16 | | | | 32 | 32 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 33 | 31 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 34 | 36 | SORT AGGREGATE | | 1 | 16 | | | | 35 | 35 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 36 | 34 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 37 | 40 | SORT AGGREGATE | | 1 | 16 | | | | 38 | 39 | INLIST ITERATOR | | | | | | | 39 | 38 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 40 | 37 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 41 | 43 | SORT AGGREGATE | | 1 | 16 | | | | 42 | 42 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 43 | 41 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 44 | 46 | SORT AGGREGATE | | 1 | 16 | | | | 45 | 45 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 46 | 44 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 47 | 49 | SORT AGGREGATE | | 1 | 16 | | | | 48 | 48 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 49 | 47 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 50 | 52 | SORT AGGREGATE | | 1 | 16 | | | | 51 | 51 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | | 52 | 50 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 53 | 55 | SORT AGGREGATE | | 1 | 39 | | | | 54 | 54 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 39 | 1 (0)| 00:00:01 | | 55 | 53 | INDEX RANGE SCAN | IDX_MT_PAY_RECORD_FIN_2 | 1 | | 1 (0)| 00:00:01 | | 56 | 66 | FILTER | | | | | | | 57 | 65 | SORT GROUP BY | | 1 | 309 | 56871 (1)| 00:11:23 | | 58 | 64 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 119 | 1 (0)| 00:00:01 | | 59 | 63 | NESTED LOOPS | | 1 | 309 | 56869 (1)| 00:11:23 | | 60 | 61 | MERGE JOIN OUTER | | 1 | 190 | 56868 (1)| 00:11:23 | | 61 | 57 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_BIZ | 1 | 164 | 56867 (1)| 00:11:23 | | 62 | 56 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_SERIAL_DATE | 285K| | 209 (1)| 00:00:03 | | 63 | 60 | BUFFER SORT | | 1 | 26 | 1 (0)| 00:00:01 | | 64 | 59 | TABLE ACCESS BY INDEX ROWID| BS_CENTER | 1 | 26 | 1 (0)| 00:00:01 | | 65 | 58 | INDEX UNIQUE SCAN | PK_BS_CENTER | 1 | | 1 (0)| 00:00:01 | | 66 | 62 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------------------
從其執行計劃中可以看到最消耗成本的操作就是對IDX_PM_ACCOUNT_BIZ_SERIAL_DATE索引執行索引範圍掃描返回285K條記錄再回表查詢記錄的成本是56867，而整個SQL語句的成本是56871。再與BS_CENTER表執行排序合併連線，再與MT_BIZ_FIN執行巢狀迴圈連線，再執行group by操作。因為表BS_CENTER表只有10幾條記錄，且表中的center_id是主鍵，表PM_ACCOUNT_BIZ中center_id與其它列存在複合索引，理想的執行計劃應該是先訪問表BS_CENTER再與PM_ACCOUNT_BIZ表執行巢狀迴圈連線，再與MT_BIZ_FIN連線。而且上述SQL中的group by子句是可以去掉了，這裡group by真正的作用只起到了去掉重複記錄的作用，而PM_ACCOUNT_BIZ表的主鍵是account_id，在查詢列中，所以可以去掉這個group by 子句。
上面的SQL語句簡化如下形式：
select ...省略
from pm_account_biz a
where 1 = 1
and ('0' = 'A' or a.reimburse_flag ='0')
and ('00' = '00' or a.biz_type ='00')
and a.center_id in ('430722')
and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
and a.valid_flag = '1'
and a.deal_flag in ('0', '1', '2')
and (nvl(a.staff1_finish_flag, '0') ='0' and
nvl(a.staff2_finish_flag, '0') = '0' and
nvl(a.staff3_finish_flag, '0') = '0')
group by a.account_id,
a.hospital_id,
a.serial_no,
a.center_id,
a.icd,
a.biz_type,
a.treatment_type,
a.reimburse_flag,
a.corp_id,
a.corp_type_code,
a.indi_id,
a.pers_type,
a.biz_flag,
a.biz_flag_old,
a.disease_fee,
a.fin_date,
a.violate_flag,
a.violate,
a.MONTH_DECL_SN,
a.deal_flag,
a.audit_flag,
a.declare_icd_guide,
a.declare_audit_fee,
a.declare_audit_fee_cl,
a.first_icd_guide,
a.first_audit_fee,
a.first_audit_fee_cl,
a.second_icd_guide,
a.second_audit_fee,
a.second_audit_fee_cl) t1,
mt_biz_fin t2,
bs_center center
where t1.serial_no = t2.serial_no
AND t1.hospital_id = t2.hospital_id
AND t2.valid_flag = '1'
and t1.center_id = center.center_id(+)
order by t1.hospital_id, t1.serial_no
可以看到表mt_biz_fin，與bs_center可以合併到內嵌檢視t1中直接與表pm_account_biz進行表連線,前面已經說過了可以去掉group by子句,進行改寫後的SQL如下：
select t1.account_id,
t1.hospital_id,
(select hosp_level
from bs_hospital a
where t1.hospital_id = a.hospital_id
and a.valid_flag = '1') as hosp_level,
t1.serial_no,
t1.icd,
(select t4.disease
from bs_disease t4
where t1.icd = t4.icd(+)
and t4.center_id = nvl(t1.catalog_center, t1.center_id)) as disease,
t1.biz_type,
t1.treatment_type,
t1.disease_type,
t1.reimburse_flag,
t1.corp_id,
t1.corp_type_code,
t1.corp_name,
t1.indi_id,
t1.pers_type,
t1.center_id,
t1.district_code,
t1.out_type,
t1.biz_flag,
t1.biz_flag_old,
t1.name,
t1.idcard,
t1.sex,
t1.begin_date,
t1.end_date,
t1.fin_date,
t1.reg_date,
t1.disease_fee,
t1.violate_flag,
t1.patient_id,
t1.in_days,
decode(t1.allow_treat, '0', 0, 1) As allow_treat,
Decode(T1.Treatment_Type_Last,
'',
T1.Treatment_Type,
Decode(T1.Treatment_Type,
'120',
'120',
Decode(T1.Biz_Type,
'41',
'扣減已記帳金額',
'42',
'扣減已記帳金額',
T1.Treatment_Type_Last))) As Treatment_Type_Last,
t1.violate,
t1.pay_money,
t1.pay_fund,
t1.audit_money,
'0' as finish_flag,
t1.MONTH_DECL_SN,
t1.deal_flag,
t1.audit_deal_flag,
t1.declare_icd_guide,
t1.declare_audit_fee,
t1.declare_audit_fee_cl,
t1.first_icd_guide,
t1.first_audit_fee,
t1.first_audit_fee_cl,
t1.second_icd_guide,
t1.second_audit_fee,
t1.second_audit_fee_cl,
t1.PAY_otherfee,
t1.PAY_FUND003,
t1.PAY_FUND001,
t1.PAY_FUND201,
t1.PAY_FUND301,
t1.PAY_FUND999,
t1.PAY_FUND996,
t1.PAY_begin
from (select a.account_id,
a.hospital_id,
a.serial_no,
a.center_id,
a.icd,
a.biz_type,
a.treatment_type,
a.reimburse_flag,
a.corp_id,
a.corp_type_code,
t2.corp_name,
a.indi_id,
a.pers_type,
center.catalog_center,
t2.district_code,
decode(t2.center_id, t2.district_code, 0, 1) as out_type,
a.biz_flag,
a.biz_flag_old,
t2.name,
t2.idcard,
t2.sex,
t2.begin_date,
t2.end_date,
a.fin_date,
t2.reg_date,
a.violate_flag,
(select mbf.disease_type
from mt_biz_fin mbf
where mbf.valid_flag = '1'
and mbf.hospital_id = a.hospital_id
and mbf.serial_no = a.serial_no) disease_type,
(Select aa1.allow_treat
From pm_account_scene aa1
where aa1.hospital_id = a.hospital_id
and aa1.serial_no = a.serial_no
and aa1.valid_flag = '1') as allow_treat,
(select distinct bb1.patient_id
from mt_biz_fin bb1
where bb1.hospital_id = a.hospital_id
and bb1.serial_no = a.serial_no
and bb1.valid_flag = '1') as patient_id,
(select distinct bb1.in_days
from mt_biz_fin bb1
where bb1.hospital_id = a.hospital_id
and bb1.serial_no = a.serial_no
and bb1.valid_flag = '1') as in_days,
(select Max(audit_treat_value)
from pm_treat_audit aa
where aa.account_id = a.account_id
and aa.audit_staff_id = '2208'
and aa.audit_phase = '1'
and aa.valid_flag = '1') as treatment_type_last,
a.violate,
nvl((SELECT sum(pm_account_fund.pay_money)
FROM pm_account_fund
WHERE a.account_id = pm_account_fund.account_id
AND pm_account_fund.valid_flag = '1'),
0) as pay_money,
nvl((SELECT sum(pm_account_fund.pay_money)
FROM pm_account_fund
WHERE a.account_id = pm_account_fund.account_id
AND pm_account_fund.valid_flag = '1'
AND pm_account_fund.fund_id not in
('003', '999', '996', '997')),
0) as pay_fund,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '003'),
0) AS PAY_FUND003,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID in ('801', '001')),
0) AS PAY_FUND001,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '201'),
0) AS PAY_FUND201,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '301'),
0) AS PAY_FUND301,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '999'),
0) AS PAY_FUND999,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID = '996'),
0) AS PAY_FUND996,
NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
FROM PM_ACCOUNT_FUND
WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
AND PM_ACCOUNT_FUND.FUND_ID not in
('801', '001', '003', '201', '999')),
0) AS PAY_otherfee,
NVL((SELECT SUM(mprf.real_pay)
FROM mt_pay_record_fin mprf
WHERE A.hospital_id = mprf.hospital_id
AND A.serial_no = mprf.serial_no
AND mprf.policy_item_code = 'S01'),
0) AS PAY_begin,
(select nvl(sum(b.AUDIT_MONEY), 0)
from pm_fee_audit b
where a.account_id = b.account_id
and b.audit_staff_id = '2208'
and b.AUDIT_TYPE = '1'
and b.AUDIT_PHASE = '1'
and b.valid_flag = '1') as audit_money,
a.MONTH_DECL_SN,
a.deal_flag,
decode(nvl(a.disease_fee, 0),
0,
nvl((select nvl(max(ration), 0)
from pm_ration pr
where pr.center_id = a.center_id
and pr.hospital_id = a.hospital_id
and pr.treatment_type = a.treatment_type
and pr.year = to_char(a.fin_date, 'yyyy')
and (decode(a.pers_type,
'21',
'21',
'22',
'21',
'23',
'21',
'24',
'21',
'41',
'21',
'3',
'12',
'4',
'12',
'5',
'12',
'7',
'12',
'2') = pr.insr_no or
pr.insr_no = '0')
and rownum < 2
and pr.biz_flag = a.biz_flag),
0),
a.disease_fee) as disease_fee,
a.audit_flag as audit_deal_flag,
a.declare_icd_guide as declare_icd_guide,
a.declare_audit_fee as declare_audit_fee,
a.declare_audit_fee_cl as declare_audit_fee_cl,
a.first_icd_guide as first_icd_guide,
a.first_audit_fee as first_audit_fee,
a.first_audit_fee_cl as first_audit_fee_cl,
decode(a.biz_type,
'12',
decode(a.biz_flag_old, 5, '4304001', a.second_icd_guide),
a.second_icd_guide) second_icd_guide,
a.second_audit_fee as second_audit_fee,
a.second_audit_fee_cl as second_audit_fee_cl
from bs_center center,pm_account_biz a,mt_biz_fin t2
where 1 = 1
and ('0' = 'A' or a.reimburse_flag ='0')
and ('00' = '00' or a.biz_type ='00')
and a.center_id in ('430701')
and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
and a.valid_flag = '1'
and a.deal_flag in ('0', '1', '2')
and (nvl(a.staff1_finish_flag, '0') ='0' and
nvl(a.staff2_finish_flag, '0') = '0' and
nvl(a.staff3_finish_flag, '0') = '0')
and a.serial_no = t2.serial_no
AND a.hospital_id = t2.hospital_id
AND t2.valid_flag = '1'
and a.center_id = center.center_id(+)
and center.center_id in ('430701')
order by a.hospital_id, a.serial_no
)t1
來測試執行一次,但是執行了10分鐘還是沒有執行完，其執行計劃如下:
------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 3 | 855 | 24453 (1)| 00:04:54 | |* 1 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 16 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | 1 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | 1 (0)| 00:00:01 | |* 5 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 7 | SORT AGGREGATE | | 1 | 34 | | | |* 8 | COUNT STOPKEY | | | | | | |* 9 | TABLE ACCESS BY INDEX ROWID | PM_RATION | 1 | 34 | 1 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | IDX_PM_RATION_1 | 1 | | 1 (0)| 00:00:01 | | 11 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 | |* 12 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | |* 13 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | | 14 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 | |* 15 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 | |* 16 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | |* 17 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_SCENE | 1 | 28 | 1 (0)| 00:00:01 | |* 18 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_SCENE_1 | 1 | | 1 (0)| 00:00:01 | | 19 | SORT AGGREGATE | | 1 | 37 | | | |* 20 | TABLE ACCESS BY INDEX ROWID | PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 | |* 21 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 | | 22 | SORT AGGREGATE | | 1 | 37 | | | |* 23 | TABLE ACCESS BY INDEX ROWID | PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 | |* 24 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 | | 25 | SORT AGGREGATE | | 1 | 12 | | | |* 26 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 12 | 1 (0)| 00:00:01 | |* 27 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND1 | 1 | | 1 (0)| 00:00:01 | | 28 | SORT AGGREGATE | | 1 | 16 | | | |* 29 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 30 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 31 | SORT AGGREGATE | | 1 | 21 | | | |* 32 | TABLE ACCESS BY INDEX ROWID | PM_FEE_AUDIT | 1 | 21 | 1 (0)| 00:00:01 | |* 33 | INDEX RANGE SCAN | PK_PM_FEE_AUDIT | 1 | | 1 (0)| 00:00:01 | | 34 | SORT AGGREGATE | | 1 | 16 | | | |* 35 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 36 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 37 | SORT AGGREGATE | | 1 | 16 | | | |* 38 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 39 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 40 | SORT AGGREGATE | | 1 | 16 | | | | 41 | INLIST ITERATOR | | | | | | |* 42 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 43 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 44 | SORT AGGREGATE | | 1 | 16 | | | |* 45 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 46 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 47 | SORT AGGREGATE | | 1 | 16 | | | |* 48 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 49 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 50 | SORT AGGREGATE | | 1 | 16 | | | |* 51 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 52 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 53 | SORT AGGREGATE | | 1 | 16 | | | |* 54 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 | |* 55 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 | | 56 | SORT AGGREGATE | | 1 | 39 | | | | 57 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 39 | 1 (0)| 00:00:01 | |* 58 | INDEX RANGE SCAN | IDX_MT_PAY_RECORD_FIN_2 | 1 | | 1 (0)| 00:00:01 | | 59 | SORT ORDER BY | | 3 | 855 | 24453 (1)| 00:04:54 | |* 60 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 107 | 1 (0)| 00:00:01 | | 61 | NESTED LOOPS | | 3 | 855 | 24452 (1)| 00:04:54 | | 62 | NESTED LOOPS | | 3 | 534 | 24451 (1)| 00:04:54 | | 63 | TABLE ACCESS BY INDEX ROWID| BS_CENTER | 1 | 14 | 1 (0)| 00:00:01 | |* 64 | INDEX UNIQUE SCAN | PK_BS_CENTER | 1 | | 1 (0)| 00:00:01 | |* 65 | TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ | 3 | 492 | 24450 (1)| 00:04:54 | |* 66 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_SERIAL_DATE | 118K| | 29 (7)| 00:00:01 | |* 67 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------------------
其執行計劃中現在是選訪問表BS_CENTER後再與PM_ACCOUNT_BIZ執行巢狀迴圈連線，與我們設想的一樣，這裡慢的主要原因是因為要對錶連線之後的記錄執行order by a.hospital_id, a.serial_no操作。PM_ACCOUNT_BIZ表的查詢條件中有and a.center_id in ('430701') and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))，而在CENTER_ID與FIN_DATE列上有存在複合索引。索引資訊如下所示，在建立索引時，索引列的記錄預設是按升序來儲存的，而查詢中要進行升序操作的列就是hospital_id,serial_no，而這兩列已經存在複合索引IDX_PM_ACCOUNT_BIZ_SERIAL_NO中，而該索引中首列是CENTER_ID，我們只要把fin_date列加入這個複合索引中就可以讓CBO在執行查詢時使用該索引且能避免排序操作，而在IDX_PM_ACCOUNT_BIZ_SERIAL_DATE索引的有兩列分別是CENTER_ID與FIN_DATE所以可以將這兩個索引合併成一個新的索引（create index idx_pm_account_biz_center_id on pm_account_biz(center_id,hospital_id,serial_no,fin_date),並刪除原先的這兩個索引。
SQL> select a.index_name,a.column_name,a.column_position 2 from user_ind_columns a where a.table_name='PM_ACCOUNT_BIZ' and a.index_name='IDX_PM_ACCOUNT_BIZ_SERIAL_NO'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------------------------------------------------------------------- --------------- IDX_PM_ACCOUNT_BIZ_SERIAL_NO CENTER_ID 1 IDX_PM_ACCOUNT_BIZ_SERIAL_NO HOSPITAL_ID 2 IDX_PM_ACCOUNT_BIZ_SERIAL_NO SERIAL_NO 3 SQL> select a.index_name,a.column_name,a.column_position 2 from user_ind_columns a where a.table_name='PM_ACCOUNT_BIZ' and a.index_name='IDX_PM_ACCOUNT_BIZ_SERIAL_DATE'; INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ -------------------------------------------------------------------------------- --------------- IDX_PM_ACCOUNT_BIZ_SERIAL_DATE CENTER_ID 1 IDX_PM_ACCOUNT_BIZ_SERIAL_DATE FIN_DATE
我們在建立新索引後，執行修改後的SQL語句：
SQL> set timing on
SQL> set autotrace traceonly
SQL> select t1.account_id,
2 t1.hospital_id,
3 (select hosp_level
4 from bs_hospital a
5 where t1.hospital_id = a.hospital_id
6 and a.valid_flag = '1') as hosp_level,
7 t1.serial_no,
8 t1.icd,
9 (select t4.disease
10 from bs_disease t4
11 where t1.icd = t4.icd(+)
12 and t4.center_id = nvl(t1.catalog_center, t1.center_id)) as disease,
13 t1.biz_type,
14 t1.treatment_type,
15 t1.disease_type,
16 t1.reimburse_flag,
17 t1.corp_id,
18 t1.corp_type_code,
19 t1.corp_name,
20 t1.indi_id,
21 t1.pers_type,
22 t1.center_id,
23 t1.district_code,
24 t1.out_type,
25 t1.biz_flag,
26 t1.biz_flag_old,
27 t1.name,
28 t1.idcard,
29 t1.sex,
30 t1.begin_date,
31 t1.end_date,
32 t1.fin_date,
33 t1.reg_date,
34 t1.disease_fee,
35 t1.violate_flag,
36 t1.patient_id,
37 t1.in_days,
38 decode(t1.allow_treat, '0', 0, 1) As allow_treat,
39 Decode(T1.Treatment_Type_Last,
40 '',
41 T1.Treatment_Type,
42 Decode(T1.Treatment_Type,
43 '120',
44 '120',
45 Decode(T1.Biz_Type,
46 '41',
47 '扣減已記帳金額',
48 '42',
49 '扣減已記帳金額',
50 T1.Treatment_Type_Last))) As Treatment_Type_Last,
51 t1.violate,
52 t1.pay_money,
53 t1.pay_fund,
54 t1.audit_money,
55 '0' as finish_flag,
56 t1.MONTH_DECL_SN,
57 t1.deal_flag,
58 t1.audit_deal_flag,
59 t1.declare_icd_guide,
60 t1.declare_audit_fee,
61 t1.declare_audit_fee_cl,
62 t1.first_icd_guide,
63 t1.first_audit_fee,
64 t1.first_audit_fee_cl,
65 t1.second_icd_guide,
66 t1.second_audit_fee,
67 t1.second_audit_fee_cl,
68 t1.PAY_otherfee,
69 t1.PAY_FUND003,
70 t1.PAY_FUND001,
71 t1.PAY_FUND201,
72 t1.PAY_FUND301,
73 t1.PAY_FUND999,
74 t1.PAY_FUND996,
75 t1.PAY_begin
76 from (select a.account_id,
77 a.hospital_id,
78 a.serial_no,
79 a.center_id,
80 a.icd,
81 a.biz_type,
82 a.treatment_type,
83 a.reimburse_flag,
84 a.corp_id,
85 a.corp_type_code,
86 t2.corp_name,
87 a.indi_id,
88 a.pers_type,
89 center.catalog_center,
90 t2.district_code,
91 decode(t2.center_id, t2.district_code, 0, 1) as out_type,
92 a.biz_flag,
93 a.biz_flag_old,
94 t2.name,
95 t2.idcard,
96 t2.sex,
97 t2.begin_date,
98 t2.end_date,
99 a.fin_date,
100 t2.reg_date,
101 a.violate_flag,
102 (select mbf.disease_type
103 from mt_biz_fin mbf
104 where mbf.valid_flag = '1'
105 and mbf.hospital_id = a.hospital_id
106 and mbf.serial_no = a.serial_no) disease_type,
107 (Select aa1.allow_treat
108 From pm_account_scene aa1
109 where aa1.hospital_id = a.hospital_id
110 and aa1.serial_no = a.serial_no
111 and aa1.valid_flag = '1') as allow_treat,
112 (select distinct bb1.patient_id
113 from mt_biz_fin bb1
114 where bb1.hospital_id = a.hospital_id
115 and bb1.serial_no = a.serial_no
116 and bb1.valid_flag = '1') as patient_id,
117 (select distinct bb1.in_days
118 from mt_biz_fin bb1
119 where bb1.hospital_id = a.hospital_id
120 and bb1.serial_no = a.serial_no
121 and bb1.valid_flag = '1') as in_days,
122 (select Max(audit_treat_value)
123 from pm_treat_audit aa
124 where aa.account_id = a.account_id
125 and aa.audit_staff_id = '2208'
126 and aa.audit_phase = '1'
127 and aa.valid_flag = '1') as treatment_type_last,
128 a.violate,
129 nvl((SELECT sum(pm_account_fund.pay_money)
130 FROM pm_account_fund
131 WHERE a.account_id = pm_account_fund.account_id
132 AND pm_account_fund.valid_flag = '1'),
133 0) as pay_money,
134 nvl((SELECT sum(pm_account_fund.pay_money)
135 FROM pm_account_fund
136 WHERE a.account_id = pm_account_fund.account_id
137 AND pm_account_fund.valid_flag = '1'
138 AND pm_account_fund.fund_id not in
139 ('003', '999', '996', '997')),
140 0) as pay_fund,
141 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
142 FROM PM_ACCOUNT_FUND
143 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
144 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
145 AND PM_ACCOUNT_FUND.FUND_ID = '003'),
146 0) AS PAY_FUND003,
147 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
148 FROM PM_ACCOUNT_FUND
149 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
150 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
151 AND PM_ACCOUNT_FUND.FUND_ID in ('801', '001')),
152 0) AS PAY_FUND001,
153 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
154 FROM PM_ACCOUNT_FUND
155 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
156 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
157 AND PM_ACCOUNT_FUND.FUND_ID = '201'),
158 0) AS PAY_FUND201,
159 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
160 FROM PM_ACCOUNT_FUND
161 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
162 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
163 AND PM_ACCOUNT_FUND.FUND_ID = '301'),
164 0) AS PAY_FUND301,
165 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
166 FROM PM_ACCOUNT_FUND
167 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
168 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
169 AND PM_ACCOUNT_FUND.FUND_ID = '999'),
170 0) AS PAY_FUND999,
171 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
172 FROM PM_ACCOUNT_FUND
173 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
174 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
175 AND PM_ACCOUNT_FUND.FUND_ID = '996'),
176 0) AS PAY_FUND996,
177 NVL((SELECT SUM(PM_ACCOUNT_FUND.PAY_MONEY)
178 FROM PM_ACCOUNT_FUND
179 WHERE A.ACCOUNT_ID = PM_ACCOUNT_FUND.ACCOUNT_ID
180 AND PM_ACCOUNT_FUND.VALID_FLAG = '1'
181 AND PM_ACCOUNT_FUND.FUND_ID not in
182 ('801', '001', '003', '201', '999')),
183 0) AS PAY_otherfee,
184 NVL((SELECT SUM(mprf.real_pay)
185 FROM mt_pay_record_fin mprf
186 WHERE A.hospital_id = mprf.hospital_id
187 AND A.serial_no = mprf.serial_no
188 AND mprf.policy_item_code = 'S01'),
189 0) AS PAY_begin,
190 (select nvl(sum(b.AUDIT_MONEY), 0)
191 from pm_fee_audit b
192 where a.account_id = b.account_id
193 and b.audit_staff_id = '2208'
194 and b.AUDIT_TYPE = '1'
195 and b.AUDIT_PHASE = '1'
196 and b.valid_flag = '1') as audit_money,
197 a.MONTH_DECL_SN,
198 a.deal_flag,
199 decode(nvl(a.disease_fee, 0),
200 0,
201 nvl((select nvl(max(ration), 0)
202 from pm_ration pr
203 where pr.center_id = a.center_id
204 and pr.hospital_id = a.hospital_id
205 and pr.treatment_type = a.treatment_type
206 and pr.year = to_char(a.fin_date, 'yyyy')
207 and (decode(a.pers_type,
208 '21',
209 '21',
210 '22',
211 '21',
212 '23',
213 '21',
214 '24',
215 '21',
216 '41',
217 '21',
218 '3',
219 '12',
220 '4',
221 '12',
222 '5',
223 '12',
224 '7',
225 '12',
226 '2') = pr.insr_no or
227 pr.insr_no = '0')
228 and rownum < 2
229 and pr.biz_flag = a.biz_flag),
230 0),
231 a.disease_fee) as disease_fee,
232 a.audit_flag as audit_deal_flag,
233 a.declare_icd_guide as declare_icd_guide,
234 a.declare_audit_fee as declare_audit_fee,
235 a.declare_audit_fee_cl as declare_audit_fee_cl,
236 a.first_icd_guide as first_icd_guide,
237 a.first_audit_fee as first_audit_fee,
238 a.first_audit_fee_cl as first_audit_fee_cl,
239 decode(a.biz_type,
240 '12',
241 decode(a.biz_flag_old, 5, '4304001', a.second_icd_guide),
242 a.second_icd_guide) second_icd_guide,
243 a.second_audit_fee as second_audit_fee,
244 a.second_audit_fee_cl as second_audit_fee_cl
245 from bs_center center,pm_account_biz a,mt_biz_fin t2
246 where 1 = 1
247 and ('0' = 'A' or a.reimburse_flag ='0')
248 and ('00' = '00' or a.biz_type ='00')
249 and a.center_id in ('430701')
250 and (a.fin_date between to_date('2014-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
251 to_date('2014-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
252 and a.valid_flag = '1'
253 and a.deal_flag in ('0', '1', '2')
254 and (nvl(a.staff1_finish_flag, '0') ='0' and
255 nvl(a.staff2_finish_flag, '0') = '0' and
256 nvl(a.staff3_finish_flag, '0') = '0')
257 and a.serial_no = t2.serial_no
258 AND a.hospital_id = t2.hospital_id
259 AND t2.valid_flag = '1'
260 and a.center_id = center.center_id(+)
261 and center.center_id in ('430701')
262 order by a.hospital_id, a.serial_no
263 )t1
264 ;
189998 rows selected.
Elapsed: 00:01:37.15
Execution Plan
----------------------------------------------------------
Plan hash value: 1778792342
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 855 | 20157 (1)| 00:04:02 |
|* 1 | TABLE ACCESS BY INDEX ROWID | BS_HOSPITAL | 1 | 16 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_BS_HOSPITAL | 1 | | 1 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | BS_DISEASE | 1 | 33 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | INX_BS_DISEASE_01 | 1 | | 1 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | 34 | | |
|* 8 | COUNT STOPKEY | | | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | PM_RATION | 1 | 34 | 1 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IDX_PM_RATION_1 | 1 | | 1 (0)| 00:00:01 |
| 11 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 |
| 14 | HASH UNIQUE | | 1 | 26 | 2 (50)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 26 | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_SCENE | 1 | 28 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_SCENE_1 | 1 | | 1 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | 37 | | |
|* 20 | TABLE ACCESS BY INDEX ROWID | PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 |
| 22 | SORT AGGREGATE | | 1 | 37 | | |
|* 23 | TABLE ACCESS BY INDEX ROWID| PM_TREAT_AUDIT | 1 | 37 | 1 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN | PK_PM_TREAT_AUDIT | 1 | | 1 (0)| 00:00:01 |
| 25 | SORT AGGREGATE | | 1 | 12 | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 12 | 1 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND1 | 1 | | 1 (0)| 00:00:01 |
| 28 | SORT AGGREGATE | | 1 | 16 | | |
|* 29 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 30 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 31 | SORT AGGREGATE | | 1 | 21 | | |
|* 32 | TABLE ACCESS BY INDEX ROWID | PM_FEE_AUDIT | 1 | 21 | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | PK_PM_FEE_AUDIT | 1 | | 1 (0)| 00:00:01 |
| 34 | SORT AGGREGATE | | 1 | 16 | | |
|* 35 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 36 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 37 | SORT AGGREGATE | | 1 | 16 | | |
|* 38 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 39 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 40 | SORT AGGREGATE | | 1 | 16 | | |
| 41 | INLIST ITERATOR | | | | | |
|* 42 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 44 | SORT AGGREGATE | | 1 | 16 | | |
|* 45 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 47 | SORT AGGREGATE | | 1 | 16 | | |
|* 48 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 50 | SORT AGGREGATE | | 1 | 16 | | |
|* 51 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 53 | SORT AGGREGATE | | 1 | 16 | | |
|* 54 | TABLE ACCESS BY INDEX ROWID | PM_ACCOUNT_FUND | 1 | 16 | 1 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN | PK_PM_ACCOUNT_FUND | 1 | | 1 (0)| 00:00:01 |
| 56 | SORT AGGREGATE | | 1 | 39 | | |
| 57 | TABLE ACCESS BY INDEX ROWID | MT_PAY_RECORD_FIN | 1 | 39 | 1 (0)| 00:00:01 |
|* 58 | INDEX RANGE SCAN | IDX_MT_PAY_RECORD_FIN_2 | 1 | | 1 (0)| 00:00:01 |
|* 59 | TABLE ACCESS BY INDEX ROWID | MT_BIZ_FIN | 1 | 107 | 1 (0)| 00:00:01 |
| 60 | NESTED LOOPS | | 3 | 855 | 20157 (1)| 00:04:02 |
| 61 | NESTED LOOPS | | 3 | 534 | 20156 (1)| 00:04:02 |
| 62 | TABLE ACCESS BY INDEX ROWID| BS_CENTER | 1 | 14 | 1 (0)| 00:00:01 |
|* 63 | INDEX UNIQUE SCAN | PK_BS_CENTER | 1 | | 1 (0)| 00:00:01 |
|* 64 | TABLE ACCESS BY INDEX ROWID| PM_ACCOUNT_BIZ | 3 | 492 | 20155 (1)| 00:04:02 |
|* 65 | INDEX RANGE SCAN | IDX_PM_ACCOUNT_BIZ_CENTER_ID | 118K| | 178 (4)| 00:00:03 |
|* 66 | INDEX RANGE SCAN | PK_MT_BIZ_FIN | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."VALID_FLAG"='1')
2 - access("A"."HOSPITAL_ID"=:B1)
4 - access("T4"."CENTER_ID"=NVL(:B1,:B2) AND "T4"."ICD"=:B3)
5 - filter("MBF"."VALID_FLAG"='1')
6 - access("MBF"."HOSPITAL_ID"=:B1 AND "MBF"."SERIAL_NO"=:B2)
8 - filter(ROWNUM<2)
9 - filter(("PR"."INSR_NO"=TO_NUMBER(DECODE(:B1,'21','21','22','21','23','21','24','21','41','21','3
','12','4','12','5','12','7','12','2')) OR "PR"."INSR_NO"=0) AND TO_NUMBER("PR"."BIZ_FLAG")=:B2)
10 - access("PR"."YEAR"=TO_NUMBER(TO_CHAR(:B1,'yyyy')) AND "PR"."HOSPITAL_ID"=:B2 AND
"PR"."TREATMENT_TYPE"=:B3 AND "PR"."CENTER_ID"=:B4)
filter("PR"."TREATMENT_TYPE"=:B1 AND "PR"."CENTER_ID"=:B2)
12 - filter("BB1"."VALID_FLAG"='1')
13 - access("BB1"."HOSPITAL_ID"=:B1 AND "BB1"."SERIAL_NO"=:B2)
15 - filter("BB1"."VALID_FLAG"='1')
16 - access("BB1"."HOSPITAL_ID"=:B1 AND "BB1"."SERIAL_NO"=:B2)
17 - filter("AA1"."VALID_FLAG"='1')
18 - access("AA1"."HOSPITAL_ID"=:B1 AND "AA1"."SERIAL_NO"=:B2)
20 - filter("AA"."VALID_FLAG"='1')
21 - access("AA"."ACCOUNT_ID"=:B1 AND "AA"."AUDIT_PHASE"='1' AND "AA"."AUDIT_STAFF_ID"='2208')
filter("AA"."AUDIT_STAFF_ID"='2208' AND "AA"."AUDIT_PHASE"='1')
23 - filter("AA"."VALID_FLAG"='1')
24 - access("AA"."ACCOUNT_ID"=:B1 AND "AA"."AUDIT_PHASE"='1' AND "AA"."AUDIT_STAFF_ID"='2208')
filter("AA"."AUDIT_STAFF_ID"='2208' AND "AA"."AUDIT_PHASE"='1')
26 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
27 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1)
29 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
30 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1)
filter("PM_ACCOUNT_FUND"."FUND_ID"<>'003' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'999' AND
"PM_ACCOUNT_FUND"."FUND_ID"<>'996' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'997')
32 - filter("B"."VALID_FLAG"='1')
33 - access("B"."ACCOUNT_ID"=:B1 AND "B"."AUDIT_TYPE"='1' AND "B"."AUDIT_PHASE"='1' AND
"B"."AUDIT_STAFF_ID"='2208')
filter("B"."AUDIT_STAFF_ID"='2208')
35 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
36 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1)
filter("PM_ACCOUNT_FUND"."FUND_ID"<>'003' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'999' AND
"PM_ACCOUNT_FUND"."FUND_ID"<>'001' AND "PM_ACCOUNT_FUND"."FUND_ID"<>'801' AND
"PM_ACCOUNT_FUND"."FUND_ID"<>'201')
38 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
39 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='003')
42 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
43 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND ("PM_ACCOUNT_FUND"."FUND_ID"='001' OR
"PM_ACCOUNT_FUND"."FUND_ID"='801'))
45 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
46 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='201')
48 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
49 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='301')
51 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
52 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='999')
54 - filter("PM_ACCOUNT_FUND"."VALID_FLAG"='1')
55 - access("PM_ACCOUNT_FUND"."ACCOUNT_ID"=:B1 AND "PM_ACCOUNT_FUND"."FUND_ID"='996')
58 - access("MPRF"."HOSPITAL_ID"=:B1 AND "MPRF"."SERIAL_NO"=:B2 AND "MPRF"."POLICY_ITEM_CODE"='S01')
59 - filter("T2"."VALID_FLAG"='1')
63 - access("CENTER"."CENTER_ID"='430701')
64 - filter(NVL("A"."STAFF2_FINISH_FLAG",'0')='0' AND NVL("A"."STAFF1_FINISH_FLAG",'0')='0' AND
NVL("A"."STAFF3_FINISH_FLAG",'0')='0' AND "A"."REIMBURSE_FLAG"='0' AND "A"."VALID_FLAG"='1' AND
("A"."DEAL_FLAG"='0' OR "A"."DEAL_FLAG"='1' OR "A"."DEAL_FLAG"='2'))
65 - access("A"."CENTER_ID"="CENTER"."CENTER_ID" AND "A"."FIN_DATE">=TO_DATE(' 2014-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "A"."FIN_DATE"< =TO_DATE(' 2014-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss')) filter("A"."FIN_DATE">=TO_DATE(' 2014-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A"."CENTER_ID"='430701' AND "A"."FIN_DATE"< =TO_DATE(' 2014-12-31 23:59:59', 'syyyy-mm-dd hh24:mi:ss'))
66 - access("A"."HOSPITAL_ID"="T2"."HOSPITAL_ID" AND "A"."SERIAL_NO"="T2"."SERIAL_NO")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2319157 consistent gets
8301 physical reads
576 redo size
2809640 bytes sent via SQL*Net to client
24709 bytes received via SQL*Net from client
1335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
189998 rows processed
從上面的實際執行情況可以看到返回了接近19萬條記錄花費的時間是1分37秒，也就是97秒與3286秒，已經是幾十倍的提升了。
小結:這裡最佳化的方法是透過修改SQL語句，改變表連線的方法，去掉不必要的分組(group by)操作，建立來合理的複合索引來避免排序(order by）來達到效能的提升。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25723371/viewspace-1397984/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一個文章表的 MySQL 索引怎麼建立合理MySql索引
- 建立索引——提高SQL Server效能索引SQLServer
- 【Oracle】-【建立索引】-建立索引的操作原理與一些體會Oracle索引
- 優化案例--重建索引引發的sql效能問題優化索引SQL
- 如何合理使用 CPU 管理策略，提升容器效能？
- 用SQL建立索引的方法步驟SQL索引
- 快速定位不合理的索引——MySQL索引調優索引MySql
- SQL優化案例-正確的使用索引（二）SQL優化索引
- 【SQL 學習】排序問題之order by與索引排序SQL排序索引
- oracle order by索引是否使用的情況Oracle索引
- 建立聯合函式索引解決top sql效能問題函式索引SQL
- Oracle優化案例-join列索引缺失導致的sql效能問題（二十六）Oracle優化索引SQL
- MySQL系列-- 3.建立高效能的索引MySql索引
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- 建立索引調整sql的執行計劃索引SQL
- SQL SERVER建立索引需要注意的問題SQLServer索引
- SQL最佳化案例-正確的使用索引（二）SQL索引
- PostgreSQLCREATEINDEXCONCURRENTLY的原理以及哪些操作可能堵塞索引的建立SQLIndex索引
- 【MySQL】效能優化之 order by (一)MySql優化
- SQLT 最佳化SQL 用複合索引代替單列索引的案例SQL索引
- mysql資料庫索引的建立以及效能測試MySql資料庫索引
- 避免動態SQL(一)SQL
- SQL提取當前庫內索引的建立語句SQL索引
- 一個效能優化的案例優化
- 一個複合索引的優化案例索引優化
- SQL效能優化案例分析SQL優化
- 案例 - EBS SQL效能診斷SQL
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- order by在SQL Server與Oracle中的一個區別SQLServerOracle
- 一頓騷操作版本號比較效能提升300%
- MySQL 的 sql_mode 合理設定MySql
- MySQL的sql_mode合理設定MySql
- mysql 的sql_mode合理設定MySql
- 從效能的角度談SQL Server聚集索引鍵的選擇SQLServer索引
- SQL的order by 高階使用·指定一條資訊排列第一條SQL
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引
- SQL優化案例一則--複合索引沒有被使用SQL優化索引
- sql優化案例一:使用了表示式不會使用索引SQL優化索引