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@xxxxxxxxxx>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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of Amaral, Rui > Sent: Wednesday, May 04, 2011 9:50 AM > To: 'rjoralist2@xxxxxxxxxxxxxxxxxxxxx'; oracle-l@xxxxxxxxxxxxx > 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] > On Behalf Of Rich Jesse > Sent: Wednesday, May 04, 2011 9:44 AM > To: oracle-l@xxxxxxxxxxxxx > 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@xxxxxxxxx> > > ... > > > Why would anyone ever apply a TRUNC ( ) function to a date column in a > > where clause? > > Rich > > -- > //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. > -- > //www.freelists.org/webpage/oracle-l > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Rumpi Gravenstein