如题,以前,每次人家问我sql执行的时候在什么情况下会出现排序。我的答案中都会含有group by 子句,后来我发现我错了。
下边是一个用到group by子句但没有排序的例子。
SQL> set autot on
SQL> select * from (select min(rownum) from dual where rownum < 10 group by rownum);
MIN(ROWNUM)
-----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3333817076
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 2 | SORT GROUP BY NOSORT| | 1 | | 2 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在执行计划中的 SORT GROUP BY NOSORT 可以看出没有排序。
其实,在这里oracle在执行到group by子句时,发现该处已经是排好序的,故其不在进行sort操作。
下边是一个用到group by子句但没有排序的例子。
SQL> set autot on
SQL> select * from (select min(rownum) from dual where rownum < 10 group by rownum);
MIN(ROWNUM)
-----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 3333817076
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 2 | SORT GROUP BY NOSORT| | 1 | | 2 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
在执行计划中的 SORT GROUP BY NOSORT 可以看出没有排序。
其实,在这里oracle在执行到group by子句时,发现该处已经是排好序的,故其不在进行sort操作。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26143577/viewspace-750073/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26143577/viewspace-750073/