Home » RDBMS Server » Performance Tuning » Data type conversion impact on performance
Data type conversion impact on performance [message #533063] Mon, 28 November 2011 01:21 Go to next message
patni123
Messages: 3
Registered: November 2011
Location: Hyderabad
Junior Member
my sql query has three tables in from clause so it has two join conditions and one where condition.

account_no is number data type
and v_account_no is varchar2() data type

The where clause is :

"where account_no=to_number(v_account_no)"
with this condition in my sql query has the cost 392

we just modify the where clause as

where v_account_no=to_char(account_no)
with this condition in the sql query has the cost 11.

can any one explain what is impact of this data type conversion
and difference between these two "to_number() and to_char()"
in performance wise to reduce the cost of query?







Re: Data type conversion impact on performance [message #533065 is a reply to message #533063] Mon, 28 November 2011 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet in one case Oracle is able to use an index and in the other case it is not.

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: Data type conversion impact on performance [message #533135 is a reply to message #533063] Mon, 28 November 2011 07:11 Go to previous messageGo to next message
jrnayak
Messages: 35
Registered: November 2011
Location: London
Member
No need to convert the data externally using to_char or to_number. Oracle will do an internal conversion if the data types are compatible. If you really need to do it externally , it would be better if you change the data type of the variable rather than using to_char or to_number on a column of a table.
Re: Data type conversion impact on performance [message #533141 is a reply to message #533135] Mon, 28 November 2011 07:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER ever rely on implicit conversion.
ALWAYS do them explicitly but possibly for expressions in SELECT clause.

Regards
Michel
Re: Data type conversion impact on performance [message #533254 is a reply to message #533141] Tue, 29 November 2011 01:46 Go to previous messageGo to next message
patni123
Messages: 3
Registered: November 2011
Location: Hyderabad
Junior Member
Hi Michel,

Exactly in one case it is going with index in other case not.

"where v_account_no=to_char(account_no)" in this case oracle is able to use index.

"where account_no=to_number(v_account_no)" But in this case goes to full table scan.

Eventhough we have indexes on both columns.

can you please elobarate why like this?
Is it because of to_number() function?
Re: Data type conversion impact on performance [message #533260 is a reply to message #533254] Tue, 29 November 2011 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post both execution plans.

Regards
Michel
Re: Data type conversion impact on performance [message #533265 is a reply to message #533260] Tue, 29 November 2011 02:14 Go to previous messageGo to next message
patni123
Messages: 3
Registered: November 2011
Location: Hyderabad
Junior Member
Please find execution plans in attached document.

Thanks.

[Updated on: Tue, 29 November 2011 02:21]

Report message to a moderator

Re: Data type conversion impact on performance [message #533270 is a reply to message #533265] Tue, 29 November 2011 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry your images are too small I don't see anything.
Read the links I provided and post accordingly.

Regards
Michel
Re: Data type conversion impact on performance [message #533286 is a reply to message #533270] Tue, 29 November 2011 03:59 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Both columns? I assumed v_account_no was a variable not a column. What exact indexes do you have on which tables/columns?
Previous Topic: Query performance decreased between order by ASC/DESC
Next Topic: WINDOW SORT
Goto Forum:
  


Current Time: Thu Mar 28 13:59:42 CDT 2024