| RANGE SCAN VS FAST FULL SCAN [message #521363] |
Mon, 29 August 2011 04:46  |
patneel
Messages: 47
Registered: June 2007
Location: India
|
Member |
|
|
Hi,
I am adding condition in color to my existing query but my query taking too long time after that. This condition calculates last 3 month end dates from given COB (20110516).
SUMMARY is my summarized table and COB is my reference table
Without adding condition in query it gives
INDEX (RANGE SCAN)--PK_SUMMARY(INDEX(UNIQUE))
After adding condition in query it gives
INDEX(FAST FULL SCAN)--PK_SUMMARY(INDEX(UNIQUE))
Can anyone pls tell me how can i keep RANGE SCAN even after adding my new conditions?
SELECT
distinct
COB.COB_ID_C,
SUMMARY.SUMM_COB_ID_C
FROM
COB,
SUMMARY
WHERE
( SUMMARY.SUMM_COB_ID_C=COB.COB_ID_C )
AND
(
( to_date(COB.COB_ID_C,'YYYY-MM-DD') >= (to_date('20110516','YYYY-MM-DD')) - (1+12) AND (to_date(COB.COB_ID_C,'YYYY-MM-DD') <= (to_date('20110516','YYYY-MM-DD'))) )
OR
( COB.COB_ID_C IN
(
SELECT
COB.COB_ID_C
FROM
COB
WHERE
(
EXISTS (SELECT
Table__OP.FINAL_OP
FROM (SELECT
CASE when to_char(LAST_DAY_MON,'fmDy') in ('Sat','Sun')
then next_day(LAST_DAY_MON-7,'Friday')
else LAST_DAY_MON end AS FINAL_OP
FROM ( SELECT
last_day(add_months(dt, -1*line)) AS LAST_DAY_MON
FROM
(
select
To_Date(To_Char('20110516'),'YYYYMMDD') AS dt,
rownum AS line
from COB
where
rownum <=3
) ) )Table__OP WHERE TO_DATE (COB.COB_ID_C, 'YYYYMMDD') = Table__OP.FINAL_OP))) ) )
Thanks in Advance
|
|
|
|
|
|
| Re: RANGE SCAN VS FAST FULL SCAN [message #521367 is a reply to message #521366] |
Mon, 29 August 2011 05:10  |
patneel
Messages: 47
Registered: June 2007
Location: India
|
Member |
|
|
Formatted SQL--
SELECT DISTINCT COB.COB_ID_C,
SUMMARY.SUMM_COB_ID_C
FROM COB, SUMMARY
WHERE ( SUMMARY.SUMM_COB_ID_C = COB.COB_ID_C )
AND ( ( TO_DATE(COB.COB_ID_C, 'YYYY-MM-DD') >= (TO_DATE('20110516',
'YYYY-MM-DD')) - (1 + 12)
AND (TO_DATE(COB.COB_ID_C, 'YYYY-MM-DD') <= (TO_DATE('20110516', 'YYYY-MM-DD'))
) )
OR ( COB.COB_ID_C IN (
SELECT COB.COB_ID_C
FROM COB
WHERE ( EXISTS (
SELECT Table__OP.FINAL_OP
FROM (
SELECT CASE
WHEN TO_CHAR(LAST_DAY_MON, 'fmDy') IN ('Sat', 'Sun')
THEN
NEXT_DAY(LAST_DAY_MON - 7, 'Friday')
ELSE
LAST_DAY_MON
END AS FINAL_OP
FROM (
SELECT LAST_DAY(ADD_MONTHS(dt, - 1 * line)) AS LAST_DAY_MON
FROM (
SELECT TO_DATE(TO_CHAR('20110516'), 'YYYYMMDD') AS dt,
ROWNUM AS line
FROM COB
WHERE ROWNUM <= 3 ) ) )Table__OP
WHERE TO_DATE (COB.COB_ID_C, 'YYYYMMDD') = Table__OP.FINAL_OP))) ) )
It's taking twice amount of time to fetch no of records, if i pass last 3 month end dates with following simple then SQL taking very less time and not switching to FFS
COB.COB_ID_C IN ('20110228','20110331','20110429')
I want to know what modifications can be done by me in last 3 month end logic so that performance will be good..
[Updated on: Mon, 29 August 2011 05:11] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
