Re: Just as a learning exercise

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Wed, 4 May 2011 10:37:55 -0400
Message-ID: <BANLkTi=zzCrG+G2eN2+gggG23rSNqf4beg_at_mail.gmail.com>

Isn't the 1=1 there because someone doesn't want to use the proper ANSI syntax?

I would think

SELECT P.last_name
     , P.first_name
     , s.title
     , T.created AS completed
  FROM persons P
       JOIN required_courses s
          ON 1 = 1
       ...

is better written as

SELECT P.last_name
     , P.first_name
     , s.title
     , T.created AS completed
  FROM persons P
       CROSS JOIN required_courses s

On Wed, May 4, 2011 at 10:12 AM, Ric Van Dyke <ric.van.dyke_at_hotsos.com>wrote:

> I'm pretty sure that 1=1 has never improved performance of a query; it's
> like adding "TURE" to a statement. Certainly since about 9 or so the
> optimizer removes it as a predicate, notice the 1=1 predicate isn't applied
> at all in the plan below (yes I truncated the plan so as to fit better):
>
> SQL> set autotrace traceonly explain
> SQL> select * from emp where 1=1 and deptno = 10;
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 919097248
>
> ------------------------------------------------------------
> | Id | Operation | Name | Rows |
> ------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 4 |
> | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 4 |
> |* 2 | INDEX RANGE SCAN | EMP_DEPT_IDX | 4 |
> ------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - access("DEPTNO"=10)
>
>
> As to the query with an outer join in Oracle syntax:
>
> Think of the + as a grave marker, and think that you need to put the grave
> marker on the side of the query where you need the ghost records from. So
> which table will there not be a match on that you want records from? That
> is the side you put the + on.
>
> I hope that helps.
>
> -----------------------
> Ric Van Dyke
> Hotsos Enterprises
> Cell 248-705-0624
> -----------------------
>
> The 10th Hotsos Symposium
> 4-8 March 2012 Start making plans now!
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Amaral, Rui
> Sent: Wednesday, May 04, 2011 9:50 AM
> To: 'rjoralist2_at_society.servebeer.com'; oracle-l_at_freelists.org
> Subject: RE: Just as a learning exercise
>
> "1=1" - I seem to remember the same thing (tho not hop-soaked) and
> eventually got phased out as being not helpful at all.
>
>
> Rui Amaral
> Database Administrator
> ITS - SSG
> TD Bank Financial Group
> 220 Bay St., 11th Floor
> Toronto, ON, CA, M5K1A2
> (bb) (647) 204-9106
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Rich Jesse
> Sent: Wednesday, May 04, 2011 9:44 AM
> To: oracle-l_at_freelists.org
> Subject: Re: Just as a learning exercise
>
> Joe writes:
>
> > to truncate the time component?
>
> ...which not only unnecessary in the comparison, but a performance drain.
> Also, although it defaults to midnight, I'd add the time component in the
> TO_DATE calls.
>
> As far as the "1=1", when I started my current job, consultants used that
> in every statement. A distant hop-soaked memory thinks it was a hack to
> attempt to remind the optimizer that the first table specified in the FROM
> was the driving table (or something like that?) in pre-9i days.
>
> > From:
> > Paul Drake <bdbafh_at_gmail.com>
>
> ...
>
> > Why would anyone ever apply a TRUNC ( ) function to a date column in a
> > where clause?
>
> Rich
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> NOTICE: Confidential message which may be privileged. Unauthorized
> use/disclosure prohibited. If received in error, please go to
> www.td.com/legal for instructions.
> AVIS : Message confidentiel dont le contenu peut être privilégié.
> Utilisation/divulgation interdites sans permission. Si reçu par erreur,
> prière d'aller au www.td.com/francais/avis_juridique pour des
> instructions.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Rumpi Gravenstein

--
http://www.freelists.org/webpage/oracle-l

Received on Wed May 04 2011 - 09:37:55 CDT