Home » RDBMS Server » Performance Tuning » RANGE SCAN VS FAST FULL SCAN
RANGE SCAN VS FAST FULL SCAN [message #521363] Mon, 29 August 2011 04:46 Go to next message
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 #521366 is a reply to message #521363] Mon, 29 August 2011 04:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If Oracle switch from RANGE SCAN to FAST FULL SCAN this is because it estimates it will perform faster.
If you think the opposite then tell us why.

For any peformances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: RANGE SCAN VS FAST FULL SCAN [message #521367 is a reply to message #521366] Mon, 29 August 2011 05:10 Go to previous messageGo to next message
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

Re: RANGE SCAN VS FAST FULL SCAN [message #521371 is a reply to message #521367] Mon, 29 August 2011 05:36 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Formatted SQL

Not at all, please read How to use [code] tags and make your code easier to read.

We have neither your tables, neither your indexes, neither your data, neither your execution, neither your statistics. We have NOTHING.

Read the link I provided and provide what is requested.

Quote:
I want to know what modifications can be done by me in last 3 month end logic

Pass them as static.

Regards
Michel
Previous Topic: SQL TUNING
Next Topic: Need to improve speed for UPDATE statement
Goto Forum:
  


Current Time: Thu Mar 28 16:34:47 CDT 2024