SQL code tuning

[Tom]

I have an application running on my database with a bit of code that takes forever to run. I have the code showing below with an explain plan of what it is doing. Can anyone help me by giving me pointers on how to speed this SQL up.
My database is running in Rule based mode as the application can only use this mode. I am running on oracle 7.3.4.5 on a UNix box.


SQL> explain plan set statement_id = 'A' for
2 SELECT RS.DESCRIPTION,RS.STAGE_ORDER FROM FDR_REC_HISTS R,FDR_SUSP_HISTS
S,FDR_REC_STAGES RS WHERE R.ACC_ID = S.ACC_ID AND
3 R.ACC_ID = :b1 AND R.DEBT_SEQUENCE = S.DEBT_SEQUENCE AND
4 R.REC_STAGE = RS.REC_STAGE AND RS.STAGE_ORDER = (SELECT
5 MAX(RS1.STAGE_ORDER) FROM FDR_REC_STAGES RS1,FDR_REC_HISTS R1 WHERE
6 R1.ACC_ID = :b1 AND R1.REC_STAGE = RS1.REC_STAGE AND R1.CANCELDATE
7 IS NULL AND R1.CANCEL_ID IS NULL ) AND (S.EXPIRYDATE IS NULL
8 OR S.EXPIRYDATE > SYSDATE ) AND R.REC_STAGE = RS.REC_STAGE
9 ;
SQL> @xplread
SQL> set pages 5000
SQL> set lines 132
SQL> set echo off

Execution Plan

--------------------------------------------------------------------------------
----------------------------------------------------
SELECT STATEMENT

NESTED LOOPS

NESTED LOOPS

TABLE ACCESS BY ROWID FDR_REC_STAGES

INDEX RANGE SCAN REC_STAGE_I_ORDER NON-UNIQUE

SORT AGGREGATE

NESTED LOOPS

TABLE ACCESS BY ROWID FDR_REC_HISTS

INDEX RANGE SCAN REC_HIST_PK UNIQUE

TABLE ACCESS BY ROWID FDR_REC_STAGES

INDEX UNIQUE SCAN REC_STAGE_PK UNIQUE

TABLE ACCESS BY ROWID FDR_REC_HISTS

INDEX RANGE SCAN REC_I_STAGE NON-UNIQUE

TABLE ACCESS BY ROWID FDR_SUSP_HISTS

INDEX RANGE SCAN FDR_SUSP_6 NON-UNIQUE

[tamilselvan]

Post the Index details. That may help to solve your problem.

[Tom]

Please find a list of the indexes, if you need any more information then please let me know.

TABLE_NAME INDEX_NAME COLUMNS

FDR_REC_STAGES REC_STAGE_I_ORDER (STAGE_ORDER)

FDR_REC_STAGES REC_STAGE_PK (REC_STAGE)

FDR_REC_HISTS REC_HIST_PK (ACC_ID, DEBT_SEQUENCE, REC_DATE)

FDR_REC_HISTS REC_I_STAGE (REC_STAGE)

FDR_SUSP_HISTS FDR_SUSP_HISTS (ADD_ID, DEBT_SEQUENCE)

Table FDR_REC_HISTS has 1000000 rows.
Table FDR_REC_STAGES has 29.
Table FDR_SUSP_HISTS has 4500 rows.


description of FDR_REC_STAGES :-
FDR_REC_STAGES (
REC_STAGE CHAR (3) NOT NULL,
DESCRIPTION VARCHAR2 (60) NOT NULL,
COMMENTS VARCHAR2 (40) NOT NULL,
PERCENT_COST NUMBER (4,2) NOT NULL,
STAGE_ORDER NUMBER (2) NOT NULL,
DEMINIMIS NUMBER (5,2),
INSTALMENT VARCHAR2 (1) DEFAULT 'N',
FN_IND VARCHAR2 (1),
REM_IND VARCHAR2 (1),
BAILIFF VARCHAR2 (1),
DSS_IND VARCHAR2 (1),
SUM_IND VARCHAR2 (1),
CSUM_IND VARCHAR2 (1),
LO_IND VARCHAR2 (1)

description of FDR_REC_HISTS :-

FDR_REC_HISTS (
ACC_ID VARCHAR2 (12) NOT NULL,
DEBT_SEQUENCE NUMBER (2) NOT NULL,
REC_DATE DATE NOT NULL,
CANCELDATE DATE,
CANCEL_ID CHAR (3),
COSTS NUMBER (10,2) NOT NULL,
REC_STAGE CHAR (3) NOT NULL,
REC_BY VARCHAR2 (8) NOT NULL,
COMMENTS VARCHAR2 (240),
DEBTAGEN NUMBER,
SUMMONS_ID NUMBER,
BALANCE NUMBER (10,2),
SHERDATE DATE,
S_O_CANCELLED VARCHAR2 (1),
RBT_ID VARCHAR2 (9)

description of FDR_SUSP_HISTS :-

FDR_SUSP_HISTS (
SUSP_DATE DATE NOT NULL,
EXPIRYDATE DATE,
ACC_ID VARCHAR2 (12) NOT NULL,
DEBT_SEQUENCE NUMBER (2) NOT NULL,
SUSP_TYPE CHAR (3) NOT NULL,
COMMENTS VARCHAR2 (240),
EXP_BY VARCHAR2 (8)