Select * from Subquery VS Select Statement-CodePudding

Below are two statements, is it totally same?

(1)

SELECT *
FROM (SELECT DEPTNO, ROUND(AVG(SAL)) AS AVG_SAL 
     FROM EMP 
     GROUP BY DEPTNO 
     ORDER BY DEPTNO);

(2)

SELECT DEPTNO, ROUND(AVG(SAL)) AS AVG_SAL 
FROM EMP 
GROUP BY DEPTNO 
ORDER BY DEPTNO;

CodePudding user response：

You will find that the queries will generate identical results (as the ORDER BY clause in the sub-query will not be changed by wrapping it in a view that does not apply any additional orderings); however, no, they are not identical if you compare the EXPLAIN PLANs for the two queries:

EXPLAIN PLAN FOR
SELECT *
FROM (SELECT DEPTNO, ROUND(AVG(SAL)) AS AVG_SAL 
      FROM EMP 
      GROUP BY DEPTNO 
      ORDER BY DEPTNO);

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Outputs:

| PLAN_TABLE_OUTPUT                                                            |
| :--------------------------------------------------------------------------- |
| Plan hash value: 2533405727                                                  |
|                                                                              |
| ---------------------------------------------------------------------------- |
| | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | |
| ---------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT    |      |    14 |   364 |     4  (25)| 00:00:01 | |
| |   1 |  VIEW               |      |    14 |   364 |     4  (25)| 00:00:01 | |
| |   2 |   SORT GROUP BY     |      |    14 |   364 |     4  (25)| 00:00:01 | |
| |   3 |    TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 | |
| ---------------------------------------------------------------------------- |
|                                                                              |
| Note                                                                         |
| -----                                                                        |
|    - dynamic statistics used: dynamic sampling (level=2)                     |

and then:

EXPLAIN PLAN FOR
SELECT DEPTNO, ROUND(AVG(SAL)) AS AVG_SAL 
FROM EMP 
GROUP BY DEPTNO 
ORDER BY DEPTNO;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

| PLAN_TABLE_OUTPUT                                                           |
| :-------------------------------------------------------------------------- |
| Plan hash value: 15469362                                                   |
|                                                                             |
| --------------------------------------------------------------------------- |
| | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | |
| --------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT   |      |    14 |   364 |     4  (25)| 00:00:01 | |
| |   1 |  SORT GROUP BY     |      |    14 |   364 |     4  (25)| 00:00:01 | |
| |   2 |   TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 | |
| --------------------------------------------------------------------------- |
|                                                                             |
| Note                                                                        |
| -----                                                                       |
|    - dynamic statistics used: dynamic sampling (level=2)                    |

db<>fiddle here

There is an additional step in the explain plan for the first query to generate the view that is not optimised out by the SQL compiler.

CodePudding user response：

No, to get a identical statement you must add the outer order by clause to the statement (1)

SELECT *
FROM (SELECT DEPTNO, ROUND(AVG(SAL)) AS AVG_SAL 
     FROM EMP 
     GROUP BY DEPTNO 
     ORDER BY DEPTNO)
ORDER BY DEPTNO;

This will allow Oracle to eliminate the subquery and produce an identical execution plan as for the query (2)

-------------------------------------------
| Id  | Operation                  | Name | 
-------------------------------------------
|   0 | SELECT STATEMENT           |      |  
|   1 |  SORT GROUP BY             |      |  
|   2 |   TABLE ACCESS STORAGE FULL| EMP  | 
-------------------------------------------

In your formulation you can expect the same result of both queries, but you must be prepared for surprise.