Home » RDBMS Server » Performance Tuning » Scalar subquery merging (DB 19.x)
Scalar subquery merging [message #682040] Tue, 29 September 2020 09:40 Go to next message
John Watson
Messages: 8425
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 #682041 is a reply to message #682040] Tue, 29 September 2020 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With MAX the optimizer knows the result is one and only one row.
Without MAX it doesn't know if the correlated sub query returns 0, 1 or more rows and so how to handle this result.

You will say that it should know the result is at most one row from DEPT primary key SO we have to assume that the "rewrite" part of the code is currently not complete and does not handle many cases of select subqueries (in older version of "explain plan" these ones didn't even appear in the chart). Only Oracle can answer this point.

Re: Scalar subquery merging [message #682042 is a reply to message #682041] Tue, 29 September 2020 13:03 Go to previous messageGo to next message
John Watson
Messages: 8425
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
Michel Cadot
Messages: 67540
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...

Previous Topic: Different SQL ID for same SQL/HASH_VALUE
Next Topic: Bitmap join index
Goto Forum:
  


Current Time: Wed Nov 25 10:36:32 CST 2020