SELECT count(1) from(
SELECT R.PERSON_INFO_ID AS HISPATIENTID,
NVL(R.OPC_REGISTRATION_NO, '-') AS RECNUMBER,
1 AS NUMBER0,
H.ORDERED_DATE AS CHARGEORDERDATE,
DT.ID AS ORDERDEPTCODE,
DT.DEPARTMENT_CHINESE_NAME AS ORDERDEPTNAME,
E.EMPLOYEE_NO AS ORDEREMPCODE,
H.CHARGE_DATE AS CHARGEEXECDATE,
DT0.ID AS EXECDEPTCODE,
DT0.DEPARTMENT_CHINESE_NAME AS EXECDEPTNAME,
E0.EMPLOYEE_NO AS EXECEMPCODE,
FL.S_MZFYFL_DM AS CHARGETYPECODE,
FL.S_MZFYFL_CMC AS CHARGETYPENAME,
DI.DIAG_ITEM_CODE AS CHARGEITEMCODE,
DI.DIAG_ITEM_CNAME AS CHARGEITEMNAME,
ROUND(D.TOTAL_QTY, 2) AS NUM,
D.TOTAL_AMT / DECODE(D.TOTAL_QTY, 0, 1, D.TOTAL_QTY) AS PRICE,
D.TOTAL_AMT AS AMOUNT,
TY.ID AS PATIENTTYPECODE
FROM OPC_DIAG_SERVICE_H_CHARGE H
INNER JOIN OPC_DIAG_SERVICE_D_CHARGE D
ON D.DIAG_SERVICE_H_CHARGE_ID = H.ID
LEFT JOIN HRA00_DEPARTMENT DT
ON DT.ID = H.ORDERED_DEPT_ID
LEFT JOIN HRA00_DEPARTMENT DT0
ON DT0.ID = D.EXEC_DEPT_ID
LEFT JOIN PUB_MZFYFL FL
ON FL.S_MZFYFL_DM = D.OUT_PATIENT_CHARGE_TYPE
LEFT JOIN PUB_DIAGNOSIS_ITEM_DICT DI
ON DI.ID = D.DIAG_ITEM_ID
LEFT JOIN OPC_REGISTRATION R
ON R.ID = H.OPC_REGISTRATION_ID
LEFT JOIN PUB_PATIENT_TYPE TY
ON TY.ID = R.PATIENT_TYPE_ID
LEFT JOIN HRM_EMPLOYEE E
ON E.ID = H.ORDERED_DOCTOR_ID
LEFT JOIN HRM_EMPLOYEE E0
ON E.ID = D.EXEC_PERSON_ID
WHERE H.CHARGE_DATE BETWEEN DATE '2020-06-01' AND DATE '2020-07-01'
AND LENGTH(D.INVOICE_NO) > 3
AND H.HEALTH_SERVICE_ORG_ID = 'RSS20140108000000001')
解釋計劃執行
Description 訪問謂詞 物件名稱 耗費 基數 時間 位元組
SELECT STATEMENT, GOAL = ALL_ROWS 1,512,610 1 18,152 127
SORT AGGREGATE 1 127
NESTED LOOPS OUTER 1,512,610 571,310,599 18,152 72,556,446,073
HASH JOIN RIGHT OUTER "E"."ID"(+)="H"."ORDERED_DOCTOR_ID" 285,895 203,748 3,431 25,875,996
INDEX FAST FULL SCAN PK_HRM_EMPLOYEE 6 2,804 1 44,864
HASH JOIN "D"."DIAG_SERVICE_H_CHARGE_ID"="H"."ID" 285,887 203,748 3,431 22,616,028
TABLE ACCESS BY INDEX ROWID OPC_DIAG_SERVICE_H_CHARGE 36,727 96,844 441 8,231,740
INDEX RANGE SCAN "H"."CHARGE_DATE">=TIMESTAMP' 2020-06-01 00:00:00' AND "H"."CHARGE_DATE"<=TIMESTAMP' 2020-07-01 00:00:00' OPCDSH_CHARGE_INDEX 801 116,739 10
TABLE ACCESS FULL OPC_DIAG_SERVICE_D_CHARGE 199,509 27,244,882 2,395 708,366,932
VIEW 6 2,804 1
FILTER
INDEX FAST FULL SCAN PK_HRM_EMPLOYEE 6 2,804 1
uj5u.com熱心網友回復：
總共30w 資料 , 查詢個總量要35秒左右 , 太慢了 ,怎么優化以下 !!轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/249156.html
標籤：開發
上一篇：ORA-12545: Connect failed because target host or object does not exist
下一篇：資料主鍵id重新編號