HELP avoiding full table scans with NVL and DECODE

From: Kel Brigman <kelley.brigman_at_ac.com>
Date: 20 Feb 1999 00:34:23 GMT
Message-ID: <01be5c68$5ff02380$f5377a95_at_twmmpe01>

I need to tune numerous queries that use NVL with DECODE to allow users to leave fields null when querying. This seems to force a full table scan! The table has grown beyond 7,000,000 rows, and performance now terrible!

Works like this:

SELECT col1, col2, col3...
FROM table
WHERE NVL(variable1, 'T') = DECODE(variable1, NULL, 'T', column1) AND NVL(variable2, 'T') = DECODE(variable2, NULL, 'T', column2) ...

if variable 1 is NOT NULL, but variable 2 IS NULL, then you get WHERE variable1 = column1
AND 'T' = 'T' (not 'T' = column2)

the second column name is out of the query, and it can't hit column2 index. Any ideas on avoiding a full table scan??!! thanks in advance!

SELECT OTX.OTX_OB_TXN_ID,

		OTX.OTX_STATUS_CD,
		OTX.OTX_AP_ID,
		OTX.OTX_BATCH_TMSTMP,
		OTX.OTX_TC_DA,
		OTX.OTX_SI_DA,
        		OTX.OTX_NEW_BTN_NO,
		OTX.OTX_NEW_WTN_NO,
		OTX.OTX_DAT,
		OTX.OTX_NEW_CUSTMR_TYP_INDR,
		OTX.OTX_J_INDR,
		OTX.OTX_CIC_4_DA

FROM OUTXN10T OTX
WHERE NVL(in_otx_internal_file_nm, 'T') = DECODE(in_otx_internal_file_nm, NULL, 'T',

			OTX.OTX_INTERNAL_FILE_NM)
AND     NVL(in_otx_status_cd, 'T') = DECODE(in_otx_status_cd, NULL, 'T',
OTX.OTX_STATUS_CD)
AND     NVL(in_otx_ap_id, 'T') = DECODE(in_otx_ap_id, NULL, 'T',
OTX.OTX_AP_ID)
AND     NVL(in_otx_tc_da, 'T') = DECODE(in_otx_tc_da, NULL, 'T',
OTX.OTX_TC_DA)
AND     NVL(in_otx_si_da, 'T') = DECODE(in_otx_si_da, NULL, 'T',
OTX.OTX_SI_DA)
AND     NVL(in_otx_new_wtn_no, 'T') = DECODE(in_otx_new_wtn_no, NULL, 'T',
OTX.OTX_NEW_WTN_NO)
AND     NVL(in_otx_new_custmr_typ_indr, 'T') =
DECODE(in_otx_new_custmr_typ_indr, NULL, 'T', 
			OTX.OTX_NEW_CUSTMR_TYP_INDR)
AND     ((DECODE(in_start_date, NULL, 'T', 'F') = 'T')
OR	    (OTX.OTX_BATCH_TMSTMP BETWEEN in_start_date AND in_end_date))
AND     NVL(in_otx_new_btn_no, 'T') = DECODE(in_otx_new_btn_no, NULL, 'T',
OTX.OTX_NEW_BTN_NO)
AND     NVL(in_otx_cic_4_da, 'T') = DECODE(in_otx_cic_4_da, NULL, 'T',

OTX.OTX_CIC_4_DA); Received on Sat Feb 20 1999 - 01:34:23 CET