OraFAQ Forum: Performance Tuning » Data type conversion impact on performance

Home » RDBMS Server » Performance Tuning » Data type conversion impact on performance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data type conversion impact on performance [message #533063]

Mon, 28 November 2011 01:21

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?

Report message to a moderator

Re: Data type conversion impact on performance [message #533065 is a reply to message #533063]

Mon, 28 November 2011 01:23

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Data type conversion impact on performance [message #533135 is a reply to message #533063]

Mon, 28 November 2011 07:11

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.

Report message to a moderator

Re: Data type conversion impact on performance [message #533141 is a reply to message #533135]

Mon, 28 November 2011 07:42

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

NEVER ever rely on implicit conversion.
ALWAYS do them explicitly but possibly for expressions in SELECT clause.

Regards
Michel

Report message to a moderator

Re: Data type conversion impact on performance [message #533254 is a reply to message #533141]

Tue, 29 November 2011 01:46

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?

Report message to a moderator

Re: Data type conversion impact on performance [message #533260 is a reply to message #533254]

Tue, 29 November 2011 01:59

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post both execution plans.

Regards
Michel

Report message to a moderator

Re: Data type conversion impact on performance [message #533265 is a reply to message #533260]

Tue, 29 November 2011 02:14

patni123
Messages: 3
Registered: November 2011
Location: Hyderabad

Junior Member

Please find execution plans in attached document.

Thanks.

Attachment: performance issue.gif
(Size: 14.82KB, Downloaded 1179 times)

[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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Data type conversion impact on performance [message #533286 is a reply to message #533270]

Tue, 29 November 2011 03:59

cookiemonster
Messages: 13958
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?

Report message to a moderator

Previous Topic:	Query performance decreased between order by ASC/DESC
Next Topic:	WINDOW SORT

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 12:44:19 CST 2024