newkid 发表于 2014-4-3 23:48
靠,这可是正面宣传好不好。
newkid大哥,我把上述几种方法做了执行计划,发现2和3与1相比,代价较高,给我推荐下哪种方式更好呢,
第一种:
SQL> SELECT t1.deptno,
2 (SELECT COUNT(1) FROM emp1 t WHERE t1.deptno = t.deptno) EMP1_COUNT,
3 (SELECT SUM(SAL) FROM emp1 t WHERE t1.deptno = t.deptno) EMP1_SAL,
4 (SELECT COUNT(1) FROM emp2 t WHERE t1.deptno = t.deptno) EMP2_COUNT,
5 (SELECT SUM(SAL) FROM emp2 t WHERE t1.deptno = t.deptno) EMP2_SAL,
6 (SELECT COUNT(1) FROM emp3 t WHERE t1.deptno = t.deptno) EMP3_COUNT,
7 (SELECT SUM(SAL) FROM emp3 t WHERE t1.deptno = t.deptno) EMP3_SAL,
8 (SELECT COUNT(1) FROM emp4 t WHERE t1.deptno = t.deptno) EMP4_COUNT,
9 (SELECT SUM(SAL) FROM emp4 t WHERE t1.deptno = t.deptno) EMP4_SAL
10 FROM dept t1
11 GROUP BY t1.deptno;
DEPTNO EMP1_COUNT EMP1_SAL EMP2_COUNT EMP2_SAL EMP3_COUNT EMP3_SAL EMP4_COUNT EMP4_SAL
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
10 3 8750 3 8750 3 8750 3 8750
20 5 10875 5 10875 5 10875 5 10875
30 6 9400 6 9400 6 9400 6 9400
40 0 0 0 0
已用时间: 00: 00: 00.06
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
------------------------------
SQL_ID 8cbdcbgjchjwg, child number 1
-------------------------------------
SELECT t1.deptno, (SELECT COUNT(1) FROM emp1 t WHERE t1.deptno
= t.deptno) EMP1_COUNT, (SELECT SUM(SAL) FROM emp1 t WHERE
t1.deptno = t.deptno) EMP1_SAL, (SELECT COUNT(1) FROM emp2 t
WHERE t1.deptno = t.deptno) EMP2_COUNT, (SELECT SUM(SAL) FROM
emp2 t WHERE t1.deptno = t.deptno) EMP2_SAL, (SELECT COUNT(1)
FROM emp3 t WHERE t1.deptno = t.deptno) EMP3_COUNT, (SELECT
SUM(SAL) FROM emp3 t WHERE t1.deptno = t.deptno) EMP3_SAL,
(SELECT COUNT(1) FROM emp4 t WHERE t1.deptno = t.deptno) EMP4_COUNT,
(SELECT SUM(SAL) FROM emp4 t WHERE t1.deptno = t.deptno) EMP4_SAL
FROM dept t1 GROUP BY t1.deptno
Plan hash value: 1305195623
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 2 |
| 1 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.01 | 12 |
|* 2 | TABLE ACCESS FULL | EMP1 | 4 | 5 | 14 |00:00:00.01 | 12 |
| 3 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.01 | 12 |
|* 4 | TABLE ACCESS FULL | EMP1 | 4 | 5 | 14 |00:00:00.01 | 12 |
| 5 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.01 | 12 |
|* 6 | TABLE ACCESS FULL | EMP2 | 4 | 5 | 14 |00:00:00.01 | 12 |
| 7 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.01 | 12 |
|* 8 | TABLE ACCESS FULL | EMP2 | 4 | 5 | 14 |00:00:00.01 | 12 |
| 9 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.01 | 12 |
|* 10 | TABLE ACCESS FULL | EMP3 | 4 | 5 | 14 |00:00:00.01 | 12 |
| 11 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.01 | 12 |
|* 12 | TABLE ACCESS FULL | EMP3 | 4 | 5 | 14 |00:00:00.01 | 12 |
| 13 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.01 | 12 |
|* 14 | TABLE ACCESS FULL | EMP4 | 4 | 5 | 14 |00:00:00.01 | 12 |
| 15 | SORT AGGREGATE | | 4 | 1 | 4 |00:00:00.01 | 12 |
|* 16 | TABLE ACCESS FULL | EMP4 | 4 | 5 | 14 |00:00:00.01 | 12 |
| 17 | SORT GROUP BY NOSORT| | 1 | 4 | 4 |00:00:00.01 | 2 |
| 18 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("T"."DEPTNO"=:B1)
4 - filter("T"."DEPTNO"=:B1)
6 - filter("T"."DEPTNO"=:B1)
8 - filter("T"."DEPTNO"=:B1)
10 - filter("T"."DEPTNO"=:B1)
12 - filter("T"."DEPTNO"=:B1)
14 - filter("T"."DEPTNO"=:B1)
16 - filter("T"."DEPTNO"=:B1)
第二种:
SQL> WITH A AS
2 (SELECT COUNT(DEPTNO) AS COUNTS_1, DEPTNO, SUM(SAL) AS SAL1 FROM emp1 GROUP BY DEPTNO),
3 T2 AS
4 (SELECT COUNT(DEPTNO) AS COUNTS_2, DEPTNO, SUM(SAL) AS SAL2 FROM emp2 GROUP BY DEPTNO),
5 t3 AS
6 (SELECT COUNT(DEPTNO) AS COUNTS_3, DEPTNO, SUM(SAL) AS SAL4 FROM emp3 GROUP BY DEPTNO),
7 t4 AS
8 (SELECT COUNT(DEPTNO) AS COUNTS_4, DEPTNO, SUM(SAL) AS SAL4 FROM emp4 GROUP BY DEPTNO)
9 SELECT *
10 FROM DEPT T1
11 LEFT JOIN A
12 ON T1.DEPTNO = A.DEPTNO
13 LEFT JOIN T2
14 ON T1.DEPTNO = T2.DEPTNO
15 LEFT JOIN T3
16 ON T1.DEPTNO = T3.DEPTNO
17 LEFT JOIN T4
18 ON T1.DEPTNO = T4.DEPTNO;
DEPTNO DNAME LOC COUNTS_1 DEPTNO SAL1 COUNTS_2 DEPTNO SAL2 COUNTS_3 DEPTNO
---------- -------------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
30 SALES CHICAGO 6 30 9400 6 30 9400 6 30
20 RESEARCH DALLAS 5 20 10875 5 20 10875 5 20
10 ACCOUNTING NEW YORK 3 10 8750 3 10 8750 3 10
40 OPERATIONS BOSTON
已用时间: 00: 00: 00.04
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------
------------------------------
SQL_ID 8u86r1c19dyvd, child number 1
-------------------------------------
WITH A AS (SELECT COUNT(DEPTNO) AS COUNTS_1, DEPTNO, SUM(SAL) AS SAL1
FROM emp1 GROUP BY DEPTNO), T2 AS (SELECT COUNT(DEPTNO) AS COUNTS_2,
DEPTNO, SUM(SAL) AS SAL2 FROM emp2 GROUP BY DEPTNO), t3 AS (SELECT
COUNT(DEPTNO) AS COUNTS_3, DEPTNO, SUM(SAL) AS SAL4 FROM emp3 GROUP BY
DEPTNO), t4 AS (SELECT COUNT(DEPTNO) AS COUNTS_4, DEPTNO, SUM(SAL) AS
SAL4 FROM emp4 GROUP BY DEPTNO) SELECT * FROM DEPT T1 LEFT JOIN A
ON T1.DEPTNO = A.DEPTNO LEFT JOIN T2 ON T1.DEPTNO = T2.DEPTNO
LEFT JOIN T3 ON T1.DEPTNO = T3.DEPTNO LEFT JOIN T4 ON
T1.DEPTNO = T4.DEPTNO
Plan hash value: 1077397451
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 14 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 4 | 4 |00:00:00.01 | 14 | 746K| 746K| 732K (0)|
|* 2 | HASH JOIN OUTER | | 1 | 4 | 4 |00:00:00.01 | 11 | 762K| 762K| 720K (0)|
|* 3 | HASH JOIN OUTER | | 1 | 4 | 4 |00:00:00.01 | 8 | 786K| 786K| 735K (0)|
| 4 | MERGE JOIN OUTER | | 1 | 4 | 4 |00:00:00.01 | 5 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 4 |00:00:00.01 | 2 | | | |
| 6 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
|* 7 | SORT JOIN | | 4 | 3 | 3 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 8 | VIEW | | 1 | 3 | 3 |00:00:00.01 | 3 | | | |
| 9 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.01 | 3 | 779K| 779K| 1120K (0)|
| 10 | TABLE ACCESS FULL | EMP1 | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
| 11 | VIEW | | 1 | 3 | 3 |00:00:00.01 | 3 | | | |
| 12 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.01 | 3 | 779K| 779K| 1120K (0)|
| 13 | TABLE ACCESS FULL | EMP2 | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
| 14 | VIEW | | 1 | 3 | 3 |00:00:00.01 | 3 | | | |
| 15 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.01 | 3 | 779K| 779K| 1118K (0)|
| 16 | TABLE ACCESS FULL | EMP3 | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
| 17 | VIEW | | 1 | 3 | 3 |00:00:00.01 | 3 | | | |
| 18 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.01 | 3 | 779K| 779K| 1116K (0)|
| 19 | TABLE ACCESS FULL | EMP4 | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
-------------------------------------------------------------------------------------------------------------------------------
第三种:
已用时间: 00: 00: 00.26
SQL> SELECT *
2 FROM DEPT T1
3 LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_1, DEPTNO, SUM(SAL) AS SAL1 FROM emp1 GROUP BY DEPTNO) A
4 ON T1.DEPTNO = A.DEPTNO
5 LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_2, DEPTNO, SUM(SAL) AS SAL2 FROM emp2 GROUP BY DEPTNO) T2
6 ON T1.DEPTNO = T2.DEPTNO
7 LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_3, DEPTNO, SUM(SAL) AS SAL4 FROM emp3 GROUP BY DEPTNO) T3
8 ON T1.DEPTNO = T3.DEPTNO
9 LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_4, DEPTNO, SUM(SAL) AS SAL4 FROM emp4 GROUP BY DEPTNO) T4
10 ON T1.DEPTNO = T4.DEPTNO
11 ;
DEPTNO DNAME LOC COUNTS_1 DEPTNO SAL1 COUNTS_2 DEPTNO SAL2 COUNTS_3 DEPTNO SAL4
---------- -------------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
30 SALES CHICAGO 6 30 9400 6 30 9400 6 30 9400
20 RESEARCH DALLAS 5 20 10875 5 20 10875 5 20 10875
10 ACCOUNTING NEW YORK 3 10 8750 3 10 8750 3 10 8750
40 OPERATIONS BOSTON
已用时间: 00: 00: 00.03
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
------------------------------
SQL_ID 1yz5h8ca86jta, child number 1
-------------------------------------
SELECT * FROM DEPT T1 LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_1,
DEPTNO, SUM(SAL) AS SAL1 FROM emp1 GROUP BY DEPTNO) A ON T1.DEPTNO
= A.DEPTNO LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_2, DEPTNO,
SUM(SAL) AS SAL2 FROM emp2 GROUP BY DEPTNO) T2 ON T1.DEPTNO =
T2.DEPTNO LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_3, DEPTNO,
SUM(SAL) AS SAL4 FROM emp3 GROUP BY DEPTNO) T3 ON T1.DEPTNO =
T3.DEPTNO LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_4, DEPTNO,
SUM(SAL) AS SAL4 FROM emp4 GROUP BY DEPTNO) T4 ON T1.DEPTNO =
T4.DEPTNO
Plan hash value: 1077397451
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 4 |00:00:00.01 | 14 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 4 | 4 |00:00:00.01 | 14 | 746K| 746K| 732K (0)|
|* 2 | HASH JOIN OUTER | | 1 | 4 | 4 |00:00:00.01 | 11 | 762K| 762K| 723K (0)|
|* 3 | HASH JOIN OUTER | | 1 | 4 | 4 |00:00:00.01 | 8 | 786K| 786K| 738K (0)|
| 4 | MERGE JOIN OUTER | | 1 | 4 | 4 |00:00:00.01 | 5 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 4 |00:00:00.01 | 2 | | | |
| 6 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
|* 7 | SORT JOIN | | 4 | 3 | 3 |00:00:00.01 | 3 | 2048 | 2048 | 2048 (0)|
| 8 | VIEW | | 1 | 3 | 3 |00:00:00.01 | 3 | | | |
| 9 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.01 | 3 | 779K| 779K| 1118K (0)|
| 10 | TABLE ACCESS FULL | EMP1 | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
| 11 | VIEW | | 1 | 3 | 3 |00:00:00.01 | 3 | | | |
| 12 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.01 | 3 | 779K| 779K| 1117K (0)|
| 13 | TABLE ACCESS FULL | EMP2 | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
| 14 | VIEW | | 1 | 3 | 3 |00:00:00.01 | 3 | | | |
| 15 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.01 | 3 | 779K| 779K| 1120K (0)|
| 16 | TABLE ACCESS FULL | EMP3 | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
| 17 | VIEW | | 1 | 3 | 3 |00:00:00.01 | 3 | | | |
| 18 | HASH GROUP BY | | 1 | 3 | 3 |00:00:00.01 | 3 | 779K| 779K| 1116K (0)|
| 19 | TABLE ACCESS FULL | EMP4 | 1 | 14 | 14 |00:00:00.01 | 3 | | | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."DEPTNO"="T4"."DEPTNO")
2 - access("T1"."DEPTNO"="T3"."DEPTNO")
3 - access("T1"."DEPTNO"="T2"."DEPTNO")
7 - access("T1"."DEPTNO"="A"."DEPTNO")
filter("T1"."DEPTNO"="A"."DEPTNO")
我觉得1的效果,好点,我刚刚学习这个,不太理解,到底哪个好,