SQL==>高级分组

1.为了遍在其他用户下面查看执行计划，先给scott用户授予以下权限

SQL> CONN /AS SYSDBA
Connected.
SQL> grant select on v_$sql_plan to scott;
Grant succeeded.
SQL> grant select on v_$session to scott;
Grant succeeded.
SQL> grant select on v_$sql_plan_statistics_all to scott;
Grant succeeded.
SQL> grant select on v_$sql to scott;
Grant succeeded.

2.创建生成执行计划的脚本：

SQL> !cat showplan_last.sql
set pause off
set verify off
set trimspool on
set line 200 arraysize 1
clear break
clear compute
--serveroutput must be OFF for dbms_xplan.display_cursor to work.
--but do not turn it off here,or the SET satement will be the 'last' cursor
select *  from table(dbms_xplan.display_cursor(null,null,'TYPICAL LAST'));

GROUP BY 子句：

SELECT /*+ gather_plan_statistics */ --告诉优化器通过DBMS_XPLAN收集统计信息

DISTINCT dname,
         decode(d.deptno,
                10,
                (SELECT COUNT(*) FROM emp WHERE deptno = 10),
                20,
                (SELECT COUNT(*) FROM emp WHERE deptno = 20),
                30,
                (SELECT COUNT(*) FROM emp WHERE deptno = 30),
                (SELECT COUNT(*) FROM emp WHERE deptno NOT IN (10, 20, 30))) dept_count
  FROM (SELECT DISTINCT deptno FROM emp) d
  JOIN dept d2

    ON d2.deptno = d.deptno;

SQL> @showplan_last.sql


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 7fzwdwv7s1crk, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */
--?????????????????????DBMS_XPLAN?????????????????? DISTINCT dname,
     decode(d.deptno,      10,  (SELECT
COUNT(*) FROM emp WHERE deptno = 10),      20,
  (SELECT COUNT(*) FROM emp WHERE deptno = 20), 30,
   (SELECT COUNT(*) FROM emp WHERE deptno = 30),
  (SELECT COUNT(*) FROM emp WHERE deptno NOT IN (10, 20, 30)))
dept_count   FROM (SELECT DISTINCT deptno FROM emp) d JOIN dept d2
 ON d2.deptno = d.deptno

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1394181223
-----------------------------------------------------------------------------------------
| Id  | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | | | |     7 (100)| |
|   1 |  SORT AGGREGATE      | |     1 |     3 |     | |
|*  2 |   TABLE ACCESS FULL      | EMP |     5 |    15 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE      | |     1 |     3 |     | |
|*  4 |     TABLE ACCESS FULL      | EMP |     5 |    15 |     3   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   5 |      SORT AGGREGATE      | |     1 |     3 |     | |
|*  6 |       TABLE ACCESS FULL       | EMP |     5 |    15 |     3   (0)| 00:00:01 |
|   7 |        SORT AGGREGATE      | |     1 |     3 |     | |
|*  8 | TABLE ACCESS FULL     | EMP |     4 |    12 |     3   (0)| 00:00:01 |
|   9 |  HASH UNIQUE      | |     9 |   144 |     7  (29)| 00:00:01 |
|  10 |   MERGE JOIN      | |    14 |   224 |     6  (17)| 00:00:01 |
|  11 |    TABLE ACCESS BY INDEX ROWID| DEPT |     4 |    52 |     2   (0)| 00:00:01 |
|  12 |     INDEX FULL SCAN      | PK_DEPT |     4 | |     1   (0)| 00:00:01 |
|* 13 |    SORT JOIN      | |    14 |    42 |     4  (25)| 00:00:01 |
|  14 |     TABLE ACCESS FULL      | EMP |    14 |    42 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPTNO"=10)
   4 - filter("DEPTNO"=20)
   6 - filter("DEPTNO"=30)
   8 - filter(("DEPTNO"<>10 AND "DEPTNO"<>20 AND "DEPTNO"<>30))
  13 - access("D2"."DEPTNO"="DEPTNO")
       filter("D2"."DEPTNO"="DEPTNO")
44 rows selected.

上述语句对emp执行了5次全表扫描，用group 不用改写以上语句：

SELECT /* +gather_plan_statistics */
 d.dname, COUNT(empno) empcount
  FROM emp e
  JOIN dept d
    ON d.deptno = e.deptno
 GROUP BY d.dname
  7   ORDER BY d.dname;


DNAME EMPCOUNT
-------------- ----------
ACCOUNTING 3
RESEARCH 5
SALES 6

SQL> @showplan_last;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3u5r046sxjvmt, child number 0
-------------------------------------
SELECT /* +gather_plan_statistics */  d.dname, COUNT(empno) empcount
FROM emp e   JOIN dept d     ON d.deptno = e.deptno  GROUP BY d.dname
ORDER BY d.dname
Plan hash value: 2970111170
-----------------------------------------------------------------------------------------
| Id  | Operation      | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------