{"id":2402,"date":"2019-02-26T12:56:23","date_gmt":"2019-02-26T12:56:23","guid":{"rendered":"https:\/\/nenadnoveljic.com\/blog\/?p=2402"},"modified":"2019-02-26T13:20:51","modified_gmt":"2019-02-26T13:20:51","slug":"correlated-subqueries-in-the-select-clause-2","status":"publish","type":"post","link":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/","title":{"rendered":"Correlated Subqueries in the SELECT Clause (2)"},"content":{"rendered":"

In the previous installment<\/a>, I described a case where Oracle optimizer doesn’t unnest the subquery in the SELECT clause, even when that would be a better option. Christian Antognini pointed out<\/a> that this happens because the scalar subquery didn’t return a single value, which disqualified the subquery from the subquery unnesting transformation. Nevertheless, SQL Server managed to rewrite the same query to use a more efficient semi join instead, which resulted in orders of magnitude less logical reads.<\/p>\n

That prompted me to test more such queries and explore the execution plans in both database products. This information can be used for manual optimization, as it’s often possible to rewrite a subquery in the SELECT clause as either a semi join or an outer join.<\/p>\n

I tested on Oracle 18c and SQL Server 2017.<\/p>\n

The non-indexed test tables t_1k and t_100k contain distinct integer values and are defined in my previous blog post<\/a>.<\/p>\n

Query 1 – EXISTS<\/span><\/h1>\n
select \n  case when exists<\/span> ( \n    select  1 \n\t  from t_1k \n\t  where t_1k.n1 = t_100k.n1 \n  ) then 1 else 0 end\n  from t_100k ; <\/code><\/pre>\n
SQL Server:<\/p>\n
StmtText\tStmtId\tNodeId\tParent\tPhysicalOp\tLogicalOp\tArgument\tDefinedValues\tEstimateRows\tEstimateIO\tEstimateCPU\tAvgRowSize\tTotalSubtreeCost\tOutputList\tWarnings\tType\tParallel\tEstimateExecutions\nselect     case when exists (       select  1      from t_1k      where t_1k.n1 = t_100k.n1     ) then 1 else 0 end    from t_100k ;\t1\t1\t0\tNULL\tNULL\t1\tNULL\t100000\tNULL\tNULL\tNULL\t7.925597\tNULL\tNULL\tSELECT\t0\tNULL\n  |--Compute Scalar(DEFINE:([Expr1007]=CASE WHEN [Expr1008] THEN (1) ELSE (0) END))\t1\t2\t1\tCompute Scalar\tCompute Scalar\tDEFINE:([Expr1007]=CASE WHEN [Expr1008] THEN (1) ELSE (0) END)\t[Expr1007]=CASE WHEN [Expr1008] THEN (1) ELSE (0) END\t100000\t0\t0.01\t11\t7.925597\t[Expr1007]\tNULL\tPLAN_ROW\t0\t1\n       |--Merge Join(Left Semi Join<\/span>, MANY-TO-MANY MERGE:([BVTOOLS].[dbo].[t_100k].[n1])=([BVTOOLS].[dbo].[t_1k].[n1]), RESIDUAL:([BVTOOLS].[dbo].[t_1k].[n1]=[BVTOOLS].[dbo].[t_100k].[n1]))\t1\t3\t2\tMerge Join\tLeft Semi Join\tMANY-TO-MANY MERGE:([BVTOOLS].[dbo].[t_100k].[n1])=([BVTOOLS].[dbo].[t_1k].[n1]), RESIDUAL:([BVTOOLS].[dbo].[t_1k].[n1]=[BVTOOLS].[dbo].[t_100k].[n1])\tNULL\t100000\t0.000313\t0.0336502\t9\t7.915597\t[Expr1008]\tNULL\tPLAN_ROW\t0\t1\n            |--Sort(ORDER BY:([BVTOOLS].[dbo].[t_100k].[n1] ASC))\t1\t4\t3\tSort\tSort\tORDER BY:([BVTOOLS].[dbo].[t_100k].[n1] ASC)\tNULL\t100000\t0.01126126\t7.623925\t11\t7.866987\t[BVTOOLS].[dbo].[t_100k].[n1]\tNULL\tPLAN_ROW\t0\t1\n            |    |--Table Scan(OBJECT:([BVTOOLS].[dbo].[t_100k]))\t1\t5\t4\tTable Scan\tTable Scan\tOBJECT:([BVTOOLS].[dbo].[t_100k])\t[BVTOOLS].[dbo].[t_100k].[n1]\t100000\t0.1216435\t0.110157\t11\t0.2318005\t[BVTOOLS].[dbo].[t_100k].[n1]\tNULL\tPLAN_ROW\t0\t1\n            |--Sort(ORDER BY:([BVTOOLS].[dbo].[t_1k].[n1] ASC))\t1\t6\t3\tSort\tSort\tORDER BY:([BVTOOLS].[dbo].[t_1k].[n1] ASC)\tNULL\t1\t0.01126126\t0.000100011\t11\t0.01464437\t[BVTOOLS].[dbo].[t_1k].[n1]\tNULL\tPLAN_ROW\t0\t1\n                 |--Table Scan(OBJECT:([BVTOOLS].[dbo].[t_1k]))\t1\t7\t6\tTable Scan\tTable Scan\tOBJECT:([BVTOOLS].[dbo].[t_1k])\t[BVTOOLS].[dbo].[t_1k].[n1]\t1\t0.003125\t0.0001581\t11\t0.0032831\t[BVTOOLS].[dbo].[t_1k].[n1]\tNULL\tPLAN_ROW\t0\t1<\/code><\/pre>\n
(100000 rows affected)\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 't_1k'. Scan count 1, logical reads 2<\/span>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 't_100k'. Scan count 1, logical reads 161<\/span>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/code><\/pre>\n
Oracle:<\/p>\n
----------------------------------------------------------------------------\n| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |        |   100K|   488K|   356K  (1)| 00:00:56 |\n|*  1 |  TABLE ACCESS FULL| T_1K   |     1 |     4 |     4   (0)| 00:00:01 |\n|   2 |  TABLE ACCESS FULL| T_100K |   100K|   488K|    33   (0)| 00:00:01 |\n----------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   1 - filter(\"T_1K\".\"N1\"=:B1)<\/span><\/code><\/pre>\n
consistent gets\t499163<\/span><\/code><\/pre>\n
Conclusion: Unlike Oracle, SQL Server rewrote the query to merge semi join and produced a more efficient execution plan.<\/p>\n
Query 2 – MAX<\/span><\/h1>\n
The query below is a part of the demo script subquery_unnesting.sql<\/a> from Christian Antognini’s book Troubleshooting Oracle Performance, 2nd Edition<\/a>:<\/p>\n
select \n  t_100k.*,\n  (select max<\/span>(t_1k.n1) from t_1k where t_1k.n1 = t_100k.n1) as max_id\nfrom t_100k;<\/code><\/pre>\n
SQL Server:<\/p>\n
StmtText\tStmtId\tNodeId\tParent\tPhysicalOp\tLogicalOp\tArgument\tDefinedValues\tEstimateRows\tEstimateIO\tEstimateCPU\tAvgRowSize\tTotalSubtreeCost\tOutputList\tWarnings\tType\tParallel\tEstimateExecutions\nselect     t_100k.*,    (select max(t_1k.n1) from t_1k where t_1k.n1 = t_100k.n1) as max_id  from t_100k;\t1\t1\t0\tNULL\tNULL\t1\tNULL\t100000\tNULL\tNULL\tNULL\t1.072298\tNULL\tNULL\tSELECT\t0\tNULL\n  |--Compute Scalar(DEFINE:([Expr1008]=[Expr1006]))\t1\t2\t1\tCompute Scalar\tCompute Scalar\tDEFINE:([Expr1008]=[Expr1006])\t[Expr1008]=[Expr1006]\t100000\t0\t0.01\t15\t1.072298\t[BVTOOLS].[dbo].[t_100k].[n1], [Expr1008]\tNULL\tPLAN_ROW\t0\t1\n       |--Hash Match(Right Outer Join<\/span>, HASH:([BVTOOLS].[dbo].[t_1k].[n1])=([BVTOOLS].[dbo].[t_100k].[n1]), RESIDUAL:([BVTOOLS].[dbo].[t_1k].[n1]=[BVTOOLS].[dbo].[t_100k].[n1]))\t1\t3\t2\tHash Match\tRight Outer Join\tHASH:([BVTOOLS].[dbo].[t_1k].[n1])=([BVTOOLS].[dbo].[t_100k].[n1]), RESIDUAL:([BVTOOLS].[dbo].[t_1k].[n1]=[BVTOOLS].[dbo].[t_100k].[n1])\tNULL\t100000\t0\t0.7973643\t15\t1.062298\t[BVTOOLS].[dbo].[t_100k].[n1], [Expr1006]\tNULL\tPLAN_ROW\t0\t1\n            |--Stream Aggregate(GROUP BY:([BVTOOLS].[dbo].[t_1k].[n1]) DEFINE:([Expr1006]=MAX([BVTOOLS].[dbo].[t_1k].[n1])))\t1\t4\t3\tStream Aggregate\tAggregate\tGROUP BY:([BVTOOLS].[dbo].[t_1k].[n1])\t[Expr1006]=MAX([BVTOOLS].[dbo].[t_1k].[n1])\t1000\t0\t0.0011\t15\t0.03313064\t[BVTOOLS].[dbo].[t_1k].[n1], [Expr1006]\tNULL\tPLAN_ROW\t0\t1\n            |    |--Sort(ORDER BY:([BVTOOLS].[dbo].[t_1k].[n1] ASC))\t1\t5\t4\tSort\tSort\tORDER BY:([BVTOOLS].[dbo].[t_1k].[n1] ASC)\tNULL\t1000\t0.01126126\t0.01564663\t11\t0.03203064\t[BVTOOLS].[dbo].[t_1k].[n1]\tNULL\tPLAN_ROW\t0\t1\n            |         |--Table Scan(OBJECT:([BVTOOLS].[dbo].[t_1k]))\t1\t6\t5\tTable Scan\tTable Scan\tOBJECT:([BVTOOLS].[dbo].[t_1k])\t[BVTOOLS].[dbo].[t_1k].[n1]\t1000\t0.003865741\t0.001257\t11\t0.005122741\t[BVTOOLS].[dbo].[t_1k].[n1]\tNULL\tPLAN_ROW\t0\t1\n            |--Table Scan(OBJECT:([BVTOOLS].[dbo].[t_100k]))\t1\t7\t3\tTable Scan\tTable Scan\tOBJECT:([BVTOOLS].[dbo].[t_100k])\t[BVTOOLS].[dbo].[t_100k].[n1]\t100000\t0.1216435\t0.110157\t11\t0.2318005\t[BVTOOLS].[dbo].[t_100k].[n1]\tNULL\tPLAN_ROW\t0\t1<\/code><\/pre>\n
(100000 rows affected)\nTable 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 't_100k'. Scan count 1, logical reads 161<\/span>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 't_1k'. Scan count 1, logical reads 2<\/span>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/code><\/pre>\n
Oracle:<\/p>\n
----------------------------------------------------------------------------------\n| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT      |          |   100K|  3027K|    39   (6)| 00:00:01 |\n|*  1 |  HASH JOIN RIGHT OUTER<\/span>|          |   100K|  3027K|    39   (6)| 00:00:01 |\n|   2 |   VIEW                | VW_SSQ_1 |  1000 | 26000 |     5  (20)| 00:00:01 |\n|   3 |    HASH GROUP BY      |          |  1000 |  4000 |     5  (20)| 00:00:01 |\n|   4 |     TABLE ACCESS FULL | T_1K     |  1000 |  4000 |     4   (0)| 00:00:01 |\n|   5 |   TABLE ACCESS FULL   | T_100K   |   100K|   488K|    33   (0)| 00:00:01 |\n----------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   1 - access(\"ITEM_1\"(+)=\"T_100K\".\"N1\")<\/code><\/pre>\n
consistent gets\t168<\/span><\/code><\/pre>\n
Conclusion: Both database engines produced a good execution plan. Because of the MAX<\/span> function, the optimizers recognized that the subquery returns a single value and then unnested the query.<\/p>\n
Query 3 – DISTINCT<\/span><\/h1>\n
In the next query, I replaced MAX with DISTINCT<\/span> which should also guarantee the single value.<\/p>\n
select \n  t_100k.*,\n  (select distinct<\/span> t_1k.n1 from t_1k where t_1k.n1 = t_100k.n1) as s_n1\nfrom t_100k;<\/code><\/pre>\n
SQL Server:<\/p>\n
StmtText\tStmtId\tNodeId\tParent\tPhysicalOp\tLogicalOp\tArgument\tDefinedValues\tEstimateRows\tEstimateIO\tEstimateCPU\tAvgRowSize\tTotalSubtreeCost\tOutputList\tWarnings\tType\tParallel\tEstimateExecutions\nselect     t_100k.*,    (select distinct t_1k.n1 from t_1k where t_1k.n1 = t_100k.n1) as s_n1  from t_100k;\t1\t1\t0\tNULL\tNULL\t1\tNULL\t100000\tNULL\tNULL\tNULL\t1.071298\tNULL\tNULL\tSELECT\t0\tNULL\n  |--Compute Scalar(DEFINE:([Expr1007]=[BVTOOLS].[dbo].[t_1k].[n1]))\t1\t2\t1\tCompute Scalar\tCompute Scalar\tDEFINE:([Expr1007]=[BVTOOLS].[dbo].[t_1k].[n1])\t[Expr1007]=[BVTOOLS].[dbo].[t_1k].[n1]\t100000\t0\t0.01\t15\t1.071298\t[BVTOOLS].[dbo].[t_100k].[n1], [Expr1007]\tNULL\tPLAN_ROW\t0\t1\n       |--Hash Match(Right Outer Join<\/span>, HASH:([BVTOOLS].[dbo].[t_1k].[n1])=([BVTOOLS].[dbo].[t_100k].[n1]), RESIDUAL:([BVTOOLS].[dbo].[t_1k].[n1]=[BVTOOLS].[dbo].[t_100k].[n1]))\t1\t3\t2\tHash Match\tRight Outer Join\tHASH:([BVTOOLS].[dbo].[t_1k].[n1])=([BVTOOLS].[dbo].[t_100k].[n1]), RESIDUAL:([BVTOOLS].[dbo].[t_1k].[n1]=[BVTOOLS].[dbo].[t_100k].[n1])\tNULL\t100000\t0\t0.7973643\t15\t1.061298\t[BVTOOLS].[dbo].[t_100k].[n1], [BVTOOLS].[dbo].[t_1k].[n1]\tNULL\tPLAN_ROW\t0\t1\n            |--Sort(DISTINCT ORDER BY:([BVTOOLS].[dbo].[t_1k].[n1] ASC))\t1\t5\t3\tSort\tDistinct Sort\tDISTINCT ORDER BY:([BVTOOLS].[dbo].[t_1k].[n1] ASC)\tNULL\t1000\t0.01126126\t0.01564664\t15\t0.03213064\t[BVTOOLS].[dbo].[t_1k].[n1]\tNULL\tPLAN_ROW\t0\t1\n            |    |--Compute Scalar(DEFINE:([Expr1006]=[BVTOOLS].[dbo].[t_1k].[n1]))\t1\t6\t5\tCompute Scalar\tCompute Scalar\tDEFINE:([Expr1006]=[BVTOOLS].[dbo].[t_1k].[n1])\t[Expr1006]=[BVTOOLS].[dbo].[t_1k].[n1]\t1000\t0\t0.0001\t15\t0.005222741\t[BVTOOLS].[dbo].[t_1k].[n1], [Expr1006]\tNULL\tPLAN_ROW\t0\t1\n            |         |--Table Scan(OBJECT:([BVTOOLS].[dbo].[t_1k]))\t1\t7\t6\tTable Scan\tTable Scan\tOBJECT:([BVTOOLS].[dbo].[t_1k])\t[BVTOOLS].[dbo].[t_1k].[n1]\t1000\t0.003865741\t0.001257\t11\t0.005122741\t[BVTOOLS].[dbo].[t_1k].[n1]\tNULL\tPLAN_ROW\t0\t1\n            |--Table Scan(OBJECT:([BVTOOLS].[dbo].[t_100k]))\t1\t8\t3\tTable Scan\tTable Scan\tOBJECT:([BVTOOLS].[dbo].[t_100k])\t[BVTOOLS].[dbo].[t_100k].[n1]\t100000\t0.1216435\t0.110157\t11\t0.2318005\t[BVTOOLS].[dbo].[t_100k].[n1]\tNULL\tPLAN_ROW\t0\t1<\/code><\/pre>\n
(100000 rows affected)\nTable 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 't_100k'. Scan count 1, logical reads 161<\/span>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 't_1k'. Scan count 1, logical reads 2<\/span>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/code><\/pre>\n
Oracle:<\/p>\n
-----------------------------------------------------------------------------\n| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |\n-----------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |        |   100K|   488K|   464K (21)| 00:01:13 |\n|   1 |  SORT UNIQUE NOSORT|        |     1 |     4 |     5  (20)| 00:00:01 |\n|*  2 |   TABLE ACCESS FULL| T_1K   |     1 |     4 |     4   (0)| 00:00:01 |\n|   3 |  TABLE ACCESS FULL | T_100K |   100K|   488K|    33   (0)| 00:00:01 |\n-----------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   2 - filter(\"T_1K\".\"N1\"=:B1)<\/span><\/code><\/pre>\n
consistent gets\t500163<\/span><\/code><\/pre>\n
Conclusion: Although DISTINCT<\/span> guarantees the uniqueness and, thus, the scalar subquery returns a single result per driving row, Oracle optimizer didn’t unnest the subquery, which resulted in a suboptimal execution plan.<\/p>\n
Query 4 – primary key<\/span><\/h1>\n
In this example I’ll be using a large table with a primary key:<\/p>\n
SQL Server:<\/p>\n
create table t_1m_pk (\n  n1 integer, c1 nvarchar(100), \n  constraint pk_t_1m primary key<\/span> nonclustered (n1) \n) ;\n\ninsert into t_1m_pk\nSELECT TOP (1000000) \n  n1 = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id])),\n  'A'\nFROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2\nOPTION (MAXDOP 1);<\/code><\/pre>\n
Oracle:<\/p>\n
create table t_1m_pk ( \n  n1 integer, c1 varchar2(1), \n  constraint pk_1m_pk primary key<\/span> (n1) \n) ;\n\ninsert into t_1m_pk\n  select level , 'A'\n    from dual\n    connect by level <= 1000000;\n    \ncommit ;\n\nbegin\n  dbms_stats.gather_table_stats ( null, 'T_1M_PK') ;\nend ;\n\/<\/code><\/pre>\n
Query:<\/p>\n
select \n  t_100k.*,\n  (select t_1m_pk.c1 from t_1m_pk where t_1m_pk.n1 = t_100k.n1) as s_n1\nfrom t_100k;<\/code><\/pre>\n
SQL Server:<\/p>\n
StmtText\tStmtId\tNodeId\tParent\tPhysicalOp\tLogicalOp\tArgument\tDefinedValues\tEstimateRows\tEstimateIO\tEstimateCPU\tAvgRowSize\tTotalSubtreeCost\tOutputList\tWarnings\tType\tParallel\tEstimateExecutions\nselect     t_100k.*,    (select t_1m_pk.c1 from t_1m_pk where t_1m_pk.n1 = t_100k.n1) as s_n1  from t_100k;\t1\t1\t0\tNULL\tNULL\t1\tNULL\t100000\tNULL\tNULL\tNULL\t11.14075\tNULL\tNULL\tSELECT\t0\tNULL\n  |--Compute Scalar(DEFINE:([Expr1007]=[BVTOOLS].[dbo].[t_1m_pk].[c1]))\t1\t2\t1\tCompute Scalar\tCompute Scalar\tDEFINE:([Expr1007]=[BVTOOLS].[dbo].[t_1m_pk].[c1])\t[Expr1007]=[BVTOOLS].[dbo].[t_1m_pk].[c1]\t100000\t0\t0.01\t16\t11.14075\t[BVTOOLS].[dbo].[t_100k].[n1], [Expr1007]\tNULL\tPLAN_ROW\t0\t1\n       |--Hash Match(Left Outer Join<\/span>, HASH:([BVTOOLS].[dbo].[t_100k].[n1])=([BVTOOLS].[dbo].[t_1m_pk].[n1]), RESIDUAL:([BVTOOLS].[dbo].[t_1m_pk].[n1]=[BVTOOLS].[dbo].[t_100k].[n1]))\t1\t3\t2\tHash Match\tLeft Outer Join\tHASH:([BVTOOLS].[dbo].[t_100k].[n1])=([BVTOOLS].[dbo].[t_1m_pk].[n1]), RESIDUAL:([BVTOOLS].[dbo].[t_1m_pk].[n1]=[BVTOOLS].[dbo].[t_100k].[n1])\tNULL\t100000\t0\t8.057143\t16\t11.13075\t[BVTOOLS].[dbo].[t_100k].[n1], [BVTOOLS].[dbo].[t_1m_pk].[c1]\tNULL\tPLAN_ROW\t0\t1\n            |--Table Scan(OBJECT:([BVTOOLS].[dbo].[t_100k]))\t1\t4\t3\tTable Scan\tTable Scan\tOBJECT:([BVTOOLS].[dbo].[t_100k])\t[BVTOOLS].[dbo].[t_100k].[n1]\t100000\t0.1216435\t0.110157\t11\t0.2318005\t[BVTOOLS].[dbo].[t_100k].[n1]\tNULL\tPLAN_ROW\t0\t1\n            |--Table Scan(OBJECT:([BVTOOLS].[dbo].[t_1m_pk]))\t1\t5\t3\tTable Scan\tTable Scan\tOBJECT:([BVTOOLS].[dbo].[t_1m_pk])\t[BVTOOLS].[dbo].[t_1m_pk].[n1], [BVTOOLS].[dbo].[t_1m_pk].[c1]\t1000000\t1.741644\t1.100157\t16\t2.8418\t[BVTOOLS].[dbo].[t_1m_pk].[n1], [BVTOOLS].[dbo].[t_1m_pk].[c1]\tNULL\tPLAN_ROW\t0\t1<\/code><\/pre>\n
(100000 rows affected)\nTable 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 't_1m_pk'. Scan count 1, logical reads 2348<\/span>, physical reads 0, read-ahead reads 845, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.\nTable 't_100k'. Scan count 1, logical reads 161<\/span>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/code><\/pre>\n
Oracle:<\/p>\n
----------------------------------------------------------------------------------------\n| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |\n----------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT            |          |   100K|   488K|   181K  (1)| 00:00:29 |\n|   1 |  TABLE ACCESS BY INDEX ROWID| T_1M_PK  |     1 |     7 |     2   (0)| 00:00:01 |\n|*  2 |   INDEX UNIQUE SCAN         | PK_1M_PK |     1 |       |     1   (0)| 00:00:01 |\n|   3 |  TABLE ACCESS FULL          | T_100K   |   100K|   488K|    33   (0)| 00:00:01 |\n----------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   2 - access(\"T_1M_PK\".\"N1\"=:B1)<\/span><\/code><\/pre>\n
consistent gets\t100731<\/span><\/code><\/pre>\n
Conclusion: Although the primary key<\/span> guarantees a single value in the subquery, Oracle didn’t unnest it, which in turn resulted in a suboptimal plan with much more consistent gets.<\/p>\n
Summary<\/h1>\n
In summary, Oracle optimizer sometimes doesn’t unnest the subqueries in the SELECT clause. Therefore, in such cases, it might make sense to check the execution statistics and consider rewriting the query by using a semi or an outer join.<\/p>\n","protected":false},"excerpt":{"rendered":"
Examples where Oracle optimizer doesn’t unnest subqueries in the SELECT clause Continue Reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[21,11,5,17],"tags":[],"class_list":["post-2402","post","type-post","status-publish","format-standard","hentry","category-comparison-oracle-sql-server","category-cost-based-optimizer","category-oracle","category-sql-server"],"yoast_head":"\nCorrelated Subqueries in the SELECT Clause (2) - All-round Database Topics<\/title>\n<meta name=\"description\" content=\"Examples where Oracle optimizer doesn't unnest subqueries in the SELECT clause\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Correlated Subqueries in the SELECT Clause (2) - All-round Database Topics\" \/>\n<meta property=\"og:description\" content=\"Examples where Oracle optimizer doesn't unnest subqueries in the SELECT clause\" \/>\n<meta property=\"og:url\" content=\"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/\" \/>\n<meta property=\"og:site_name\" content=\"All-round Database Topics\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-26T12:56:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-02-26T13:20:51+00:00\" \/>\n<meta name=\"author\" content=\"Nenad Noveljic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@NenadNoveljic\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nenad Noveljic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/correlated-subqueries-in-the-select-clause-2\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/correlated-subqueries-in-the-select-clause-2\\\/\"},\"author\":{\"name\":\"Nenad Noveljic\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"headline\":\"Correlated Subqueries in the SELECT Clause (2)\",\"datePublished\":\"2019-02-26T12:56:23+00:00\",\"dateModified\":\"2019-02-26T13:20:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/correlated-subqueries-in-the-select-clause-2\\\/\"},\"wordCount\":395,\"commentCount\":1,\"articleSection\":[\"Comparison Oracle-SQL Server\",\"cost based optimizer\",\"Oracle\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/correlated-subqueries-in-the-select-clause-2\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/correlated-subqueries-in-the-select-clause-2\\\/\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/correlated-subqueries-in-the-select-clause-2\\\/\",\"name\":\"Correlated Subqueries in the SELECT Clause (2) - All-round Database Topics\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\"},\"datePublished\":\"2019-02-26T12:56:23+00:00\",\"dateModified\":\"2019-02-26T13:20:51+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\"},\"description\":\"Examples where Oracle optimizer doesn't unnest subqueries in the SELECT clause\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/correlated-subqueries-in-the-select-clause-2\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/correlated-subqueries-in-the-select-clause-2\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/correlated-subqueries-in-the-select-clause-2\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Correlated Subqueries in the SELECT Clause (2)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/\",\"name\":\"All-round Database Topics\",\"description\":\"Nenad Noveljic\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/#\\\/schema\\\/person\\\/51458d9dd86dbbdd19f5add451d44efa\",\"name\":\"Nenad Noveljic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g\",\"caption\":\"Nenad Noveljic\"},\"sameAs\":[\"nenad-noveljic-9b746a6\",\"https:\\\/\\\/x.com\\\/NenadNoveljic\"],\"url\":\"https:\\\/\\\/nenadnoveljic.com\\\/blog\\\/author\\\/nenad\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Correlated Subqueries in the SELECT Clause (2) - All-round Database Topics","description":"Examples where Oracle optimizer doesn't unnest subqueries in the SELECT clause","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/","og_locale":"en_US","og_type":"article","og_title":"Correlated Subqueries in the SELECT Clause (2) - All-round Database Topics","og_description":"Examples where Oracle optimizer doesn't unnest subqueries in the SELECT clause","og_url":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/","og_site_name":"All-round Database Topics","article_published_time":"2019-02-26T12:56:23+00:00","article_modified_time":"2019-02-26T13:20:51+00:00","author":"Nenad Noveljic","twitter_card":"summary_large_image","twitter_creator":"@NenadNoveljic","twitter_misc":{"Written by":"Nenad Noveljic","Est. reading time":"11 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/#article","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/"},"author":{"name":"Nenad Noveljic","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"headline":"Correlated Subqueries in the SELECT Clause (2)","datePublished":"2019-02-26T12:56:23+00:00","dateModified":"2019-02-26T13:20:51+00:00","mainEntityOfPage":{"@id":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/"},"wordCount":395,"commentCount":1,"articleSection":["Comparison Oracle-SQL Server","cost based optimizer","Oracle","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/","url":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/","name":"Correlated Subqueries in the SELECT Clause (2) - All-round Database Topics","isPartOf":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#website"},"datePublished":"2019-02-26T12:56:23+00:00","dateModified":"2019-02-26T13:20:51+00:00","author":{"@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa"},"description":"Examples where Oracle optimizer doesn't unnest subqueries in the SELECT clause","breadcrumb":{"@id":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/nenadnoveljic.com\/blog\/correlated-subqueries-in-the-select-clause-2\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/nenadnoveljic.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Correlated Subqueries in the SELECT Clause (2)"}]},{"@type":"WebSite","@id":"https:\/\/nenadnoveljic.com\/blog\/#website","url":"https:\/\/nenadnoveljic.com\/blog\/","name":"All-round Database Topics","description":"Nenad Noveljic","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/nenadnoveljic.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/nenadnoveljic.com\/blog\/#\/schema\/person\/51458d9dd86dbbdd19f5add451d44efa","name":"Nenad Noveljic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a97b796613ea48ec8a7b79c8ffe1c685dcffc920c68121f6238d5caab5070670?s=96&d=mm&r=g","caption":"Nenad Noveljic"},"sameAs":["nenad-noveljic-9b746a6","https:\/\/x.com\/NenadNoveljic"],"url":"https:\/\/nenadnoveljic.com\/blog\/author\/nenad\/"}]}},"_links":{"self":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2402","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/comments?post=2402"}],"version-history":[{"count":1,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2402\/revisions"}],"predecessor-version":[{"id":2408,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/posts\/2402\/revisions\/2408"}],"wp:attachment":[{"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/media?parent=2402"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/categories?post=2402"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nenadnoveljic.com\/blog\/wp-json\/wp\/v2\/tags?post=2402"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}