Toad World® Forums

1st Alt query won't run


#1

I generated alternatives for a sql statement and then tried to ‘run selected’. The original statement runs ok, but the 1st alternate didn’t finish. I copied the first alternate to toad and it runs fine returning data in about 5 seconds–so the alt 1 statement it generates is valid. In the tuning lab, if I right click the the alt 1 sql statement and select ‘retrieve results’, the window appears with no sql statement in it. This is the statement:

select distinct SPRIDEN_PIDM
from SPRIDEN,
(select SORLCUR_PIDM “SAVCCUR_PIDM”,
SORLCUR_SEQNO “SAVCCUR_SEQNO”,
SORLCUR_LMOD_CODE “SAVCCUR_LMOD_CODE”,
SORLCUR_TERM_CODE “SAVCCUR_TERM_CODE”,
SORLCUR_KEY_SEQNO “SAVCCUR_KEY_SEQNO”,
SORLCUR_PRIORITY_NO “SAVCCUR_PRIORITY_NO”,
SORLCUR_CACT_CODE “SAVCCUR_CACT_CODE”,
SORLCUR_USER_ID “SAVCCUR_USER_ID”,
SORLCUR_DATA_ORIGIN “SAVCCUR_DATA_ORIGIN”,
SORLCUR_ACTIVITY_DATE “SAVCCUR_ACTIVITY_DATE”,
SORLCUR_LEVL_CODE “SAVCCUR_LEVL_CODE”,
SORLCUR_COLL_CODE “SAVCCUR_COLL_CODE”,
SORLCUR_DEGC_CODE “SAVCCUR_DEGC_CODE”,
SORLCUR_TERM_CODE_CTLG “SAVCCUR_TERM_CODE_CTLG”,
SORLCUR_TERM_CODE_END “SAVCCUR_TERM_CODE_END”,
SORLCUR_CAMP_CODE “SAVCCUR_CAMP_CODE”,
SORLCUR_PROGRAM “SAVCCUR_PROGRAM”,
SORLCUR_START_DATE “SAVCCUR_START_DATE”,
SORLCUR_END_DATE “SAVCCUR_END_DATE”,
SORLCUR_CURR_RULE “SAVCCUR_CURR_RULE”,
SORLCUR_ROLLED_SEQNO “SAVCCUR_ROLLED_SEQNO”,
SORLCUR.ROWID “SAVCCUR_ROWID”,
SOKCCUR.f_order(sorlcur_pidm, sb_curriculum_str.f_admissions, sorlcur_seqno, saradap_term_code_entry, saradap_appl_no) “SAVCCUR_ORDER”,
substr(sb_learnercurricstatus.f_is_active(SORLCUR_CACT_CODE), 1, 1) “SAVCCUR_ACTIVE_IND”,
SARADAP1.ROWID “SAVCCUR_ADAP_ROWID”,
SARADAP_APPL_DATE “SAVCCUR_APPL_DATE”,
SARADAP_APST_CODE “SAVCCUR_APST_CODE”,
SARADAP_APST_DATE “SAVCCUR_APST_DATE”,
SARADAP_MAINT_IND “SAVCCUR_MAINT_IND”,
SARADAP_ADMT_CODE “SAVCCUR_ADMT_CODE”,
SARADAP_STYP_CODE “SAVCCUR_STYP_CODE”,
SARADAP_SITE_CODE “SAVCCUR_SITE_CODE”,
SARADAP_RESD_CODE “SAVCCUR_RESD_CODE”,
SARADAP_FULL_PART_IND “SAVCCUR_FULL_PART_IND”,
SARADAP_SESS_CODE “SAVCCUR_SESS_CODE”,
SARADAP_WRSN_CODE “SAVCCUR_WRSN_CODE”,
SARADAP_INTV_CODE “SAVCCUR_INTV_CODE”,
SARADAP_FEE_IND “SAVCCUR_FEE_IND”,
SARADAP_FEE_DATE “SAVCCUR_FEE_DATE”,
SARADAP_RATE_CODE “SAVCCUR_RATE_CODE”,
SARADAP_EGOL_CODE “SAVCCUR_EGOL_CODE”,
SARADAP_EDLV_CODE “SAVCCUR_EDLV_CODE”,
SARADAP_SBGI_CODE “SAVCCUR_SBGI_CODE”,
SARADAP_RECR_CODE “SAVCCUR_RECR_CODE”,
SARADAP_RTYP_CODE “SAVCCUR_RTYP_CODE”,
SARADAP_WEB_ACCT_MISC_IND “SAVCCUR_WEB_ACCT_MISC_IND”,
SARADAP_WEB_CASHIER_USER “SAVCCUR_WEB_CASHIER_USER”,
SARADAP_WEB_TRANS_NO “SAVCCUR_WEB_TRANS_NO”,
SARADAP_WEB_AMOUNT “SAVCCUR_WEB_AMOUNT”,
SARADAP_WEB_RECEIPT_NUMBER “SAVCCUR_WEB_RECEIPT_NUMBER”,
SARADAP_WAIV_CODE “SAVCCUR_WAIV_CODE”,
SARADAP_APPL_PREFERENCE “SAVCCUR_APPL_PREFERENCE”
from “PUBLIC”.saradap SARADAP1,
“PUBLIC”.sorlcur
where saradap_term_code_entry = sorlcur_term_code
and saradap_appl_no = sorlcur_key_seqno
and saradap_pidm = sorlcur_pidm
and sorlcur_lmod_code = sb_curriculum_str.f_admissions
and sorlcur_pidm > 0) SAVCCUR,
(SELECT SARADAP_PIDM “SAVAPDC_PIDM”,
SARADAP_TERM_CODE_ENTRY “SAVAPDC_TERM_CODE”,
SARADAP_APPL_NO “SAVAPDC_APPL_NO”,
SARADAP_APST_CODE “SAVAPDC_APST_CODE”,
STVAPST_DESC “SAVAPDC_APST_DESC”,
A1.SARAPPD_APDC_CODE “SAVAPDC_APDC_CODE”,
STVAPDC_DESC “SAVAPDC_APDC_DESC”,
STVAPDC_SIGNF_IND “SAVAPDC_SIGNF_IND”,
STVAPDC_INST_ACC_IND “SAVAPDC_INST_ACC_IND”,
STVAPDC_STDN_ACC_IND “SAVAPDC_STDN_ACC_IND”,
STVAPDC_APPL_INACT “SAVAPDC_APPL_INACT”
FROM “PUBLIC”.STVAPDC STVAPDC1,
“PUBLIC”.SARAPPD A1,
“PUBLIC”.STVAPST STVAPST1,
“PUBLIC”.SARADAP SARADAP2
WHERE STVAPST_CODE (+) = SARADAP_APST_CODE
AND A1.SARAPPD_PIDM (+) = SARADAP_PIDM
AND A1.SARAPPD_TERM_CODE_ENTRY (+) = SARADAP_TERM_CODE_ENTRY
AND A1.SARAPPD_APPL_NO (+) = SARADAP_APPL_NO
AND (to_char(A1.SARAPPD_APDC_DATE, ‘YYYYMMDD’) || A1.SARAPPD_SEQ_NO = (SELECT MAX(to_char(B1.SARAPPD_APDC_DATE, ‘YYYYMMDD’) || B1.SARAPPD_SEQ_NO)
FROM “PUBLIC”.SARAPPD B1
WHERE B1.SARAPPD_PIDM = A1.SARAPPD_PIDM
AND B1.SARAPPD_TERM_CODE_ENTRY = A1.SARAPPD_TERM_CODE_ENTRY
AND B1.SARAPPD_APPL_NO = A1.SARAPPD_APPL_NO)
OR A1.SARAPPD_APDC_DATE IS NULL)
AND STVAPDC_CODE = A1.SARAPPD_APDC_CODE
UNION ALL
SELECT SARADAP_PIDM “SAVAPDC_PIDM”,
SARADAP_TERM_CODE_ENTRY “SAVAPDC_TERM_CODE”,
SARADAP_APPL_NO “SAVAPDC_APPL_NO”,
SARADAP_APST_CODE “SAVAPDC_APST_CODE”,
STVAPST_DESC “SAVAPDC_APST_DESC”,
A2.SARAPPD_APDC_CODE “SAVAPDC_APDC_CODE”,
NULL “SAVAPDC_APDC_DESC”,
NULL “SAVAPDC_SIGNF_IND”,
NULL “SAVAPDC_INST_ACC_IND”,
NULL “SAVAPDC_STDN_ACC_IND”,
NULL “SAVAPDC_APPL_INACT”
FROM “PUBLIC”.SARAPPD A2,
“PUBLIC”.STVAPST STVAPST2,
“PUBLIC”.SARADAP SARADAP3
WHERE STVAPST_CODE (+) = SARADAP_APST_CODE
AND A2.SARAPPD_PIDM (+) = SARADAP_PIDM
AND A2.SARAPPD_TERM_CODE_ENTRY (+) = SARADAP_TERM_CODE_ENTRY
AND A2.SARAPPD_APPL_NO (+) = SARADAP_APPL_NO
AND (to_char(A2.SARAPPD_APDC_DATE, ‘YYYYMMDD’) || A2.SARAPPD_SEQ_NO = (SELECT MAX(to_char(B2.SARAPPD_APDC_DATE, ‘YYYYMMDD’) || B2.SARAPPD_SEQ_NO)
FROM “PUBLIC”.SARAPPD B2
WHERE B2.SARAPPD_PIDM = A2.SARAPPD_PIDM
AND B2.SARAPPD_TERM_CODE_ENTRY = A2.SARAPPD_TERM_CODE_ENTRY
AND B2.SARAPPD_APPL_NO = A2.SARAPPD_APPL_NO)
OR A2.SARAPPD_APDC_DATE IS NULL)
AND (NOT EXISTS (SELECT ‘X’
FROM “PUBLIC”.STVAPDC STVAPDC2
WHERE STVAPDC_CODE = A2.SARAPPD_APDC_CODE))) SAVAPDC
where (SPRIDEN_PIDM IN (select SRBRECR_PIDM
from SRBRECR)
OR SPRIDEN_PIDM IN (select SARADAP_PIDM
from SARADAP SARADAP21))
and SAVCCUR_PIDM = SPRIDEN_PIDM
and SAVAPDC_PIDM = SPRIDEN_PIDM
and SAVAPDC_APPL_NO = SAVCCUR_KEY_SEQNO
and SAVAPDC_TERM_CODE = SAVCCUR_TERM_CODE
and SAVCCUR_TERM_CODE = :1
and SAVCCUR_ORDER = 1
AND SAVCCUR_LEVL_CODE = :2
and NVL(SAVAPDC_STDN_ACC_IND, ‘N’) = ‘Y’


#2

For long SQL statement, you better test run all alternative, low cost does not mean better in performance.
How many records your SQL will returned?
5 seconds returning data in Toad does not mean Alt1 will finish returning all data in short time. Don’t be confused that a SQL with short reponse time,it will also return all data in short time.


#3

Hi,

We have changed the way the Tuning Lab handles the Retrieve Results function in version 7.4 which is scheduled to be available tomorrow (September 17). If you download this new version, your SQL statement should be displayed in the Retrieve Results window where you can then execute it.

Rene