OraFAQ Forum: Performance Tuning » P&T Guide Question [Restructuring SQL Statements]

Home » RDBMS Server » Performance Tuning » P&T Guide Question [Restructuring SQL Statements] (10g Release 2 (10.2) - Documention Related)

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

P&T Guide Question [Restructuring SQL Statements] [message #458259]

Fri, 28 May 2010 04:51

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

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 02 09:43:37 CDT 2024