Database Tuning (MOSC)

MOSC Banner

to_char affects performance

edited Jul 14, 2011 4:45AM in Database Tuning (MOSC) 6 commentsAnswered
Hello.

Our dev team reported that the performance of the following SQL

select col1 ,TO_CHAR(col2,'YYYYMMDD') , into :b0,:b1 from table1where ACCOUNT_ID in (:b9,:b10,:b11,:b12,:b13,:b14,:b15,:b16,:b17,:b18,:b19,:b20,:b21,:b22,:b23,:b24,:b25,:b26,:b27,:b28) order by col1, TO_CHAR(col2,'YYYYMMDD')takes the hit when the user to_char function (as opposite to simply reading col2 column which is a date column) and that performance is only affected when they run multiple SQLs like this one concurrently.  I can see how the to_char can create an extra overhead, but the claim is that the time difference is rather significant. and I am not sure why would that be the case .  Is there anything that can be done (other than removing the to_char)? thank youGene

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center