Sequence of query execution

Question

When there is a correlated query, what is the sequence of execution?

Ex:

select
  p.productNo,
  (
    select count(distinct concat(bom.detailpart,bom.groupname)) 
    from dl_MBOM bom 
    where bom.DetailPart=p.ProductNo
  ) cnt1 
from dm_product p

score 0 · Answer 1 · answered Feb 14 '17 at 23:51

0

While it seems likely that the DBMS reads record for record from dm_product and for each such record looks up the value in dl_MBOM, this doesn't necessarily happen.

With an SQL query you tell the DBMS mainly what to do, not how to do it. If the DBMS thinks it better to build a join instead and work on this, it is free to do so.

Short answer: the sequence of execution is not determined. (You can, however, in many DBMS look at the query's execution plan to see how it is executed.)

answered Feb 14 '17 at 23:51

Thorsten Kettner

89,309
7
49
73

I would like to know how the inner query would work. For the inner query to work, it has to refer to the table out of the inner query. – Kaushik Feb 15 '17 at 00:20
Yes. We read one record from `dm_product`. We take its `ProductNo` and collect all `dl_MBOM` records with that `DetailPart`. We now have an array of strings consisting of `detailpart` + `groupname`. We scan it and count (each unique string only once). Now we have the first result row. We read the next record from `dm_product`, etc. – Thorsten Kettner Feb 15 '17 at 06:34
... Only that we could re-write the whole query such that we outer join a list of `dl_MBOM` aggregates. The idea here is that we would read the `dl_MBOM` table first and get the distinct counts per `DetailPart`. Then we might do as above, only that we look up our pre-aggregated records per `dm_product` record. But we could just as well read this intermediate table and put its records in hash buckets. Then we'd read the table `dm_product` and put its records in the buckets, too. And then we'd look up the buckets to get to our result records. Same thing but a completely different execution plan. – Thorsten Kettner Feb 15 '17 at 06:46
... The DBMS is free to internally re-write the query,too. And it's free to decide whether to read one table record for record and then look up the other table for each such record or to use some join mechanism, e.g. the hash algorithm described above. It is free to decide whether to read a table record per record or to use an index to access records quicker maybe or already in a desired order. *How* our query gets executed is not our concern. The DBMS will try to find the fastest algorithm. – Thorsten Kettner Feb 15 '17 at 06:53
... We can help the DBMS optimizer in this task, however, by writing good, readable queries. (E.g. not to write an outer join when we know there will never *be* any mismatch, so it's merely a hidden inner join.) – Thorsten Kettner Feb 15 '17 at 06:54

score 0 · Accepted Answer · answered Feb 28 '17 at 19:36

The execution plan will vary by database vendors. For Oracle, here is a similar query, and the corresponding execution plan.

  select dname,
     ( select count( distinct job )
            from emp e
            where e.deptno = d.deptno
          ) x
  from dept d



---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          |
|   1 |  SORT GROUP BY     |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    55 |     2   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL | DEPT |     4 |    52 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("E"."DEPTNO"=:B1)

Sequence of query execution

2 Answers2