| Use of All with equal (=) [message #600879] |
Wed, 13 November 2013 08:37  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
I was thinking about use case, where I can use
select ... from ... where .. = all (..,..,..)
Do you have one in mind.
Thanks,
Manu
|
|
|
|
|
|
|
|
|
|
| Re: Use of All with equal (=) [message #600885 is a reply to message #600879] |
Wed, 13 November 2013 09:04  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
manubatham20 wrote on Wed, 13 November 2013 09:37Hi,
I was thinking about use case, where I can use
select ... from ... where .. = all (..,..,..)
Do you have one in mind.
Sure. Instead of:
select dname,
ename
from emp e,
dept d
where e.deptno = d.deptno
and d.deptno = 10
/
DNAME ENAME
-------------- ----------
ACCOUNTING CLARK
ACCOUNTING KING
ACCOUNTING MILLER
SCOTT@orcl >
You could use:
select dname,
ename
from emp e,
dept d
where 10 = all(d.deptno,e.deptno)
/
DNAME ENAME
-------------- ----------
ACCOUNTING CLARK
ACCOUNTING KING
ACCOUNTING MILLER
SCOTT@orcl >
SY.
|
|
|
|
|
|
| Re: Use of All with equal (=) [message #600891 is a reply to message #600890] |
Wed, 13 November 2013 10:14 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
SCOTT@orcl > explain plan for
2 select dname,
3 ename
4 from emp e,
5 dept d
6 where e.deptno = d.deptno
7 and d.deptno = 10
8 /
Explained.
SCOTT@orcl > select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 568005898
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 66 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 66 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 3 | 27 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=10)
4 - filter("E"."DEPTNO"=10)
17 rows selected.
SCOTT@orcl > explain plan for
2 select dname,
3 ename
4 from emp e,
5 dept d
6 where 10 = all(d.deptno,e.deptno)
7 /
Explained.
SCOTT@orcl > select * from table(dbms_xplan.display)
2 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 568005898
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 66 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3 | 66 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 3 | 27 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=10)
4 - filter("E"."DEPTNO"=10)
17 rows selected.
SCOTT@orcl >
But it doesn't mean plan will be same in all cases.
SY.
|
|
|
|
|
|
|
|
|
|
| Re: Use of All with equal (=) [message #600927 is a reply to message #600918] |
Thu, 14 November 2013 01:53 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
I am not commenting on any of the approach being correct or incorrect. It ultimately gives the same results, however, it is the way CBO evaluates the ALL operator.
will be evaluated as This is a simple comparison, with a subquery, it will be evaluated differently.
Let's say, I have an Emp and Dept table. I am not having any constraints. So, I expect a FTS on both the tables. Let's compare the execution plans :
SQL> EXPLAIN PLAN FOR
2 SELECT D.DEPT, E.ID
3 FROM EMP E, DEPT D
4 WHERE E.DEPT = D.DEPT
5 AND D.DEPT = 10;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1093152308
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 31 (4)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 78 | 31 (4)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 13 | 15 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 2 | 52 | 15 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPT"="D"."DEPT")
2 - filter("D"."DEPT"=10)
3 - filter("E"."DEPT"=10)
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
21 rows selected
SQL> EXPLAIN PLAN FOR
2 SELECT D.DEPT, E.ID
3 FROM EMP E, DEPT D
4 WHERE 10 = ALL(D.DEPT, E.DEPT);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3429684969
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 78 | 30 (0)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 2 | 78 | 30 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | DEPT | 1 | 13 | 15 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 2 | 52 | 15 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMP | 2 | 52 | 15 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("D"."DEPT"=10)
4 - filter("E"."DEPT"=10)
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
21 rows selected
I would rather go with the first approach where Dept table would be hashed and I can see access("E"."DEPT"="D"."DEPT"). I would not like to have a MERGE JOIN CARTESIAN.
|
|
|
|
| Re: Use of All with equal (=) [message #601008 is a reply to message #600927] |
Thu, 14 November 2013 08:45 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
As I said, you compare execution plan and choose one that fits most. But it wouldn't be wise not to use some feature at all if in some cases that feature results in sub-optimal plan.
SY.
|
|
|
|
| Re: Use of All with equal (=) [message #601017 is a reply to message #601008] |
Thu, 14 November 2013 10:42 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Solomon Yakobson wrote on Thu, 14 November 2013 14:45As I said, you compare execution plan and choose one that fits most. But it wouldn't be wise not to use some feature at all if in some cases that feature results in sub-optimal plan.
Agreed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
