OraFAQ Forum: Performance Tuning » Scalar subquery merging

Home » RDBMS Server » Performance Tuning » Scalar subquery merging (DB 19.x)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Scalar subquery merging [message #682040]

Tue, 29 September 2020 09:40

John Watson
Messages: 8964
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?

Report message to a moderator

Re: Scalar subquery merging [message #682041 is a reply to message #682040]

Tue, 29 September 2020 10:31

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: Scalar subquery merging [message #682042 is a reply to message #682041]

Tue, 29 September 2020 13:03

John Watson
Messages: 8964
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.

Report message to a moderator

Re: Scalar subquery merging [message #682043 is a reply to message #682042]

Tue, 29 September 2020 13:43

Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...

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...

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 03:49:46 CST 2025