-
Hi oracle guru's
Please help me in tuning SQL statement. The details of indexes on table and results of Explain plan are :
INDEX DETAILS :
INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------------ -------------------- ---------------
IND_WLDT_APPDT APP_DATE 1
IND_WLDT_BC BILLCYCLE 1
IND_WLDT_COLAGE COL_AGING 1
IND_WLDT_PRG PRGCODE 1
IND_WLDT_SER SERVICE 1
IND_WLDT_STATUS STATUS 1
IND_WLDT_SVRAGE SVR_AGING 1
WD_PK BILLCYCLE 1
WD_PK APP_DATE 2
WD_PK SERVICE 3
WD_PK CUSTCODE 4
PLAN STATEMENT :
COLUMN plan FORMAT a70
SELECT lpad(' ', 3*level)||operation||'('||options||')
'||object_name||
' ' ||object_type plan
FROM plan_table
CONNECT BY PRIOR id = parent_id and statement_id='STMT1'
START WITH id = 1 and statement_id = 'STMT1';
PLAN RESULTS :
PLAN
----------------------------------------------------------------------
TABLE ACCESS(BY INDEX ROWID)
WL_DETAILS
INDEX(RANGE SCAN)
WD_PK UNIQUE
SORT(AGGREGATE)
INDEX(RANGE SCAN)
WD_PK UNIQUE
PLAN
----------------------------------------------------------------------
Please Help in tuning the above statement.
Thanks In Advance.
-
and which statement would that be? :)
-
Dear TerryD,
Sorry I have forgotten to supply the sequel statement. This is the statement :-
delete plan_table
where statement_id = 'STMT1'
/
EXPLAIN PLAN
SET STATEMENT_ID = 'STMT1'
FOR
select * from wl_details a
where a.billcycle = '03'
and a.app_date is not null
and a.service is not null
and a.custcode = '1.611832'
and a.app_date = (select max(b.app_date)
from wl_details b
where billcycle = '03'
and app_date is not null
and service is not null
and custcode = '1.611832')
Thanks
-
The problem is that you are going into the same table twice. The following SQL should help. I, of course, do not have the tables, so you may need to debug it a little first, but it should give you the idea. Also, this only works in 8i, AFAIK. Before that, you couldn't put an ORDER BY in a sub-select. Also, since you did not provide exact field names in your select, I was unable to remove the QUERY_ROWNUM from the final result. I'll assume this was for simplicity since one would never, of course, use a * in a production SELECT statement ;)
SELECT
*
FROM
(
SELECT
A.* ,
ROWNUM
AS QUERY_ROWNUM
FROM
WL_DETAILS A
WHERE
A.BILLCYCLE = '03'
A.APP_DATE IS NOT NULL
A.SERVICE IS NOT NULL
A.CUSTCODE = '1.611832'
ORDER BY
A.APP_DATE DESC
)
WHERE
QUERY_ROWNUM = 1
Another thought is to replace the IS NOT NULLs with > 'a' or < '1/1/80' or something to eliminate the NULL issue so any indexes on these columns could be used.
Hope this helps,
- Chris
-
How many distinct values are there in billcycle??
