| How I can Eliminate the Full Table scan for the following query? [message #165554] |
Thu, 30 March 2006 22:26  |
srivishnuhari
Messages: 5
Registered: January 2006
Location: Chennai
|
Junior Member |
|
|
Hi,
I am struggling to eliminate one time Full table scan for the following query. Can anybody help for this?
There is unique index for eff_dte,client_asset_vehicle_id_k,create_ts in both tables.
SELECT A.EFF_DTE,A.CREATE_TS,--A.FMV,
A.VEHICLE_FMV_SRC_CD_CODE_K,
A.ENCUMBRANCE_AMT, A.VEHICLE_USE_CD_CODE_K,
A.PCT_OWNED,B.VEHICLE_UNAVAIL_RSN_CD_CODE_K,
A.CLIENT_ASSET_VEHICLE_ID_K,A.START_DTE
FROM (SELECT A.*
FROM CLIENT_ASSET_VEHICLE_DTL A
WHERE A.CREATE_TS=(SELECT MAX(A1.CREATE_TS) FROM
CLIENT_ASSET_VEHICLE_DTL A1
WHERE A1.CLIENT_ID_K = A.CLIENT_ID_K AND
A1.CLIENT_ASSET_VEHICLE_ID_K = A.CLIENT_ASSET_VEHICLE_ID_K AND
A1.EFF_DTE = A.EFF_DTE)
AND A.CLIENT_ID_K = 202100
AND A.CLIENT_ASSET_VEHICLE_ID_K = 141556) A,
(SELECT B.*
FROM CLIENT_ASSET_VEHICLE_UNAVAIL B
WHERE B.CREATE_TS = (SELECT MAX(B1.CREATE_TS)
FROM CLIENT_ASSET_VEHICLE_UNAVAIL B1
WHERE B1.CLIENT_ID_K = B.CLIENT_ID_K AND
B1.CLIENT_ASSET_VEHICLE_ID_K = B.CLIENT_ASSET_VEHICLE_ID_K AND
B1.EFF_DTE = B.EFF_DTE)
AND B.CLIENT_ID_K = 202100
AND B.CLIENT_ASSET_VEHICLE_ID_K = 141556) B
WHERE A.CLIENT_ID_K = B.CLIENT_ID_K(+)
AND A.CLIENT_ASSET_VEHICLE_ID_K = B.CLIENT_ASSET_VEHICLE_ID_K(+)
AND A.EFF_DTE = B.EFF_DTE(+)
AND A.CLIENT_ID_K = 202100
AND A.EFF_DTE < SYSDATE
AND A.CLIENT_ASSET_VEHICLE_ID_K = 141556
Explain Plan in SQLPlus
-----------------------
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=208)
1 0 HASH JOIN* (OUTER) (Cost=6 Card=1 Bytes=208) :Q255761
002
2 1 HASH JOIN* (Cost=2 Card=1 Bytes=159) :Q255761
002
3 2 TABLE ACCESS* (FULL) OF 'CLIENT_ASSET_VEHICLE_DTL' (Co :Q255761
st=1 Card=1 Bytes=111) 002
4 2 VIEW* OF 'VW_SQ_1' (Cost=1 Card=1 Bytes=48) :Q255761
000
5 4 SORT (GROUP BY) (Cost=1 Card=1 Bytes=48)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_ASSET_VEH
ICLE_DTL' (Cost=1 Card=1 Bytes=48)
7 6 INDEX (RANGE SCAN) OF 'PK_CLIENT_ASSET_VEHICLE_D
TL' (UNIQUE) (Cost=2 Card=1)
8 1 VIEW* (Cost=4 Card=1 Bytes=49) :Q255761
001
9 8 FILTER
10 9 SORT (GROUP BY) (Cost=4 Card=1 Bytes=165)
11 10 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_ASSET_VEH
ICLE_UNAVAIL' (Cost=1 Card=1 Bytes=117)
12 11 NESTED LOOPS (Cost=2 Card=1 Bytes=165)
13 12 TABLE ACCESS (BY INDEX ROWID) OF 'CLIENT_ASSET
_VEHICLE_UNAVAIL' (Cost=1 Card=1 Bytes=48)
14 13 INDEX (RANGE SCAN) OF 'PK_CL_***_VEHICLE_UNA
VAIL' (UNIQUE) (Cost=2 Card=1)
15 12 INDEX (RANGE SCAN) OF 'PK_CL_***_VEHICLE_UNAVA
IL' (UNIQUE) (Cost=1 Card=1)
1 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
A1.C0,A2.C0,A1.C1,A2.C1,A1.C2,A2.C2
2 PARALLEL_COMBINED_WITH_PARENT
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_FROM_SERIAL
8 PARALLEL_FROM_SERIAL
Explain Plan in TOAD
--------------------
HASH JOIN OUTER
HASH JOIN
FULL TABLE SCAN
VIEW
SORT GROUP BY
TABLE ACCESS BY ROWID
INDIX RANGE SCAN
VIEW
FILTER
SORT GROUP BY
TABLE ACCESS BY ROWID
NESTED LOOPS
TABLE ACCESS BY ROWID
INDEX RANGE SCAN
INDEX RANGE SCAN
Anybody, Can do the needful?
Thanks in advance.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
