RE: Just as a learning exercise
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <Rui.Amaral@xxxxxxxxxxxxxxxx>, <rjoralist2@xxxxxxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 4 May 2011 09:12:07 -0500
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
Other related posts:
