| + 0 in the where clause [message #645653] |
Thu, 10 December 2015 08:23  |
 |
msinha8
Messages: 43
Registered: March 2014
Location: Hyderbad
|
Member |
|
|
Hi All,
Want to understand the significance of + 0 in the where clause
for say :-
select t1.c1,
t1.c2,
t2.c1,
t2.c2
from table1 t1,
table2 t2
where t1.c1= t2.c1 + 0;
Regards
|
|
|
|
|
|
| Re: + 0 in the where clause [message #645656 is a reply to message #645655] |
Thu, 10 December 2015 08:41  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
As it is now, the +0 makes no sense.
Two possibilities I can think of why it is there, depending on what data type t2.c1 is.
1) If t2.c1 is a character type, then it forces an implicit conversion of t2.c1 to number. In a way that is sometimes used in Perl.
So it could be the thing was written by a Perl hacker that doesn't really know about SQL and functions like to_number or cast.
2) The other possibility, if t2.c1 is for example numeric or a date, could be that there used to be something other than 0 sometime in the past, and the join was something like "get everything from yesterday an join it to everything from today" or some such, which then changed, and instead of completely deleting the +whaterver the person decided to change it to +0 as to leave a hint to where to adjust that "join offset".
|
|
|
|
|
|
|
|
|
|
| Re: + 0 in the where clause [message #645671 is a reply to message #645660] |
Thu, 10 December 2015 12:48 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Adding the +0 would be the same as using col1||null for a varchar2 field. If forces the optimizer to not use a specific index on that column (unless you have a function index) and was heavily used in the Rule Based Optimizer. As long as your statistics are up to date there is no need to do that under the Cost Based Optimizer used in modern versions of Oracle. Also you could use an optimizer hint to force it to use a different index. Both methods were used.
[Updated on: Thu, 10 December 2015 12:49] Report message to a moderator |
|
|
|
| Re: + 0 in the where clause [message #645685 is a reply to message #645653] |
Fri, 11 December 2015 00:28 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
While it would work for numeric type, however the performance might degrade.
For example, comparing the explain plans:
SQL> SET autot ON EXPLAIN
SQL> SELECT A.empno,
2 b.deptno
3 FROM scott.emp A,
4 scott.dept b
5 WHERE A.deptno = b.deptno +0;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 71037407
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 140 | 4 (0)| 00:00:01
|* 1 | HASH JOIN | | 14 | 140 | 4 (0)| 00:00:01
| 2 | INDEX FULL SCAN | PK_DEPT | 4 | 12 | 1 (0)| 00:00:01
| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO"+0)
SQL> SELECT A.empno,
2 b.deptno
3 FROM scott.emp A,
4 scott.dept b
5 WHERE A.deptno = b.deptno;
EMPNO DEPTNO
---------- ----------
7369 20
7499 30
7521 30
7566 20
7654 30
7698 30
7782 10
7788 20
7839 10
7844 30
7876 20
7900 30
7902 20
7934 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 98 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."DEPTNO" IS NOT NULL)
In the former case, it is forcing the optimizer to do an additional index full scan which you can see as access("A"."DEPTNO"="B"."DEPTNO"+0). It is an overhead, while all we want is to get all the rows therefore one FTS on EMP is enough as we have a foreign key constraint defined.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
