Re: SQL Tuning question

Dan wrote:
> I'm trying to put together some general 'best practices' for writing
> sql queries. I've been reading several books, docs, etc. One thing
> I've read very little about, but have heard about, is the order of
> filters in the 'where' clause and whether a field is on the right side
> or left side of the equal sign.
<snipped>

Dan, in my experience this very seldom makes a difference these days
with the Oracle CBO. In the past, this somestimes an issue - methinks
primarily because the join order was at times decided based on
predicate assignment direction. Driving the join from the larger result
set is always bad mojo. Recall resorting to the ORDERED hint numerous
times in the past (Oracle 7.1 days) to get the join order in the
correct sequence.

What is important in the predicate clause is using functions on the
right operand. E.g. not doing this:
WHERE TO_CHAR( date_colum, 'yyyy/mm/dd' ) = '2000/01/01'
but this:
WHERE date_colum = TO_DATE('2000/01/01', 'yyyy/mm/dd')

What makes this a stinger at times is that developers code it as:
WHERE date_colum = '2000/01/01'

...not realising that they are forcing an implicit TO_CHAR conversion on
the data column for the condition to be evaluated correctly.

Oh yeah - usual disclaimers apply as I'm not using the "new" join
syntax. I dislike it. And I have seen -numerous- occassions where this
so-called natural/logical join syntax cause developers to write the
absolutely worse performing SQL that I've ever seen. Because of getting
the predicate (with join clauses) wrong.

--
Billy

.

Relevant Pages

  • Re: show 0 values
    ... Thanks Dan for your help. ... I removed the where clause and add those conditions into join ... reference the service in ref table, ... incident#, service, desc, date ...
    (microsoft.public.sqlserver.programming)
  • Re: Permissions!
    ... Any number of thanks to you for the relentless help you have extended ... Dan! ... SQL queries as records in a DB table & then retrieving records from ...
    (microsoft.public.sqlserver.security)
  • Re: Changing Seek with a Null to Findfirst Method
    ... Dan ... >If you are still stuck after my last reply (parallel to ... rather than allenbrowne at mvps dot org. ... >> clause of a SQL ...
    (microsoft.public.access.formscoding)
  • Re: Cannot set CDX index on DBF file from C#
    ... SQL queries from C# against my large foxpro DBF/CDX table. ... I have a field called STOREDATE which is a Date field ... Expression in this second index is STORENAME + DTOS+ STOREVALUE. ... without the AND STOREDATE = clause also runs slow. ...
    (microsoft.public.fox.helpwanted)
  • Re: How do I run a SQL query and order the result set by a calcula
    ... This does NOT work - you skipped the GROUP BY clause! ... > One more way, besides what Dan said, is to use numbers. ... > Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP ... >> If I want to run a SQL query and order the result set by a calculated ...
    (microsoft.public.fox.helpwanted)