RE: Just as a learning exercise

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Wed, 4 May 2011 09:12:07 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF01AA301B_at_WIN02.hotsos.com>

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

Received on Wed May 04 2011 - 09:12:07 CDT