SELECT count (1) the from (
The SELECT R.P ERSON_INFO_ID AS HISPATIENTID, 
NVL (R.O PC_REGISTRATION_NO, '-') AS RECNUMBER, 
1 the AS NUMBER0, 
H.O RDERED_DATE AS CHARGEORDERDATE, 
DT. ID AS ORDERDEPTCODE, 
DT DEPARTMENT_CHINESE_NAME AS ORDERDEPTNAME, 
E.E MPLOYEE_NO AS ORDEREMPCODE, 
H.C HARGE_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 (which OTAL_QTY, 2) AS the NUM, 
Which OTAL_AMT/DECODE (which OTAL_QTY, 0, 1, which OTAL_QTY) AS PRICE, 
Which OTAL_AMT AS AMOUNT, 
TY. ID AS PATIENTTYPECODE 
The FROM OPC_DIAG_SERVICE_H_CHARGE H 
INNER JOIN OPC_DIAG_SERVICE_D_CHARGE D 
ON D.D IAG_SERVICE_H_CHARGE_ID=H.I D 
LEFT the JOIN HRA00_DEPARTMENT DT 
ON DT. ID=H.O RDERED_DEPT_ID 
LEFT the JOIN HRA00_DEPARTMENT DT0 
ON DT0. ID=D.E XEC_DEPT_ID 
LEFT the JOIN PUB_MZFYFL FL 
ON FL. S_MZFYFL_DM=D.O UT_PATIENT_CHARGE_TYPE 
LEFT the JOIN PUB_DIAGNOSIS_ITEM_DICT DI 
ON DI. ID=D.D IAG_ITEM_ID 
LEFT the JOIN OPC_REGISTRATION R 
ON R.I D=H.O PC_REGISTRATION_ID 
LEFT the JOIN PUB_PATIENT_TYPE TY 
ON TY. ID=R.P ATIENT_TYPE_ID 
LEFT the JOIN HRM_EMPLOYEE E 
ON E.I D=H.O RDERED_DOCTOR_ID 
LEFT the JOIN HRM_EMPLOYEE E0 
ON E.I D=D.E XEC_PERSON_ID 
WHERE H.C HARGE_DATE BETWEEN the DATE '2020-06-01' AND the DATE '2020-07-01' 
AND LENGTH (D.I NVOICE_NO) & gt; 3 
AND H.H EALTH_SERVICE_ORG_ID='RSS20140108000000001') 

Explain the plan execution

 
Description access to the predicate object name cost base time bytes 
The SELECT STATEMENT, GOAL=ALL_ROWS 1512610 1 18152 127 
SORT AGGREGATE 1 127 
NESTED LOOPS OUTER 1512610 571310599 18152 72556446073 
A HASH JOIN RIGHT OUTER "E". "ID" (+)="H". "ORDERED_DOCTOR_ID" 285895 203748 3431 25875996 
The INDEX of FAST FULL SCAN PK_HRM_EMPLOYEE 6 2804 1 44864 
A HASH JOIN "D". "DIAG_SERVICE_H_CHARGE_ID"="H". "ID", 285887, 203748, 3431, 22616028 
TABLE ACCESS BY INDEX ROWID OPC_DIAG_SERVICE_H_CHARGE 36727 96844 441 8231740 
The INDEX RANGE SCAN "H". "CHARGE_DATE & gt;"=TIMESTAMP '2020-06-01 00:00:00 "AND" H ". "CHARGE_DATE" & lt; 801, 116739, 10=TIMESTAMP '2020-07-01 00:00:00 OPCDSH_CHARGE_INDEX 
TABLE ACCESS FULL OPC_DIAG_SERVICE_D_CHARGE 199509 27244882 2395 708366932 
The VIEW 6 2804 1 
The FILTER 
The INDEX of FAST FULL SCAN PK_HRM_EMPLOYEE 6 2804 1 

CodePudding user response:

A total of 30 w data, to query a total of 35 seconds, too slow, how to optimize the following!!