Scalar subquery merging [message #682040] |
Tue, 29 September 2020 09:40 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Take query like this, that projects a subquery:orclz>
orclz> set autot trace exp
orclz> select ename,(select dname from dept where dept.deptno=emp.deptno) from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 2981343222
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 126 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"=:B1)
orclz> it works, but the correlation is bad code that could be re-written as an outer join:orclz> select ename,dname from emp left outer join dept using(deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 3387915970
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 308 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO"(+))
So it looks as though Oracle cannot merge a correlated subquery like that. Except it can with a small change:orclz> select ename,(select max(dname) from dept where dept.deptno=emp.deptno) from emp;
Execution Plan
----------------------------------------------------------
Plan hash value: 3387915970
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 308 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 308 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPT"."DEPTNO"(+)="EMP"."DEPTNO") I cannot understand why, if the CBO can merge the version with the aggregation, it cannot merge the first version without. Hinting it doesn't work.
Anyone have any insight on this?
|
|
|
|
Re: Scalar subquery merging [message #682042 is a reply to message #682041] |
Tue, 29 September 2020 13:03 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Ah, right. Perhaps they have not got around to doing it yet. I was looking at the other end: not "what if more than one row comes back?" but "what if no rows come back?" so I tried making the join column not nullable. Didn't help, even with the hint it won't merge.
|
|
|
Re: Scalar subquery merging [message #682043 is a reply to message #682042] |
Tue, 29 September 2020 13:43 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I think the "more than one row" case is the reason this has not currently been addressed as in this case the query should return an error (and not with the outer join) but, as I said, Oracle should know this case can't happen here given the constraints.
Maybe in a future version...
|
|
|