Multi-table query optimization-CodePudding

 
SELECT DISTINCT 
Amy polumbo ATIENT_ID, Dr. ISIT_ID, B.N AME, B.I NP_NO, c.a. DMISSION_DATE_TIME, C.D EPT_ADMISSION_TO, Arthur c. harge_type, D.D EPT_NAME 
The FROM INP_BILL_DETAIL A, PAT_MASTER_INDEX B, PAT_VISIT C, DEPT_DICT D 
WHERE 
PATIENT_ID=(a. p. ATIENT_ID) 
AND (a. PATIENT_ID=C.P ATIENT_ID) AND (a. VISIT_ID=c. ISIT_ID) 
AND a. RCPT_NO IS NULL 
AND a. PATIENT_ID NOT IN (SELECT PATIENT_ID FROM PATS_IN_HOSPITAL) 
AND SUBSTR (a. ORDERED_BY, 1, 4)='" + ward_code + @ "' 
AND D.D EPT_CODE=C.D EPT_ADMISSION_TO 
The ORDER BY ADMISSION_DATE_TIME desc

A table data quantity article 3000 w, B 70 w, table 17 C w, D table 100, A great god help me to optimize the

CodePudding user response:

This depends on the execution plan, otherwise, is a guess,

CodePudding user response:

reference 1st floor lhdz_bj response:

this depends on the execution plan, otherwise, it is guess,

implementation plan to stick out

CodePudding user response:

Biggest watch the FTS, try to build a functional index,
The create index ind_sub_ord14 on INP_BILL_DETAIL (SUBSTR (ORDERED_BY, 1, 4));

CodePudding user response:

A full table scan A table, see if there are any available index, if ORDERED_BY is index, to like, or, as the upstairs said to build A function index,
Amy polumbo ATIENT_ID additionally, p. ATIENT_ID, C.P ATIENT_ID, Dr. ISIT_ID, c. ISIT_ID preferably index
Eventually, of course, should according to the actual circumstance of business judgment, table indexes cannot too much