Performance Tuning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

Performance Tuning

Author

Topic

kumarpav
Starting Member

18 Posts

Posted - 2004-12-02 : 13:02:50

Hi,

In ur site (http://www.sqlteam.com/forums/search.asp)
Search options are there.

For all those search options u must have written one query.
I have the exact requirement in my project.

For "Search For(Search for all Words)" option in your url, u maintained alphabets case sensitive.like this only i want query.

Actually In my project i am maintaining search with lot of options. It is taking lot more time in production.

I want the exact total query to perform my search also to work faster.

For my table i dont have any indexes to work faster

Could u plz send me your query,what u maintained in that url (http://www.sqlteam.com/forums/search.asp).

Thanking u for ur time...

Regards And Thanks
Pavan Kumar.K

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-02 : 14:02:49

The SQL Team forums use Snitz:

http://forum.snitz.com/

You can download their code and look at how they perform their searching.

WARNING: as you may have experienced already, the search can be very slow, especially with large data sets. While their code may get you started, you may want to post your current code, and also the requirements for your search options. We can probably provide a better solution.

kumarpav
Starting Member

18 Posts

Posted - 2005-01-12 : 12:29:24

Can u try to tune this query?

SELECT
BRCH,
TELLER,
EDIT_MASK,
DECODE(NVL(DEPP.CURRENCY,''),
'',
NVL(LONP.CURRENCY_IND,''),
NVL(DEPP.CURRENCY,'')),
JR01_TIME,
SUBSTR(DATA_01,1,4),
CUSM_NO,
HOME_BRNCH,
JR01.TRAN_DATE,
TRN_TYP,
SUBSTR(DATA_01,5,8),
ABS(BASECONV.CONVDATA(DATA_01,13,8)),
NVL(SUP_ID,0),
NVL(CHECKER1_ID,0),
NVL(CHECKER2_ID,0),
JR01.TRAN_CODE,
ENQUIRY_DESC,
JR01.SYST,
DECODE(NVL(SUP_ID,0),0,DECODE(NVL(CHECKER1_ID,0),0,
DECODE(NVL(CHECKER2_ID,0),0,DECODE(TELLER,0,'NULL',TELLER)
,CHECKER2_ID),CHECKER1_ID),SUP_ID),
NVL(DESCRIPT,''),
NVL(POST_VAL_DATE,0),
NVL(LONP.CAT_TYPE_NAME,''),
DEP_TYPE_4
FROM
JR01,
MCAD,
TXBI,
LONP,
DEPP,
OCID
WHERE
JR01.JR01_DATE=38362
AND DEPP.INST_NO='003'
AND DEPP.SYS='INV'
AND LONP.INST_NO='003'
AND LONP.SYST='BOR'
AND (JR01.TRAN_DATE=38362
OR JR01.POST_DATE=38362)
AND mcad.JRNL_DATE(+)=JR01.JR01_DATE
AND MCAD.JRNL_NO(+)=JR01.JRNL_NO
AND SUBSTR(JR01.TRAN_CODE,2,6)=TXBI.TRAN_CODE
AND txbi.NON_FIN_TXN_23='0'
AND JR01.SYST <> 'GEN'
AND TO_CHAR(OCID.JRNL_NO(+))=JR01.JRNL_NO
AND OCID.TRAN_DATE(+)=JR01.TRAN_DATE
AND SUBSTR(jr01.DATA_01,1,4)=DEPP.TYPE(+)
AND JR01.INT_CAT=DEPP.INT_CAT(+)
AND SUBSTR(jr01.DATA_01,1,4)=LONP.ACCT_TYPE(+)
AND JR01.INT_CAT=LONP.INT_CAT(+)
UNION ALL
SELECT
BRCH,
TELLER,
EDIT_MASK,
DECODE(NVL(DEPP.CURRENCY,''),
'',
NVL(LONP.CURRENCY_IND,''),
NVL(DEPP.CURRENCY,'')),
JR02_TIME,
SUBSTR(DATA_01,1,4),
CUSM_NO,
HOME_BRNCH,
JR02.TRAN_DATE,
TRN_TYP,
SUBSTR(DATA_01,5,8),
ABS(BASECONV.CONVDATA(DATA_01,13,8)),
NVL(SUP_ID,0),
NVL(CHECKER1_ID,0),
NVL(CHECKER2_ID,0),
JR02.TRAN_CODE,
ENQUIRY_DESC,
JR02.SYST,
DECODE(NVL(SUP_ID,0),0,DECODE(NVL(CHECKER1_ID,0),0,DECODE
(NVL(CHECKER2_ID,0),0,DECODE(TELLER,0,'NULL',TELLER)
,CHECKER2_ID),CHECKER1_ID),SUP_ID),
NVL(DESCRIPT,''),
NVL(POST_VAL_DATE,0),
NVL(LONP.CAT_TYPE_NAME,''),
DEP_TYPE_4
FROM
JR02,
MCAD,
TXBI,
LONP,
DEPP,
OCID
WHERE
JR02.JR02_DATE=38362
AND DEPP.INST_NO='003'
AND DEPP.SYS='INV'
AND LONP.INST_NO='003'
AND LONP.SYST='BOR'
AND (JR02.TRAN_DATE=38362
OR POST_DATE=38362)
AND JRNL_DATE(+)=JR02_DATE
AND MCAD.JRNL_NO(+)=JR02.JRNL_NO
AND SUBSTR(JR02.TRAN_CODE,2,6)=TXBI.TRAN_CODE
AND NON_FIN_TXN_23='0'
AND JR02.SYST <> 'GEN'
AND TO_CHAR(OCID.JRNL_NO(+))=JR02.JRNL_NO
AND OCID.TRAN_DATE(+)=JR02.TRAN_DATE
AND SUBSTR(DATA_01,1,4)=DEPP.TYPE(+)
AND JR02.INT_CAT=DEPP.INT_CAT(+)
AND SUBSTR(DATA_01,1,4)=LONP.ACCT_TYPE(+)
AND JR02.INT_CAT=LONP.INT_CAT(+)
UNION ALL
SELECT
BRCH,
TELLER,
EDIT_MASK,
DECODE(NVL(DEPP.CURRENCY,''),'',NVL(LONP.CURRENCY_IND,''),
NVL(DEPP.CURRENCY,'')),
JR03_TIME,
SUBSTR(DATA_01,1,4),
CUSM_NO,
HOME_BRNCH,
JR03.TRAN_DATE,
TRN_TYP,
SUBSTR(DATA_01,5,8),
ABS(BASECONV.CONVDATA(DATA_01,13,8)),
NVL(SUP_ID,0),
NVL(CHECKER1_ID,0),
NVL(CHECKER2_ID,0),
JR03.TRAN_CODE,
ENQUIRY_DESC,
JR03.SYST,
DECODE(NVL(SUP_ID,0),0,DECODE(NVL(CHECKER1_ID,0),0,DECODE
(NVL(CHECKER2_ID,0),0,DECODE(TELLER,0,'NULL',TELLER)
,CHECKER2_ID),CHECKER1_ID),SUP_ID),
NVL(DESCRIPT,''),
NVL(POST_VAL_DATE,0),
NVL(LONP.CAT_TYPE_NAME,''),
DEP_TYPE_4
FROM
JR03,
MCAD,
TXBI,
LONP,
DEPP,
OCID
WHERE
JR03.JR03_DATE=38362
AND DEPP.INST_NO='003'
AND DEPP.SYS='INV'
AND LONP.INST_NO='003'
AND LONP.SYST='BOR'
AND (JR03.TRAN_DATE=38362
OR POST_DATE=38362)
AND JRNL_DATE(+)=JR03_DATE
AND MCAD.JRNL_NO(+)=JR03.JRNL_NO
AND SUBSTR(JR03.TRAN_CODE,2,6)=TXBI.TRAN_CODE
AND NON_FIN_TXN_23='0'
AND JR03.SYST <> 'GEN'
AND TO_CHAR(OCID.JRNL_NO(+))=JR03.JRNL_NO
AND OCID.TRAN_DATE(+)=JR03.TRAN_DATE
AND SUBSTR(DATA_01,1,4)=DEPP.TYPE(+)
AND JR03.INT_CAT=DEPP.INT_CAT(+)
AND SUBSTR(DATA_01,1,4)=LONP.ACCT_TYPE(+)
AND JR03.INT_CAT=LONP.INT_CAT(+)
UNION ALL
SELECT
BRCH,
TELLER,
EDIT_MASK,
DECODE(NVL(DEPP.CURRENCY,''),'',NVL(LONP.CURRENCY_IND,''),
NVL(DEPP.CURRENCY,'')),
JR04_TIME,
SUBSTR(DATA_01,1,4),
CUSM_NO,
HOME_BRNCH,
JR04.TRAN_DATE,
TRN_TYP,
SUBSTR(DATA_01,5,8),
ABS(BASECONV.CONVDATA(DATA_01,13,8)),
NVL(SUP_ID,0),
NVL(CHECKER1_ID,0),
NVL(CHECKER2_ID,0),
JR04.TRAN_CODE,
ENQUIRY_DESC,
JR04.SYST,
DECODE(NVL(SUP_ID,0),0,DECODE(NVL(CHECKER1_ID,0),0,DECODE
(NVL(CHECKER2_ID,0),0,DECODE(TELLER,0,'NULL',TELLER)
,CHECKER2_ID),CHECKER1_ID),SUP_ID),
NVL(DESCRIPT,''),
NVL(POST_VAL_DATE,0),
NVL(LONP.CAT_TYPE_NAME,''),
DEP_TYPE_4
FROM
JR04,
MCAD,
TXBI,
LONP,
DEPP,
OCID
WHERE
JR04.JR04_DATE=38362
AND DEPP.INST_NO='003'
AND DEPP.SYS='INV'
AND LONP.INST_NO='003'
AND LONP.SYST='BOR'
AND (JR04.TRAN_DATE=38362
OR POST_DATE=38362)
AND JRNL_DATE(+)=JR04_DATE
AND MCAD.JRNL_NO(+)=JR04.JRNL_NO
AND SUBSTR(JR04.TRAN_CODE,2,6)=TXBI.TRAN_CODE
AND NON_FIN_TXN_23='0'
AND JR04.SYST <> 'GEN'
AND TO_CHAR(OCID.JRNL_NO(+))=JR04.JRNL_NO
AND OCID.TRAN_DATE(+)=JR04.TRAN_DATE
AND SUBSTR(DATA_01,1,4)=DEPP.TYPE(+)
AND JR04.INT_CAT=DEPP.INT_CAT(+)
AND SUBSTR(DATA_01,1,4)=LONP.ACCT_TYPE(+)
AND JR04.INT_CAT=LONP.INT_CAT(+)
UNION ALL
SELECT
BRCH,
TELLER,
EDIT_MASK,
DECODE(NVL(DEPP.CURRENCY,''),'',NVL(LONP.CURRENCY_IND,''),
NVL(DEPP.CURRENCY,'')),
JR05_TIME,
SUBSTR(DATA_01,1,4),
CUSM_NO,
HOME_BRNCH,
JR05.TRAN_DATE,
TRN_TYP,
SUBSTR(DATA_01,5,8),
ABS(BASECONV.CONVDATA(DATA_01,13,8)),
NVL(SUP_ID,0),
NVL(CHECKER1_ID,0),
NVL(CHECKER2_ID,0),
JR05.TRAN_CODE,
ENQUIRY_DESC,
JR05.SYST,
DECODE(NVL(SUP_ID,0),0,DECODE(NVL(CHECKER1_ID,0),0,DECODE
(NVL(CHECKER2_ID,0),0,DECODE(TELLER,0,'NULL',TELLER)
,CHECKER2_ID),CHECKER1_ID),SUP_ID),
NVL(DESCRIPT,''),
NVL(POST_VAL_DATE,0),
NVL(LONP.CAT_TYPE_NAME,''),
DEP_TYPE_4
FROM
JR05,
MCAD,
TXBI,
LONP,
DEPP,
OCID
WHERE
JR05.JR05_DATE=38362
AND DEPP.INST_NO='003'
AND DEPP.SYS='INV'
AND LONP.INST_NO='003'
AND LONP.SYST='BOR'
AND (JR05.TRAN_DATE=38362
OR POST_DATE=38362)
AND JRNL_DATE(+)=JR05_DATE
AND MCAD.JRNL_NO(+)=JR05.JRNL_NO
AND SUBSTR(JR05.TRAN_CODE,2,6)=TXBI.TRAN_CODE
AND NON_FIN_TXN_23='0'
AND JR05.SYST <> 'GEN'
AND TO_CHAR(OCID.JRNL_NO(+))=JR05.JRNL_NO
AND OCID.TRAN_DATE(+)=JR05.TRAN_DATE
AND SUBSTR(DATA_01,1,4)=DEPP.TYPE(+)
AND JR05.INT_CAT=DEPP.INT_CAT(+)
AND SUBSTR(DATA_01,1,4)=LONP.ACCT_TYPE(+)
AND JR05.INT_CAT=LONP.INT_CAT(+)
UNION ALL
SELECT
BRCH,
TELLER,
EDIT_MASK,
DECODE(NVL(DEPP.CURRENCY,''),'',NVL(LONP.CURRENCY_IND,''),
NVL(DEPP.CURRENCY,'')),
JR06_TIME,
SUBSTR(DATA_01,1,4),
CUSM_NO,
HOME_BRNCH,
JR06.TRAN_DATE,
TRN_TYP,
SUBSTR(DATA_01,5,8),
ABS(BASECONV.CONVDATA(DATA_01,13,8)),
NVL(SUP_ID,0),
NVL(CHECKER1_ID,0),
NVL(CHECKER2_ID,0),
JR06.TRAN_CODE,
ENQUIRY_DESC,
JR06.SYST,
DECODE(NVL(SUP_ID,0),0,DECODE(NVL(CHECKER1_ID,0),0,DECODE
(NVL(CHECKER2_ID,0),0,DECODE(TELLER,0,'NULL',TELLER)
,CHECKER2_ID),CHECKER1_ID),SUP_ID),
NVL(DESCRIPT,''),
NVL(POST_VAL_DATE,0),
NVL(LONP.CAT_TYPE_NAME,''),
DEP_TYPE_4
FROM
JR06,
MCAD,
TXBI,
LONP,
DEPP,
OCID
WHERE
JR06.JR06_DATE=38362
AND DEPP.INST_NO='003'
AND DEPP.SYS='INV'
AND LONP.INST_NO='003'
AND LONP.SYST='BOR'
AND (JR06.TRAN_DATE=38362
OR POST_DATE=38362)
AND JRNL_DATE(+)=JR06_DATE
AND MCAD.JRNL_NO(+)=JR06.JRNL_NO
AND SUBSTR(JR06.TRAN_CODE,2,6)=TXBI.TRAN_CODE
AND NON_FIN_TXN_23='0'
AND JR06.SYST <> 'GEN'
AND TO_CHAR(OCID.JRNL_NO(+))=JR06.JRNL_NO
AND OCID.TRAN_DATE(+)=JR06.TRAN_DATE
AND SUBSTR(DATA_01,1,4)=DEPP.TYPE(+)
AND JR06.INT_CAT=DEPP.INT_CAT(+)
AND SUBSTR(DATA_01,1,4)=LONP.ACCT_TYPE(+)
AND JR06.INT_CAT=LONP.INT_CAT(+)
UNION ALL
SELECT
BRCH,
TELLER,
EDIT_MASK,
DECODE(NVL(DEPP.CURRENCY,''),'',NVL(LONP.CURRENCY_IND,''),
NVL(DEPP.CURRENCY,'')),
JR07_TIME,
SUBSTR(DATA_01,1,4),
CUSM_NO,
HOME_BRNCH,
JR07.TRAN_DATE,
TRN_TYP,
SUBSTR(DATA_01,5,8),
ABS(BASECONV.CONVDATA(DATA_01,13,8)),
NVL(SUP_ID,0),
NVL(CHECKER1_ID,0),
NVL(CHECKER2_ID,0),
JR07.TRAN_CODE,
ENQUIRY_DESC,
JR07.SYST,
DECODE(NVL(SUP_ID,0),0,DECODE(NVL(CHECKER1_ID,0),0,DECODE
(NVL(CHECKER2_ID,0),0,DECODE(TELLER,0,'NULL',TELLER)
,CHECKER2_ID),CHECKER1_ID),SUP_ID),
NVL(DESCRIPT,''),
NVL(POST_VAL_DATE,0),
NVL(LONP.CAT_TYPE_NAME,''),
DEP_TYPE_4
FROM
JR07,
MCAD,
TXBI,
LONP,
DEPP,
OCID
WHERE
JR07.JR07_DATE=38362
AND DEPP.INST_NO='003'
AND DEPP.SYS='INV'
AND LONP.INST_NO='003'
AND LONP.SYST='BOR'
AND (JR07.TRAN_DATE=38362
OR POST_DATE=38362)
AND JRNL_DATE(+)=JR07_DATE
AND MCAD.JRNL_NO(+)=JR07.JRNL_NO
AND SUBSTR(JR07.TRAN_CODE,2,6)=TXBI.TRAN_CODE
AND NON_FIN_TXN_23='0'
AND JR07.SYST <> 'GEN'
AND TO_CHAR(OCID.JRNL_NO(+))=JR07.JRNL_NO
AND OCID.TRAN_DATE(+)=JR07.TRAN_DATE
AND SUBSTR(DATA_01,1,4)=DEPP.TYPE(+)
AND JR07.INT_CAT=DEPP.INT_CAT(+)
AND SUBSTR(DATA_01,1,4)=LONP.ACCT_TYPE(+)
AND JR07.INT_CAT=LONP.INT_CAT(+)
UNION ALL
SELECT
BRCH,
TELLER,
EDIT_MASK,
DECODE(NVL(DEPP.CURRENCY,''),'',NVL(LONP.CURRENCY_IND,''),
NVL(DEPP.CURRENCY,'')),
JR08_TIME,
SUBSTR(DATA_01,1,4),
CUSM_NO,
HOME_BRNCH,
JR08.TRAN_DATE,
TRN_TYP,
SUBSTR(DATA_01,5,8),
ABS(BASECONV.CONVDATA(DATA_01,13,8)),
NVL(SUP_ID,0),
NVL(CHECKER1_ID,0),
NVL(CHECKER2_ID,0),
JR08.TRAN_CODE,
ENQUIRY_DESC,
JR08.SYST,
DECODE(NVL(SUP_ID,0),0,DECODE(NVL(CHECKER1_ID,0),0,DECODE
(NVL(CHECKER2_ID,0),0,DECODE(TELLER,0,'NULL',TELLER)
,CHECKER2_ID),CHECKER1_ID),SUP_ID),
NVL(DESCRIPT,''),
NVL(POST_VAL_DATE,0),
NVL(LONP.CAT_TYPE_NAME,''),
DEP_TYPE_4
FROM
JR08,
MCAD,
TXBI,
LONP,
DEPP,
OCID
WHERE
JR08.JR08_DATE=38362
AND DEPP.INST_NO='003'
AND DEPP.SYS='INV'
AND LONP.INST_NO='003'
AND LONP.SYST='BOR'
AND (JR08.TRAN_DATE=38362
OR POST_DATE=38362)
AND JRNL_DATE(+)=JR08_DATE
AND MCAD.JRNL_NO(+)=JR08.JRNL_NO
AND SUBSTR(JR08.TRAN_CODE,2,6)=TXBI.TRAN_CODE
AND NON_FIN_TXN_23='0'
AND JR08.SYST <> 'GEN'
AND TO_CHAR(OCID.JRNL_NO(+))=JR08.JRNL_NO
AND OCID.TRAN_DATE(+)=JR08.TRAN_DATE
AND SUBSTR(DATA_01,1,4)=DEPP.TYPE(+)
AND JR08.INT_CAT=DEPP.INT_CAT(+)
AND SUBSTR(DATA_01,1,4)=LONP.ACCT_TYPE(+)
AND JR08.INT_CAT=LONP.INT_CAT(+)
ORDER BY 1,19,4,23,12,5,6,7

============================================================

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
----

| Id | Operation | Name | Rows | Bytes | Cos
t |

--------------------------------------------------------------------------------
----

| 0 | SELECT STATEMENT | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 1 | SORT ORDER BY | | | |
|

| 2 | UNION-ALL | | | |
|

| 3 | MERGE JOIN OUTER | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 4 | MERGE JOIN OUTER | | | |
|

| 5 | SORT JOIN | | | |
|

| 6 | MERGE JOIN OUTER | | | |
|

| 7 | SORT JOIN | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 8 | NESTED LOOPS OUTER | | | |
|

| 9 | NESTED LOOPS | | | |
|

|* 10 | TABLE ACCESS FULL | JR01 | | |
|

|* 11 | TABLE ACCESS BY INDEX ROWID| TXBI | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

|* 12 | INDEX UNIQUE SCAN | TXBIPK | | |
|

| 13 | TABLE ACCESS BY INDEX ROWID | MCAD | | |
|

|* 14 | INDEX UNIQUE SCAN | MCADPK | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 15 | SORT JOIN | | | |
|

| 16 | TABLE ACCESS FULL | OCID | | |
|

|* 17 | SORT JOIN | | | |
|

| 18 | TABLE ACCESS FULL | DEPP | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 19 | SORT JOIN | | | |
|

| 20 | TABLE ACCESS FULL | LONP | | |
|

| 21 | MERGE JOIN OUTER | | | |
|

| 22 | MERGE JOIN OUTER | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 23 | SORT JOIN | | | |
|

| 24 | MERGE JOIN OUTER | | | |
|

| 25 | SORT JOIN | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 26 | NESTED LOOPS OUTER | | | |
|

| 27 | NESTED LOOPS | | | |
|

|* 28 | TABLE ACCESS FULL | JR02 | | |
|

|* 29 | TABLE ACCESS BY INDEX ROWID| TXBI | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|* 30 | INDEX UNIQUE SCAN | TXBIPK | | |
|

| 31 | TABLE ACCESS BY INDEX ROWID | MCAD | | |
|

|* 32 | INDEX UNIQUE SCAN | MCADPK | | |
|

|* 33 | SORT JOIN | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 34 | TABLE ACCESS FULL | OCID | | |
|

|* 35 | SORT JOIN | | | |
|

| 36 | TABLE ACCESS FULL | DEPP | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 37 | SORT JOIN | | | |
|

| 38 | TABLE ACCESS FULL | LONP | | |
|

| 39 | MERGE JOIN OUTER | | | |
|

| 40 | MERGE JOIN OUTER | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 41 | SORT JOIN | | | |
|

| 42 | MERGE JOIN OUTER | | | |
|

| 43 | SORT JOIN | | | |
|

| 44 | NESTED LOOPS OUTER | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 45 | NESTED LOOPS | | | |
|

|* 46 | TABLE ACCESS FULL | JR03 | | |
|

|* 47 | TABLE ACCESS BY INDEX ROWID| TXBI | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 48 | INDEX UNIQUE SCAN | TXBIPK | | |
|

| 49 | TABLE ACCESS BY INDEX ROWID | MCAD | | |
|

|* 50 | INDEX UNIQUE SCAN | MCADPK | | |
|

|* 51 | SORT JOIN | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 52 | TABLE ACCESS FULL | OCID | | |
|

|* 53 | SORT JOIN | | | |
|

| 54 | TABLE ACCESS FULL | DEPP | | |
|

|* 55 | SORT JOIN | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 56 | TABLE ACCESS FULL | LONP | | |
|

| 57 | MERGE JOIN OUTER | | | |
|

| 58 | MERGE JOIN OUTER | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 59 | SORT JOIN | | | |
|

| 60 | MERGE JOIN OUTER | | | |
|

| 61 | SORT JOIN | | | |
|

| 62 | NESTED LOOPS OUTER | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 63 | NESTED LOOPS | | | |
|

|* 64 | TABLE ACCESS FULL | JR04 | | |
|

|* 65 | TABLE ACCESS BY INDEX ROWID| TXBI | | |
|

|* 66 | INDEX UNIQUE SCAN | TXBIPK | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 67 | TABLE ACCESS BY INDEX ROWID | MCAD | | |
|

|* 68 | INDEX UNIQUE SCAN | MCADPK | | |
|

|* 69 | SORT JOIN | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 70 | TABLE ACCESS FULL | OCID | | |
|

|* 71 | SORT JOIN | | | |
|

| 72 | TABLE ACCESS FULL | DEPP | | |
|

|* 73 | SORT JOIN | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 74 | TABLE ACCESS FULL | LONP | | |
|

| 75 | MERGE JOIN OUTER | | | |
|

| 76 | MERGE JOIN OUTER | | | |
|

| 77 | SORT JOIN | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 78 | MERGE JOIN OUTER | | | |
|

| 79 | SORT JOIN | | | |
|

| 80 | NESTED LOOPS OUTER | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 81 | NESTED LOOPS | | | |
|

|* 82 | TABLE ACCESS FULL | JR05 | | |
|

|* 83 | TABLE ACCESS BY INDEX ROWID| TXBI | | |
|

|* 84 | INDEX UNIQUE SCAN | TXBIPK | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 85 | TABLE ACCESS BY INDEX ROWID | MCAD | | |
|

|* 86 | INDEX UNIQUE SCAN | MCADPK | | |
|

|* 87 | SORT JOIN | | | |
|

| 88 | TABLE ACCESS FULL | OCID | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

|* 89 | SORT JOIN | | | |
|

| 90 | TABLE ACCESS FULL | DEPP | | |
|

|* 91 | SORT JOIN | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 92 | TABLE ACCESS FULL | LONP | | |
|

| 93 | MERGE JOIN OUTER | | | |
|

| 94 | MERGE JOIN OUTER | | | |
|

| 95 | SORT JOIN | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 96 | MERGE JOIN OUTER | | | |
|

| 97 | SORT JOIN | | | |
|

| 98 | NESTED LOOPS OUTER | | | |
|

| 99 | NESTED LOOPS | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

|*100 | TABLE ACCESS FULL | JR06 | | |
|

|*101 | TABLE ACCESS BY INDEX ROWID| TXBI | | |
|

|*102 | INDEX UNIQUE SCAN | TXBIPK | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 103 | TABLE ACCESS BY INDEX ROWID | MCAD | | |
|

|*104 | INDEX UNIQUE SCAN | MCADPK | | |
|

|*105 | SORT JOIN | | | |
|

| 106 | TABLE ACCESS FULL | OCID | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*107 | SORT JOIN | | | |
|

| 108 | TABLE ACCESS FULL | DEPP | | |
|

|*109 | SORT JOIN | | | |
|

| 110 | TABLE ACCESS FULL | LONP | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 111 | MERGE JOIN OUTER | | | |
|

| 112 | MERGE JOIN OUTER | | | |
|

| 113 | SORT JOIN | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 114 | MERGE JOIN OUTER | | | |
|

| 115 | SORT JOIN | | | |
|

| 116 | NESTED LOOPS OUTER | | | |
|

| 117 | NESTED LOOPS | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*118 | TABLE ACCESS FULL | JR07 | | |
|

|*119 | TABLE ACCESS BY INDEX ROWID| TXBI | | |
|

|*120 | INDEX UNIQUE SCAN | TXBIPK | | |
|

| 121 | TABLE ACCESS BY INDEX ROWID | MCAD | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

|*122 | INDEX UNIQUE SCAN | MCADPK | | |
|

|*123 | SORT JOIN | | | |
|

| 124 | TABLE ACCESS FULL | OCID | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*125 | SORT JOIN | | | |
|

| 126 | TABLE ACCESS FULL | DEPP | | |
|

|*127 | SORT JOIN | | | |
|

| 128 | TABLE ACCESS FULL | LONP | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 129 | MERGE JOIN OUTER | | | |
|

| 130 | MERGE JOIN OUTER | | | |
|

| 131 | SORT JOIN | | | |
|

| 132 | MERGE JOIN OUTER | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 133 | SORT JOIN | | | |
|

| 134 | NESTED LOOPS OUTER | | | |
|

| 135 | NESTED LOOPS | | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*136 | TABLE ACCESS FULL | JR08 | | |
|

|*137 | TABLE ACCESS BY INDEX ROWID| TXBI | | |
|

|*138 | INDEX UNIQUE SCAN | TXBIPK | | |
|

| 139 | TABLE ACCESS BY INDEX ROWID | MCAD | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*140 | INDEX UNIQUE SCAN | MCADPK | | |
|

|*141 | SORT JOIN | | | |
|

| 142 | TABLE ACCESS FULL | OCID | | |
|

|*143 | SORT JOIN | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|

| 144 | TABLE ACCESS FULL | DEPP | | |
|

|*145 | SORT JOIN | | | |
|

| 146 | TABLE ACCESS FULL | LONP | | |
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----

Predicate Information (identified by operation id):
---------------------------------------------------

10 - filter("JR01"."SYST"<>'GEN' AND ("JR01"."TRAN_DATE"=38362 OR
"JR01"."POST_DATE"=38362))
11 - filter("TXBI"."NON_FIN_TXN_23"='0')
12 - access("TXBI"."TRAN_CODE"=SUBSTR("JR01"."TRAN_CODE",2,6))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
14 - access("MCAD"."JRNL_DATE"(+)="JR01"."JR01_DATE" AND
"MCAD"."JRNL_NO"(+)="JR01"."JRNL_NO")
15 - access("JR01"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
filter("OCID"."TRAN_DATE"(+)="JR01"."TRAN_DATE" AND
"JR01"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
17 - access("DEPP"."TYPE"(+)=SUBSTR("JR01"."DATA_01",1,4))
filter("JR01"."INT_CAT"="DEPP"."INT_CAT"(+) AND
"DEPP"."TYPE"(+)=SUBSTR("JR01"."DATA_01",1,4))
19 - access("LONP"."ACCT_TYPE"(+)=SUBSTR("JR01"."DATA_01",1,4))
filter("JR01"."INT_CAT"="LONP"."INT_CAT"(+) AND
"LONP"."ACCT_TYPE"(+)=SUBSTR("JR01"."DATA_01",1,4))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
28 - filter("JR02"."SYST"<>'GEN' AND ("JR02"."TRAN_DATE"=38362 OR
"JR02"."POST_DATE"=38362))
29 - filter("TXBI"."NON_FIN_TXN_23"='0')
30 - access("TXBI"."TRAN_CODE"=SUBSTR("JR02"."TRAN_CODE",2,6))
32 - access("MCAD"."JRNL_DATE"(+)="JR02"."JR02_DATE" AND
"MCAD"."JRNL_NO"(+)="JR02"."JRNL_NO")
33 - access("JR02"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
filter("OCID"."TRAN_DATE"(+)="JR02"."TRAN_DATE" AND
"JR02"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
35 - access("DEPP"."TYPE"(+)=SUBSTR("JR02"."DATA_01",1,4))
filter("JR02"."INT_CAT"="DEPP"."INT_CAT"(+) AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"DEPP"."TYPE"(+)=SUBSTR("JR02"."DATA_01",1,4))
37 - access("LONP"."ACCT_TYPE"(+)=SUBSTR("JR02"."DATA_01",1,4))
filter("JR02"."INT_CAT"="LONP"."INT_CAT"(+) AND
"LONP"."ACCT_TYPE"(+)=SUBSTR("JR02"."DATA_01",1,4))
46 - filter("JR03"."SYST"<>'GEN' AND ("JR03"."TRAN_DATE"=38362 OR
"JR03"."POST_DATE"=38362))
47 - filter("TXBI"."NON_FIN_TXN_23"='0')
48 - access("TXBI"."TRAN_CODE"=SUBSTR("JR03"."TRAN_CODE",2,6))
50 - access("MCAD"."JRNL_DATE"(+)="JR03"."JR03_DATE" AND
"MCAD"."JRNL_NO"(+)="JR03"."JRNL_NO")
51 - access("JR03"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
filter("OCID"."TRAN_DATE"(+)="JR03"."TRAN_DATE" AND
"JR03"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
53 - access("DEPP"."TYPE"(+)=SUBSTR("JR03"."DATA_01",1,4))
filter("JR03"."INT_CAT"="DEPP"."INT_CAT"(+) AND
"DEPP"."TYPE"(+)=SUBSTR("JR03"."DATA_01",1,4))
55 - access("LONP"."ACCT_TYPE"(+)=SUBSTR("JR03"."DATA_01",1,4))
filter("JR03"."INT_CAT"="LONP"."INT_CAT"(+) AND
"LONP"."ACCT_TYPE"(+)=SUBSTR("JR03"."DATA_01",1,4))
64 - filter("JR04"."SYST"<>'GEN' AND ("JR04"."TRAN_DATE"=38362 OR
"JR04"."POST_DATE"=38362))
65 - filter("TXBI"."NON_FIN_TXN_23"='0')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
66 - access("TXBI"."TRAN_CODE"=SUBSTR("JR04"."TRAN_CODE",2,6))
68 - access("MCAD"."JRNL_DATE"(+)="JR04"."JR04_DATE" AND
"MCAD"."JRNL_NO"(+)="JR04"."JRNL_NO")
69 - access("JR04"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
filter("OCID"."TRAN_DATE"(+)="JR04"."TRAN_DATE" AND
"JR04"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
71 - access("DEPP"."TYPE"(+)=SUBSTR("JR04"."DATA_01",1,4))
filter("JR04"."INT_CAT"="DEPP"."INT_CAT"(+) AND
"DEPP"."TYPE"(+)=SUBSTR("JR04"."DATA_01",1,4))
73 - access("LONP"."ACCT_TYPE"(+)=SUBSTR("JR04"."DATA_01",1,4))
filter("JR04"."INT_CAT"="LONP"."INT_CAT"(+) AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"LONP"."ACCT_TYPE"(+)=SUBSTR("JR04"."DATA_01",1,4))
82 - filter("JR05"."SYST"<>'GEN' AND ("JR05"."TRAN_DATE"=38362 OR
"JR05"."POST_DATE"=38362))
83 - filter("TXBI"."NON_FIN_TXN_23"='0')
84 - access("TXBI"."TRAN_CODE"=SUBSTR("JR05"."TRAN_CODE",2,6))
86 - access("MCAD"."JRNL_DATE"(+)="JR05"."JR05_DATE" AND
"MCAD"."JRNL_NO"(+)="JR05"."JRNL_NO")
87 - access("JR05"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
filter("OCID"."TRAN_DATE"(+)="JR05"."TRAN_DATE" AND
"JR05"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
89 - access("DEPP"."TYPE"(+)=SUBSTR("JR05"."DATA_01",1,4))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
filter("JR05"."INT_CAT"="DEPP"."INT_CAT"(+) AND
"DEPP"."TYPE"(+)=SUBSTR("JR05"."DATA_01",1,4))
91 - access("LONP"."ACCT_TYPE"(+)=SUBSTR("JR05"."DATA_01",1,4))
filter("JR05"."INT_CAT"="LONP"."INT_CAT"(+) AND
"LONP"."ACCT_TYPE"(+)=SUBSTR("JR05"."DATA_01",1,4))
100 - filter("JR06"."SYST"<>'GEN' AND ("JR06"."TRAN_DATE"=38362 OR
"JR06"."POST_DATE"=38362))
101 - filter("TXBI"."NON_FIN_TXN_23"='0')
102 - access("TXBI"."TRAN_CODE"=SUBSTR("JR06"."TRAN_CODE",2,6))
104 - access("MCAD"."JRNL_DATE"(+)="JR06"."JR06_DATE" AND
"MCAD"."JRNL_NO"(+)="JR06"."JRNL_NO")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
105 - access("JR06"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
filter("OCID"."TRAN_DATE"(+)="JR06"."TRAN_DATE" AND
"JR06"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
107 - access("DEPP"."TYPE"(+)=SUBSTR("JR06"."DATA_01",1,4))
filter("JR06"."INT_CAT"="DEPP"."INT_CAT"(+) AND
"DEPP"."TYPE"(+)=SUBSTR("JR06"."DATA_01",1,4))
109 - access("LONP"."ACCT_TYPE"(+)=SUBSTR("JR06"."DATA_01",1,4))
filter("JR06"."INT_CAT"="LONP"."INT_CAT"(+) AND
"LONP"."ACCT_TYPE"(+)=SUBSTR("JR06"."DATA_01",1,4))
118 - filter("JR07"."SYST"<>'GEN' AND ("JR07"."TRAN_DATE"=38362 OR
"JR07"."POST_DATE"=38362))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
119 - filter("TXBI"."NON_FIN_TXN_23"='0')
120 - access("TXBI"."TRAN_CODE"=SUBSTR("JR07"."TRAN_CODE",2,6))
122 - access("MCAD"."JRNL_DATE"(+)="JR07"."JR07_DATE" AND
"MCAD"."JRNL_NO"(+)="JR07"."JRNL_NO")
123 - access("JR07"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
filter("OCID"."TRAN_DATE"(+)="JR07"."TRAN_DATE" AND
"JR07"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
125 - access("DEPP"."TYPE"(+)=SUBSTR("JR07"."DATA_01",1,4))
filter("JR07"."INT_CAT"="DEPP"."INT_CAT"(+) AND
"DEPP"."TYPE"(+)=SUBSTR("JR07"."DATA_01",1,4))
127 - access("LONP"."ACCT_TYPE"(+)=SUBSTR("JR07"."DATA_01",1,4))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
filter("JR07"."INT_CAT"="LONP"."INT_CAT"(+) AND
"LONP"."ACCT_TYPE"(+)=SUBSTR("JR07"."DATA_01",1,4))
136 - filter("JR08"."SYST"<>'GEN' AND ("JR08"."TRAN_DATE"=38362 OR
"JR08"."POST_DATE"=38362))
137 - filter("TXBI"."NON_FIN_TXN_23"='0')
138 - access("TXBI"."TRAN_CODE"=SUBSTR("JR08"."TRAN_CODE",2,6))
140 - access("MCAD"."JRNL_DATE"(+)="JR08"."JR08_DATE" AND
"MCAD"."JRNL_NO"(+)="JR08"."JRNL_NO")
141 - access("JR08"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))
filter("OCID"."TRAN_DATE"(+)="JR08"."TRAN_DATE" AND
"JR08"."JRNL_NO"=TO_CHAR("OCID"."JRNL_NO"(+)))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
143 - access("DEPP"."TYPE"(+)=SUBSTR("JR08"."DATA_01",1,4))
filter("JR08"."INT_CAT"="DEPP"."INT_CAT"(+) AND
"DEPP"."TYPE"(+)=SUBSTR("JR08"."DATA_01",1,4))
145 - access("LONP"."ACCT_TYPE"(+)=SUBSTR("JR08"."DATA_01",1,4))
filter("JR08"."INT_CAT"="LONP"."INT_CAT"(+) AND
"LONP"."ACCT_TYPE"(+)=SUBSTR("JR08"."DATA_01",1,4))

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-12 : 20:19:05

I could if it was SQL Server, I don't know Oracle well enough. I DO know that pre-8i or -9i versions of Oracle had NOTHING of a query optimizer, so the way you write the query and the order in which the tables are listed makes a huge difference in performance. Not much to do there except experiment.

From what the plan is showing, there's a lot of table scans being done, and quite a few nested loops. If the tables are large this will be a significant performance killer. You also appear to be joining several identical/similar tables (JR01 - JR08) to the same tables and UNIONing the results. A better way to do this would be to take those other tables, join and query them once, then put those results into a temporary table. This should cut down on the rows needing to be processed; it may not change the query plan but should reduce the amount of work needed. You may also be able to index the temp table in a way that the optimizer can join to it more effectively (SQL Server can do this, don't know about Oracle).

The BEST way to solve this problem is to change the JR01 - JR08 tables, so instead of having 8 identical tables you have only one. Any time you have multiple tables of identical or near-identical structure, that store the same kind of data, it is a sign of poor database design. As long as you keep this design this query and others like it will continue to perform poorly. I know you may not be able to change it, but if you have any opportunity to do so you should.

kumarpav
Starting Member

18 Posts

Posted - 2005-01-13 : 13:37:38

Hi,

Thank u for ur Response....
Here i am Combining all the tables from jr01 to jr08 as a single table.Now the query is working fast...

Can u suggest me anything regarding below query...

================================================

SELECT
SUBSTR(LPAD(RTRIM(A.ISSUE_BANK,' '),3,'0'),3,1),
LPAD(TO_CHAR(RTRIM(A.ISSUE_BRCH,' ')),5,'0'),
TO_CHAR(TO_DATE(TO_NUMBER(A.ISSUE_DATE)
+2415020,'J'),'YYYYMMDD'),
DECODE(LPAD(TO_CHAR(A.DRAFT_STAT),2,'0'),1,1,3,5),
1,
SUBSTR(LPAD(RTRIM(A.PAY_BANK,' '),3,'0'),3,1),
'0'||
SUBSTR(LPAD(RTRIM(A.PAY_BANK,' '),3,'0'),3,1)||
SUBSTR(LPAD(TO_CHAR(RTRIM(A.PAY_BRCH,' '))
,5,'0'),3,5),
TO_CHAR(TO_DATE(TO_NUMBER(A.DRAFT_PAY_DATE)
+2415020,'J'),'YYYYMMDD'),
NVL(SUBSTR(A.DRAFT_PREFIX,1,8),' '),
SUBSTR(LPAD(TO_CHAR(A.DRAFT_NUM),10,'0'),4,10),
LPAD(TO_CHAR(A.TOT_DRAFT_AMT),17,'0'),
TO_CHAR(TO_DATE(TO_NUMBER(A.STAT_CHANGE_DATE)
+2415020,'J'),'YYYYMMDD'),
NVL(SUBSTR(LPAD(RTRIM(B.CIRCLE_CODE,' '),3,'0'),2,3)
||SUBSTR(LPAD(RTRIM(B.MODULE_CODE,' '),3,'0'),2,3)
||SUBSTR(LPAD(RTRIM(B.REGION_CODE,' ')
,3,'0'),2,3),'000000'),
SUBSTR(LPAD(TO_CHAR(A.ORIG_DRA_NUM),10,'0'),4,10),
NVL(SUBSTR(LPAD(RTRIM(A.ORIG_DRA_PRE,' ')
,10,'0'),3,10),'00000000')
FROM DDFT A,BRHM B
WHERE
((A.DRAFT_STAT = 1
AND A.ISSUE_DATE = 38221) OR
(A.DRAFT_STAT = 3
AND A.STAT_CHANGE_DATE = 38221))
AND (A.DRAFT_TYPE ='1' OR A.DRAFT_TYPE ='2')
AND A.ASSO_BANK = 'Y'
AND (A.ISSUE_BANK = 00
OR A.PAY_BANK = 00)
AND '00300000000000'||
LPAD(TO_CHAR(A.ISSUE_BRCH),5,'0') = B.KEY_1
and A.INST_NO = '003'

UNION

SELECT
SUBSTR(LPAD(RTRIM(G.ISSUE_BANK,' '),3,'0'),3,1),
LPAD(TO_CHAR(RTRIM(G.ISSUE_BRCH,' ')),5,'0'),
TO_CHAR(TO_DATE(TO_NUMBER(G.ISSUE_DATE)
+2415020,'J'),'YYYYMMDD'),
DECODE(LPAD(TO_CHAR(G.DRAFT_STAT),2,'0'),3,1),
1,
SUBSTR(LPAD(RTRIM(G.PAY_BANK,' '),3,'0'),3,1),
'0'||
SUBSTR(LPAD(RTRIM(G.PAY_BANK,' '),3,'0'),3,1)||
SUBSTR(LPAD(TO_CHAR(RTRIM(G.PAY_BRCH,' '))
,5,'0'),3,5),
TO_CHAR(TO_DATE(TO_NUMBER(G.DRAFT_PAY_DATE)
+2415020,'J'),'YYYYMMDD'),
NVL(SUBSTR(G.DRAFT_PREFIX,1,8),' '),
SUBSTR(LPAD(TO_CHAR(G.DRAFT_NUM),10,'0'),4,10),
LPAD(TO_CHAR(G.TOT_DRAFT_AMT),17,'0'),
TO_CHAR(TO_DATE(TO_NUMBER(G.STAT_CHANGE_DATE)
+2415020,'J'),'YYYYMMDD'),
NVL(SUBSTR(LPAD(RTRIM(H.CIRCLE_CODE,' '),3,'0'),2,3)
||SUBSTR(LPAD(RTRIM(H.MODULE_CODE,' '),3,'0'),2,3)
||SUBSTR(LPAD(RTRIM(H.REGION_CODE,' ')
,3,'0'),2,3),'000000'),
SUBSTR(LPAD(TO_CHAR(G.ORIG_DRA_NUM),10,'0'),4,10),
NVL(SUBSTR(LPAD(RTRIM(G.ORIG_DRA_PRE,' ')
,10,'0'),3,10),'00000000')
FROM DDFT G,BRHM H
WHERE
G.DRAFT_STAT = 3 AND
G.STAT_CHANGE_DATE = 38221 AND
G.ISSUE_DATE = 38221
AND (G.DRAFT_TYPE ='1' OR G.DRAFT_TYPE ='2')
AND G.ASSO_BANK = 'Y'
AND (G.ISSUE_BANK = 00
OR G.PAY_BANK = 00)
AND '00300000000000'||
LPAD(TO_CHAR(G.ISSUE_BRCH),5,'0') = H.KEY_1
and G.INST_NO= '003'

UNION

SELECT
NVL(SUBSTR(LPAD(RTRIM(C.ISSUE_BANK_CODE,' ')
,3,'0'),3,1),'0'),
LPAD(TO_CHAR(RTRIM(C.ISSUE_BRCH_NO,' ')),5,'0'),
TO_CHAR(TO_DATE(TO_NUMBER(C.ISSUE_DATE)
+2415020,'J'),'YYYYMMDD'),
DECODE(LPAD(TO_CHAR(C.TT_STAT),2,'0'),1,1),
3,
NVL(SUBSTR(LPAD(RTRIM(C.PAY_BANK_CODE,' ')
,3,'0'),3,1),'0'),
'0'||
NVL(SUBSTR(LPAD(RTRIM(C.PAY_BANK_CODE,' ')
,3,'0'),3,1),'0')||
SUBSTR(LPAD(TO_CHAR(RTRIM(C.PAY_BRN_NO,' '))
,5,'0'),3,5),
TO_CHAR(TO_DATE(TO_NUMBER(C.PAID_DATE)
+2415020,'J'),'YYYYMMDD'), ' ',
SUBSTR(LPAD(TO_CHAR(C.TT_NO),10,'0'),4,10),
LPAD(TO_CHAR(C.TT_AMT),17,'0'),
TO_CHAR(TO_DATE(TO_NUMBER(C.TT_STAT_CHG_DATE)
+2415020,'J'),'YYYYMMDD'),
NVL(SUBSTR(LPAD(RTRIM(D.CIRCLE_CODE,' '),3,'0'),2,3)
||SUBSTR(LPAD(RTRIM(D.MODULE_CODE,' '),3,'0'),2,3)
||SUBSTR(LPAD(RTRIM(D.REGION_CODE,' ')
,3,'0'),2,3),'000000'),
'0000000',
'00000000'
FROM TTRF C,BRHM D
WHERE
C.TT_STAT = 1
AND C.ISSUE_DATE = 38221
AND ASSO_BANK_FLAG = 'Y'
AND (TO_NUMBER(C.ISSUE_BANK_CODE) = 00
OR TO_NUMBER(C.PAY_BANK_CODE) = 00)
AND '00300000000000'||
LPAD(TO_CHAR(C.PAY_BRN_NO),5,'0') = D.KEY_1
and C.INST_NO = '003'

UNION

SELECT
NVL(SUBSTR(LPAD(RTRIM(E.PAY_BANK_CODE,' ')
,3,'0'),3,1),'0'),
LPAD(TO_CHAR(RTRIM(E.PAY_BRN_NO,' ')),5,'0'),
TO_CHAR(TO_DATE(TO_NUMBER(E.PAID_DATE)
+2415020,'J'),'YYYYMMDD'),
DECODE(LPAD(TO_CHAR(E.TT_STAT),2,'0'),2,3,5,3,10,4),
3,
NVL(SUBSTR(LPAD(RTRIM(E.ISSUE_BANK_CODE,' ')
,3,'0'),3,1),'0'),
'0'||
NVL(SUBSTR(LPAD(RTRIM(E.ISSUE_BANK_CODE,' ')
,3,'0'),3,1),'0')||
SUBSTR(LPAD(TO_CHAR(RTRIM(E.ISSUE_BRCH_NO,' '))
,5,'0'),3,5),
TO_CHAR(TO_DATE(TO_NUMBER(E.ISSUE_DATE)
+2415020,'J'),'YYYYMMDD'),
' ',
SUBSTR(LPAD(TO_CHAR(E.TT_NO),10,'0'),4,10),
LPAD(TO_CHAR(E.TT_AMT),17,'0'),
TO_CHAR(TO_DATE(TO_NUMBER(E.TT_STAT_CHG_DATE)
+2415020,'J'),'YYYYMMDD'),
NVL(SUBSTR(LPAD(RTRIM(F.CIRCLE_CODE,' '),3,'0'),2,3)
||SUBSTR(LPAD(RTRIM(F.MODULE_CODE,' '),3,'0'),2,3)
||SUBSTR(LPAD(RTRIM(F.REGION_CODE,' ')
,3,'0'),2,3),'000000'),
'0000000',
'00000000'
FROM TTRF E,BRHM F
WHERE
(((E.TT_STAT = 2 OR E.TT_STAT = 5)
AND E.PAID_DATE = 38221) OR
(E.TT_STAT = 10
AND E.TT_STAT_CHG_DATE = 38221))
AND ASSO_BANK_FLAG = 'Y'
AND (TO_NUMBER(E.ISSUE_BANK_CODE) = 00
OR TO_NUMBER(E.PAY_BANK_CODE) = 00)
AND '00300000000000'||
LPAD(TO_CHAR(E.PAY_BRN_NO),5,'0') = F.KEY_1
and E.INST_NO = '003'

CREATE TABLE TTRF
(INST_NO CHAR (03) NOT NULL,
JRNL_NO CHAR (09) NOT NULL,
JRNL_DATE CHAR (09) NOT NULL,
INTERM_GLACC_NO NUMBER (16) DEFAULT (0),
IBIT_GL_ACCT_NO NUMBER (16) DEFAULT (0),
CUS_FROM_ACCT_NO NUMBER (16) DEFAULT (0),
CUS_TO_ACCT_NO NUMBER (16) DEFAULT (0),
ISSUE_BANK_CODE CHAR (03) DEFAULT (LPAD(CHR(0),003,CHR(0))),
ISSUE_BRCH_NO NUMBER (05) DEFAULT (0),
ISSUE_DATE NUMBER (09) DEFAULT (0),
ISSUE_BRANCH_NAME CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
PAY_BANK_CODE CHAR (03) DEFAULT (LPAD(CHR(0),003,CHR(0))),
PAY_BRN_NO NUMBER (05) DEFAULT (0),
PAID_DATE NUMBER (09) DEFAULT (0),
PAY_BRN_NAME CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
TT_NO NUMBER (10) DEFAULT (0),
TT_VALUE_DATE NUMBER (09) DEFAULT (0),
TT_AMT NUMBER (17,3) DEFAULT (0),
BEN_NAME CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
BEN_ADD1 CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
BEN_ADD2 CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
BEN_ADD3 CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
CUS_NAME CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
CUS_ADD1 CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
CUS_ADD2 CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
CUS_ADD3 CHAR (30) DEFAULT (LPAD(CHR(0),030,CHR(0))),
CUS_PAN_NO CHAR (15) DEFAULT (LPAD(CHR(0),015,CHR(0))),
BRS_BANK_ID CHAR (12) DEFAULT (LPAD(CHR(0),012,CHR(0))),
BRS_BRCH_NO NUMBER (05) DEFAULT (0),
TT_STAT NUMBER (02) DEFAULT (0),
TT_PREV_STAT NUMBER (02) DEFAULT (0),
TT_STAT_CHG_DATE NUMBER (09) DEFAULT (0),
TT_MODE_OF_ISSUE NUMBER (02) DEFAULT (0),
TT_RBI_SCHEME CHAR (01) DEFAULT (LPAD(CHR(0),001,CHR(0))),
ADV_REC_FLAG CHAR (01) DEFAULT (LPAD(CHR(0),001,CHR(0))),
TXN_JRNL_NO NUMBER (09) DEFAULT (0),
TELEGRAM_NO NUMBER (10) DEFAULT (0),
ASSO_BANK_FLAG CHAR (01) DEFAULT (LPAD(CHR(0),001,CHR(0))),
INTEREST_PAID CHAR (01) DEFAULT (LPAD(CHR(0),001,CHR(0))),
STEPS_IDENT CHAR (01) DEFAULT (LPAD(CHR(0),001,CHR(0))),
COMM_AMT NUMBER (17,3) DEFAULT (0),
PAR_FLAG CHAR (01) DEFAULT (LPAD(CHR(0),001,CHR(0))),
ADVICE_REC_DATE NUMBER (09) DEFAULT (0),
CORRECT_FLAG CHAR (01) DEFAULT (LPAD(CHR(0),001,CHR(0))),
CONSTRAINT TTRFPK
PRIMARY KEY (INST_NO,
JRNL_NO,
JRNL_DATE),
CONSTRAINT TTRF_AK01
UNIQUE (INST_NO,
TT_NO,
ISSUE_DATE,
ISSUE_BANK_CODE,
ISSUE_BRCH_NO,
JRNL_NO,
JRNL_DATE),
CONSTRAINT TTRF_AK02
UNIQUE (INST_NO,
ISSUE_BRCH_NO,
TT_NO,
JRNL_NO,
JRNL_DATE));

=================================================================

CREATE TABLE BRHM
(KEY_1 CHAR (19) NOT NULL,
STAT CHAR (2) DEFAULT (LPAD(CHR(0),002,CHR(0))),
AREA NUMBER (3) DEFAULT (0),
SUB_AREA NUMBER (3) DEFAULT (0),
DTE_LAST_CHG NUMBER (9) DEFAULT (0),
PWORD_AUTH_BTT CHAR (4) DEFAULT (LPAD(CHR(0),004,CHR(0))),
REGION_NO NUMBER (3) DEFAULT (0),
FIL01 CHAR (1) DEFAULT (LPAD(CHR(0),001,CHR(0))),
BR_NAME CHAR (40) DEFAULT (LPAD(CHR(0),040,CHR(0))),
AGNT_NO CHAR (2) DEFAULT (LPAD(CHR(0),002,CHR(0))),
ACC_RNGE_FROM_01 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_01 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_02 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_02 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_03 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_03 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_04 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_04 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_05 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_05 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_06 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_06 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_07 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_07 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_08 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_08 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_09 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_09 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_10 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_10 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_11 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_11 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_12 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_12 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_13 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_13 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_14 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_14 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_15 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_15 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_16 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_16 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_17 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_16 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_17 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_17 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_18 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_18 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_19 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_19 NUMBER (17) DEFAULT (0),
ACC_RNGE_FROM_20 NUMBER (17) DEFAULT (0),
ACC_RNGE_TO_20 NUMBER (17) DEFAULT (0),
MANAGERS_NAME CHAR (40) DEFAULT (LPAD(CHR(0),040,CHR(0))),
ADDRESS_1 CHAR (40) DEFAULT (LPAD(CHR(0),040,CHR(0))),
ADDRESS_2 CHAR (40) DEFAULT (LPAD(CHR(0),040,CHR(0))),
ADDRESS_3 CHAR (40) DEFAULT (LPAD(CHR(0),040,CHR(0))),
POST_CODE CHAR (8) DEFAULT (LPAD(CHR(0),008,CHR(0))),
PHONE_NO CHAR (12) DEFAULT (LPAD(CHR(0),012,CHR(0))),
REP_NO_01 CHAR (3) DEFAULT (LPAD(CHR(0),003,CHR(0))),
REP_DESTN_01 CHAR (1) DEFAULT (LPAD(CHR(0),001,CHR(0))),
REP_TIME_01 CHAR (1) DEFAULT (LPAD(CHR(0),001,CHR(0))),
REP_NO_02 CHAR (3) DEFAULT (LPAD(CHR(0),003,CHR(0))),
REP_DESTN_02 CHAR (1) DEFAULT (LPAD(CHR(0),001,CHR(0))),
REP_TIME_02 CHAR (1) DEFAULT (LPAD(CHR(0),001,CHR(0))),
REP_NO_03 CHAR (3) DEFAULT (LPAD(CHR(0),003,CHR(0))),
REP_DESTN_03 CHAR (1) DEFAULT (LPAD(CHR(0),001,CHR(0))),
REP_TIME_03 CHAR (1) DEFAULT (LPAD(CHR(0),001,CHR(0)))

CONSTRAINT BRHMPK
PRIMARY KEY (KEY_1),
CONSTRAINT BRHM_AK01
UNIQUE (CORP_OFFICE_CODE,
CIRCLE_CODE,
NETWORK_CODE,
MODULE_CODE,
REGION_CODE,
KEY_1),
CONSTRAINT BRHM_AK02
UNIQUE (BR_SHORT_NAME,
KEY_1),
CONSTRAINT BRHM_AK03
UNIQUE (MICR_CODE));

===================================================================================

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT UNIQUE | | | | |
| 2 | UNION-ALL | | | | |
| 3 | NESTED LOOPS | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| DDFT | | | |
|* 5 | INDEX RANGE SCAN | DDFT_AK04 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| BRHM | | | |

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
|* 7 | INDEX UNIQUE SCAN | BRHMPK | | | |
| 8 | NESTED LOOPS | | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID| DDFT | | | |
|* 10 | INDEX RANGE SCAN | DDFT_AK04 | | | |
| 11 | TABLE ACCESS BY INDEX ROWID| BRHM | | | |
|* 12 | INDEX UNIQUE SCAN | BRHMPK | | | |
| 13 | NESTED LOOPS | | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| TTRF | | | |
|* 15 | INDEX RANGE SCAN | TTRF_AK01 | | | |
| 16 | TABLE ACCESS BY INDEX ROWID| BRHM | | | |
|* 17 | INDEX UNIQUE SCAN | BRHMPK | | | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 18 | NESTED LOOPS | | | | |
|* 19 | TABLE ACCESS BY INDEX ROWID| TTRF | | | |
|* 20 | INDEX RANGE SCAN | TTRF_AK02 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID| BRHM | | | |
|* 22 | INDEX UNIQUE SCAN | BRHMPK | | | |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter(("A"."ISSUE_BANK"=00 OR "A"."PAY_BANK"=00) AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"A"."ASSO_BANK"='Y' AND ("A"."DRAFT_TYPE"='1' OR "A"."DRAFT_TYPE"=
'2') AND

("A"."DRAFT_STAT"=1 AND "A"."ISSUE_DATE"=38221 OR "A"."DRAFT_STAT"
=3 AND

"A"."STAT_CHANGE_DATE"=38221))
5 - access("A"."INST_NO"='003')
7 - access("B"."KEY_1"='00300000000000'||LPAD(TO_CHAR("A"."ISSUE_BRCH"),5,'
0'))
9 - filter(("G"."ISSUE_BANK"=00 OR "G"."PAY_BANK"=00) AND

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
"G"."ASSO_BANK"='Y' AND ("G"."DRAFT_TYPE"='1' OR "G"."DRAFT_TYPE"=
'2') AND

"G"."ISSUE_DATE"=38221 AND "G"."STAT_CHANGE_DATE"=38221)
10 - access("G"."INST_NO"='003' AND "G"."DRAFT_STAT"=3)
filter("G"."DRAFT_STAT"=3)
12 - access("H"."KEY_1"='00300000000000'||LPAD(TO_CHAR("G"."ISSUE_BRCH"),5,'
0'))
14 - filter((TO_NUMBER("C"."ISSUE_BANK_CODE")=00 OR
TO_NUMBER("C"."PAY_BANK_CODE")=00) AND "C"."ASSO_BANK_FLAG"='Y' AN
D

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

"C"."TT_STAT"=1)
15 - access("C"."INST_NO"='003' AND "C"."ISSUE_DATE"=38221)
filter("C"."ISSUE_DATE"=38221)
17 - access("D"."KEY_1"='00300000000000'||LPAD(TO_CHAR("C"."PAY_BRN_NO"),5,'
0'))
19 - filter((TO_NUMBER("E"."ISSUE_BANK_CODE")=00 OR
TO_NUMBER("E"."PAY_BANK_CODE")=00) AND "E"."ASSO_BANK_FLAG"='Y' AN
D

(("E"."TT_STAT"=2 OR "E"."TT_STAT"=5) AND "E"."PAID_DATE"=38221 OR

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

"E"."TT_STAT"=10 AND "E"."TT_STAT_CHG_DATE"=38221))
20 - access("E"."INST_NO"='003')
22 - access("F"."KEY_1"='00300000000000'||LPAD(TO_CHAR("E"."PAY_BRN_NO"),5,'
0'))

Note: rule based optimization

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-13 : 20:23:14

quote:
Note: rule based optimization

If I recall correctly, this is Oracle's term for "we'll run it exactly as you wrote it, even if it's the worst possible plan."

The good news is you seem to have eliminated all of the outer joins and cut down a lot on the table scans. Maybe there is some optimization going on. The only other recommendations I can make are:

-reduce the number of UNIONs

-if you can't reduce them, and you know you don't have duplicate rows, change them to UNION ALL

-try to reduce the number of functions/formulas you have in the query, especially if they are used as join conditions in the WHERE clause

-if you're using a version of Oracle that supports ANSI JOIN syntax, USE IT instead of Oracle's bullshit WHERE clause join syntax. It will make the query much easier to read, and may even perform better if an optimizer actually kicks in. I don't know which version supports ANSI JOINs but I think it's 10i or later.

-look to some Oracle forums for more information, we're a SQL Server site and don't do much with other databases. http://dbforums.com is a good place to start.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources