0

I have an Oracle SQL query which has multiple sub-queries in it. The query gives correct data but it takes 15 - 20 minutes to execute. After reading some answers from links below:

  1. https://dba.stackexchange.com/questions/19605/forcing-oracle-to-use-hash-join-for-a-subquery
  2. Optimize performance of sub-queries

I tried to optimize the query using GLOBAL TEMPORARY TABLE but it seems I do not have privilege to CREATE command.

I would like to ask if anyone of you knows any tool or technique to optimize the performance of the query below:

This query is used to generated admission numbers for each campus and programmes. 

SELECT DISTINCT STVCAMP_DESC "CAMPUS",
STVDEGC_CODE || ' - ' ||STVDEGC_DESC "PROGRAMME",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
) "REC",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'AD'
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'AD'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "TOTAL ADMITTED",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'AD'
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'AD'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE 
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND SARADAP_ADMT_CODE = '01'

AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "NEWLY ADMITTED",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'AD'
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'AD'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND SARADAP_ADMT_CODE = '03'

AND TO_DATE(TO_CHAR(SARAPPD_APDC_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "ADMITTED RESUMING",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'H'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "HOLD",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'I'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "INCOMPLETE",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'P'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "PENDING USP RESULTS",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'W'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "COUNSELLING REQUIRED",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'C'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "AWAITING ASSESSMENT",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A
WHERE A.SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND A.SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(A.SARADAP_APPL_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')
AND SARADAP_APST_CODE = 'D'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND NOT EXISTS (SELECT SARADAP_PIDM FROM SARADAP B,SARAPPD D
                    WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
                    AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
                    AND SARAPPD_PIDM = SARADAP_PIDM
                    AND SARAPPD_APPL_NO = SARADAP_APPL_NO
                    AND SARAPPD_APDC_CODE = 'AD'
                    AND SARAPPD_SEQ_NO = (SELECT 
                                            MAX(SARAPPD_SEQ_NO) 
                                            FROM SARAPPD 
                                            WHERE SARAPPD_PIDM = SARADAP_PIDM 
                                            AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                                            AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                                            AND SARAPPD_APDC_CODE = 'AD'
                                        )
                    AND B.SARADAP_PIDM = A.SARADAP_PIDM
                    AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY'))
) "DECISION MADE-NOT AD",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'U3'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'U3'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "REJECTED",

(SELECT COUNT(DISTINCT SARADAP_PIDM) FROM SARADAP A,SARAPPD
WHERE SARADAP_TERM_CODE_ENTRY LIKE :TERM
AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY
AND SARAPPD_PIDM = SARADAP_PIDM
AND SARAPPD_APPL_NO = SARADAP_APPL_NO
AND SARAPPD_APDC_CODE = 'L2'
AND A.SARADAP_DEGC_CODE_1 = STVDEGC_CODE
AND SARAPPD_SEQ_NO = (SELECT 
                        MAX(SARAPPD_SEQ_NO) 
                        FROM SARAPPD 
                        WHERE SARAPPD_PIDM = SARADAP_PIDM 
                        AND SARAPPD_TERM_CODE_ENTRY = SARADAP_TERM_CODE_ENTRY 
                        AND SARAPPD_APPL_NO = SARADAP_APPL_NO 
                        AND SARAPPD_APDC_CODE = 'L2'
                    )
AND SARADAP_CAMP_CODE = STVCAMP_CODE
AND TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY') <= TO_DATE(TO_CHAR (SYSDATE,'DD/MM/')||:YEAR,'DD/MM/YYYY')) "OL GENERATED"

FROM STVCAMP,STVDEGC,
(SELECT DISTINCT SARADAP_DEGC_CODE_1 FROM SARADAP A WHERE A.SARADAP_TERM_CODE_ENTRY = 201403 ) PROG
WHERE STVCAMP_CODE NOT IN ('X','F','P','W')

AND STVDEGC_CODE = PROG.SARADAP_DEGC_CODE_1

ORDER BY 1,2
Improve this question
5
  • 1
    If you can afford the temp space usage going up and not create the temp tables (which often pays dividends) - you could use a WITH clause and at the end of the with clause, include a predicate of rownum >= 0 - this has the effect of forcing the optimizer to materialize the with clause into memory / temp before using it. Commented Jul 28, 2014 at 21:10
  • 1
    A subselect in the select clause that has a where not in subquery....ouch, this is ugly. It'd take far more work than what I can invest into a SO answer, but removing those subqueries from the select line and getting them into the from statement and joining them will likely be a good performance boost. Commented Jul 28, 2014 at 21:52
  • thanks @Andrew, WITH clause works fine for me. Commented Jul 28, 2014 at 23:33
  • thanks @Twelfth, your suggestion is also valid and too many sub-queries has affected the performance of the query. Commented Jul 28, 2014 at 23:42
  • Seems like you are running that select (max) code over and over again. Grouping can be an expensive operation. It seems if you replaced each of those with a single join to a "with" query that accounted for every possible join, that would scan/group that table once and only once. That can probably be extended to the next layer of the query as well, but looking at this made my head hurt. Commented Jul 29, 2014 at 1:51

1 Answer 1

Reset to default
2

what immediately caught my eye are these date conversions:

TO_DATE(TO_CHAR(SARAPPD_ACTIVITY_DATE,'DD/MM/YYYY'),'DD/MM/YYYY')

if there are indexes on these date columns, the database will not be able to use them because of these conversions. Why are they needed at all? Get rid of them.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

thanks HAL9000, you are right, this can also be the reason for the poor performance of the query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.