Home » RDBMS Server » Performance Tuning » P&T Guide Question [Restructuring SQL Statements] (10g Release 2 (10.2) - Documention Related)
P&T Guide Question [Restructuring SQL Statements] [message #458259] Fri, 28 May 2010 04:51 Go to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
P&T guide p226

Hi, I'm reviewing the P&T guide [10g Release 2 (10.2) B14211-03], specifically the section under Restructuring SQL Statements.

I'm 100% clear on not using functions on indexed columns, makes perfect sense, however I'm confused by the detail in the upper half of p226 of the guide:

Quote:
When you need to use SQL functions on filters or join predicates, do not use them on
the columns on which you want to have an index; rather, use them on the opposite
side of the predicate, as in the following statement:

TO_CHAR(numcol) = varcol

rather than

varcol = TO_CHAR(numcol)



I'd always thought that the order of the predicate comparision made no difference, i.e. 'x=y' would be processed identically to 'y=x'. It might be my understanding but the quoted section makes sense and fits with my expectation up until then example given. I cant see any reason either of those statements would be handled differently by Oracle - assuming they are run on identical tables of course.


I'd be grateful if anyone could explain what they mean by this.

Thanks

Report message to a moderator

Re: P&T Guide Question [Restructuring SQL Statements] [message #458263 is a reply to message #458259] Fri, 28 May 2010 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is better to post a link to what you refers to then we can easily see it.

It is a bug in the documentation, they mean if you have an index on "numcol" then do not use "varcol = TO_CHAR(numcol)" but "numcol = TO_NUMBER(varcol)".

(I should think if you have this case between 2 columns then there is a bug in the db design both columns should have the same datatype).

Regards
Michel

Report message to a moderator

Re: P&T Guide Question [Restructuring SQL Statements] [message #458265 is a reply to message #458263] Fri, 28 May 2010 05:30 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Will do in future and I appreciate you clearing it up for me, that was how I understood it.

(Agreed!)

Report message to a moderator

Previous Topic: Need help with tuning a query
Next Topic: How to check the query performance
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 22 07:25:16 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.