optimisation of queries [message #393776] |
Tue, 24 March 2009 05:40 |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
Hi,
i am having two queries which are taking a lot of time to retrive the records.can anyone try for better optimisation on these queries for better results.
---------------Query to featch the encounter list----------------------
SELECT * FROM (
SELECT ENCOUNTER_ID,DATA,CREATED_DATE,ENCOUNTER_TYPE,FEELING_COMMENTS,CONSULTANT_ID
FROM emrVWEncounterDetails WHERE PATIENT_ID = '1526'
AND ENCOUNTER_ID NOT IN (SELECT PARALLEL_ENCOUNTER_ID FROM
EMRTRACKPARALLELENCOUNTERS
WHERE PARALLEL_ENCOUNTER_ID=ENCOUNTER_ID )
UNION
SELECT ENCOUNTER_ID,DATA,CREATED_DATE,ENCOUNTER_TYPE,FEELING_COMMENTS,CONSULTANT_ID
FROM emrVWEncounterDetails VW WHERE PATIENT_ID = '1526'
AND EXISTS (SELECT PARENT_ENCOUNTER_ID FROM EMRTRACKPARALLELENCOUNTERS
INNER JOIN EMREncounterDetails EDS on PARENT_ENCOUNTER_ID=EDS.ENCOUNTER_ID
WHERE EDS.PATIENT_ID = '1526'
AND EDS.IS_SAVE_FOR_LATER=1 AND PARALLEL_ENCOUNTER_ID =VW.ENCOUNTER_ID )
) TEMP ORDER BY CREATED_DATE DESC
----------------Query to featch the parllel encounters ----------------------
SELECT ENCOUNTER_NARRATION AS NARRATION , USER_NAME AS CONSULTANT FROM
EMREncounterData
INNER JOIN EMRENCOUNTERDETAILS on
EMREncounterData.ENCOUNTER_ID=EMREncounterDetails.ENCOUNTER_ID
INNER JOIN USERS ON CONSULTANT_ID=USER_LOGIN
WHERE IS_SAVE_FOR_LATER=0 AND ( EMREncounterData.ENCOUNTER_ID = '1557'
OR EMREncounterData.ENCOUNTER_ID IN (SELECT PARALLEL_ENCOUNTER_ID FROM
EMRTRACKPARALLELENCOUNTERS
WHERE PARENT_ENCOUNTER_ID= '1557' )
OR EMREncounterData.ENCOUNTER_ID IN (
SELECT PARALLEL_ENCOUNTER_ID FROM EMRTRACKPARALLELENCOUNTERS
START WITH PARENT_ENCOUNTER_ID= '1557' CONNECT BY PRIOR
PARALLEL_ENCOUNTER_ID=PARENT_ENCOUNTER_ID))
order by EMREncounterData.ENCOUNTER_ID
|
|
|
|
Re: optimisation of queries [message #393790 is a reply to message #393783] |
Tue, 24 March 2009 06:17 |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
for first query:
SELECT STATEMENT ALL_ROWS 49 6 12366
SORT(ORDER BY) 49 6 12366
VIEW 48 6 12366
SORT(UNIQUE) 48 6 12421
UNION-ALL
HASH JOIN(ANTI) 24 5 10325 "PARALLEL_ENCOUNTER_ID"="ENCOUNTER_ID"
VIEW EZEMRXPUB_QC.EMRVWENCOUNTERDETAILS 16 5 10305
UNION-ALL
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDATA ANALYZED 2 1 9
NESTED LOOPS 7 2 96
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS ANALYZED 4 2 78 "EED"."ENCOUNTER_TYPE"='ENCOUNTER' AND "EED"."IS_SAVE_FOR_LATER"=0
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDETAILS_IX ANALYZED 1 5 "EED"."PATIENT_ID"='1526'
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDATA_EIX ANALYZED 1 1 "EED"."ENCOUNTER_ID"="EEDE"."ENCOUNTER_ID"
NESTED LOOPS 6 2 86
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS ANALYZED 4 2 78 "EED"."ENCOUNTER_TYPE"='DIABETES' AND "EED"."IS_SAVE_FOR_LATER"=0
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDETAILS_IX ANALYZED 1 5 "EED"."PATIENT_ID"='1526'
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRROUTINEDIABETESENC_EIX ANALYZED 1 1 4 "EED"."ENCOUNTER_ID"="ERDE"."ENCOUNTER_ID"
NESTED LOOPS 3 1 2090
NESTED LOOPS 2 1 2054
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRANSCRIPTIONDATA ANALYZED 2 1 2028 "ETD"."IS_VERIFIED"=1
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRTRANSCRIPTIONMAPPING ANALYZED 0 1 26
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_EMRTranscriptionMapping ANALYZED 0 1 "ETM"."TRANSCRIPTION_ID"="ETD"."TRANSCRIPTION_ID"
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS ANALYZED 1 1 36 "EED"."PATIENT_ID"='1526' AND "EED"."ENCOUNTER_TYPE"='ENCOUNTER'
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_encounterdetails ANALYZED 0 1 "EED"."ENCOUNTER_ID"="ETM"."ENCOUNTER_ID"
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS ANALYZED 7 673 2692 "PARALLEL_ENCOUNTER_ID" IS NOT NULL
NESTED LOOPS 23 1 2096
HASH JOIN 12 1 27 "PARENT_ENCOUNTER_ID"="EDS"."ENCOUNTER_ID"
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS ANALYZED 4 1 12 "EDS"."IS_SAVE_FOR_LATER"=1
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDETAILS_IX ANALYZED 1 5 "EDS"."PATIENT_ID"='1526'
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS ANALYZED 7 915 13725
VIEW EZEMRXPUB_QC.EMRVWENCOUNTERDETAILS 11 1 2069
UNION ALL PUSHED PREDICATE
NESTED LOOPS 4 1 52
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS ANALYZED 2 1 43 "EED"."PATIENT_ID"='1526' AND "EED"."ENCOUNTER_TYPE"='ENCOUNTER' AND "EED"."IS_SAVE_FOR_LATER"=0
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_encounterdetails ANALYZED 1 1 "EED"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDATA ANALYZED 2 1 9
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRENCOUNTERDATA_EIX ANALYZED 1 1 "EEDE"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"
NESTED LOOPS 3 1 47
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS ANALYZED 2 1 43 "EED"."PATIENT_ID"='1526' AND "EED"."ENCOUNTER_TYPE"='DIABETES' AND "EED"."IS_SAVE_FOR_LATER"=0
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_encounterdetails ANALYZED 1 1 "EED"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"
INDEX(RANGE SCAN) EZEMRXPUB_QC.EMRROUTINEDIABETESENC_EIX ANALYZED 1 1 4 "ERDE"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"
NESTED LOOPS 4 1 2094
NESTED LOOPS 4 1 2068
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRENCOUNTERDETAILS ANALYZED 2 1 40 "EED"."PATIENT_ID"='1526' AND "EED"."ENCOUNTER_TYPE"='ENCOUNTER'
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_encounterdetails ANALYZED 1 1 "EED"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRANSCRIPTIONDATA ANALYZED 2 1 2028 "ETD"."IS_VERIFIED"=1
TABLE ACCESS(BY INDEX ROWID) EZEMRXPUB_QC.EMRTRANSCRIPTIONMAPPING ANALYZED 0 1 26 "ETM"."ENCOUNTER_ID"="EMRTRACKPARALLELENCOUNTERS"."PARALLEL_ENCOUNTER_ID"
INDEX(UNIQUE SCAN) EZEMRXPUB_QC.PK_EMRTranscriptionMapping ANALYZED 0 1 "ETM"."TRANSCRIPTION_ID"="ETD"."TRANSCRIPTION_ID"
for second query:
SELECT STATEMENT ALL_ROWS 129 163 19886
FILTER "EMRENCOUNTERDATA"."ENCOUNTER_ID"=1557 OR EXISTS (SELECT /*+ */ 0 FROM "EMRTRACKPARALLELENCOUNTERS" "EMRTRACKPARALLELENCOUNTERS" WHERE "PARALLEL_ENCOUNTER_ID"=:B1 AND "PARENT_ENCOUNTER_ID"=1557) OR EXISTS (SELECT /*+ */ 0 FROM "EMRTRACKPARALLELENCOUNTERS" "EMRTRACKPARALLELENCOUNTERS" AND ("PARALLEL_ENCOUNTER_ID"=:B2))
MERGE JOIN 129 1667 203374
SORT(JOIN) 23 2609 88706
HASH JOIN 22 2609 88706 "EMRENCOUNTERDETAILS"."CONSULTANT_ID"="USER_LOGIN"
TABLE ACCESS(FULL) EZEMRXPUB_QC.USERS ANALYZED 7 433 7794
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRENCOUNTERDETAILS ANALYZED 14 2603 41648 "EMRENCOUNTERDETAILS"."IS_SAVE_FOR_LATER"=0
SORT(JOIN) 106 1663 146344 "EMRENCOUNTERDATA"."ENCOUNTER_ID"="EMRENCOUNTERDETAILS"."ENCOUNTER_ID" "EMRENCOUNTERDATA"."ENCOUNTER_ID"="EMRENCOUNTERDETAILS"."ENCOUNTER_ID"
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRENCOUNTERDATA ANALYZED 68 1663 146344
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS ANALYZED 7 1 8 "PARALLEL_ENCOUNTER_ID"=:B1 AND "PARENT_ENCOUNTER_ID"=1557
FILTER "PARALLEL_ENCOUNTER_ID"=:B1
CONNECT BY(WITH FILTERING) "PARENT_ENCOUNTER_ID"=1557
FILTER "PARENT_ENCOUNTER_ID"=1557
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS ANALYZED 2 1 8
HASH JOIN "PARENT_ENCOUNTER_ID"=NULL
CONNECT BY PUMP
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS ANALYZED 2 1 8
TABLE ACCESS(FULL) EZEMRXPUB_QC.EMRTRACKPARALLELENCOUNTERS ANALYZED 2 1 8
|
|
|
|