执行计划 - 墨天轮文档

执行计划

童志伟

593

6页

0次

2020-03-07

5墨值下载

SQL_ID bwvcagnxygsrj, child number 0

-------------------------------------

WITH tmpCardInfo AS ( SELECT m.memberid,mobile,idcard,guestname,POIN

T,cardno FROM cardstock.memberguest m,cardstock.GUEST g WHERE (

m.memberid=g.memberid ) AND CardNO='201000722403' ) SELECT /*+

gather_plan_statistics */ i.memberid,i.mobile,i.idcard,i.guestname,i.car

dno,i.point,NVL(SUM(POINT2019),0) point2019,i.point-NVL(SUM(point2019),0

) point2018 FROM tmpCardInfo i LEFT JOIN ( SELECT 'N'

flag,t.cardno,NVL(sum(a.point),0) POINT2019 FROM tmpCardInfo

t,cardstock.GuestPurch a WHERE (t.cardno=a.cardno) AND

to_char(PurchDatetime,'yyyy-mm-dd') BETWEEN '2019-01-01' AND

'2019-12-31' GROUP BY t.cardno UNION SELECT 'T'

flag,t.cardno,SUM(NVL(a.point,0)) POINT FROM tmpCardInfo t,

cardstock.GuestPurch0 a WHERE (t.cardno=a.cardno) GROUP BY

t.cardNO UNION SELECT 'O',s.cardno,SUM(nvl(a.point,0)) FROM

cardstock.exchangesaleitem s,cardstock.ExchangeQuitItem q,tmpCardInfo

t, cardstock.CardAccClear a WHERE (s.sheetid=q.sheetid AND

s.cardno=t.Car

Plan hash value: 3115938894

--------------------------------------------------------------------------------

------------------------------------------

| Id | Operation | Name |

--------------------------------------------------------------------------------

------------------------------------------

| 0 | SELECT STATEMENT | |

1 | | | 15 (100)| | 1 |00:00:08.75 | 2818K|

1 | 1 | | | |

| 1 | TEMP TABLE TRANSFORMATION | |

1 | | | | | 1 |00:00:08.75 | 2818K|

1 | 1 | | | |

| 2 | LOAD AS SELECT | |

1 | | | | | 0 |00:00:00.01 | 12 |

0 | 1 | 270K| 270K| 270K (0)|

| 3 | NESTED LOOPS | |

1 | 1 | 111 | 0 (0)| | 1 |00:00:00.01 | 8 |

0 | 0 | | | |

| 4 | TABLE ACCESS BY INDEX ROWID | GUEST |

1 | 1 | 36 | 0 (0)| | 1 |00:00:00.01 | 4 |

0 | 0 | | | |

|* 5 | INDEX UNIQUE SCAN | PK_GUEST |

1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 3 |

0 | 0 | | | |

| 6 | TABLE ACCESS BY INDEX ROWID | MEMBERGUEST |

1 | 1 | 75 | 0 (0)| | 1 |00:00:00.01 | 4 |

0 | 0 | | | |

|* 7 | INDEX RANGE SCAN | I_MEMBERGUEST2 |

1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 3 |

0 | 0 | | | |

| 8 | HASH GROUP BY | |

1 | 1 | 123 | 15 (47)| 00:00:01 | 1 |00:00:08.75 | 2818K|

1 | 0 | 856K| 856K| 487K (0)|

|* 9 | HASH JOIN OUTER | |

1 | 1 | 123 | 14 (43)| 00:00:01 | 2 |00:00:08.75 | 2818K|

1 | 0 | 911K| 911K| 414K (0)|

| 10 | VIEW | |

1 | 1 | 99 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |

1 | 0 | | | |

| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68F6_CF5666DC |

1 | 1 | 99 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |

1 | 0 | | | |

| 12 | VIEW | |

1 | 3 | 72 | 12 (50)| 00:00:01 | 2 |00:00:08.75 | 2818K|

0 | 0 | | | |

| 13 | SORT UNIQUE | |

1 | 3 | 178 | 12 (50)| 00:00:01 | 2 |00:00:08.75 | 2818K|

0 | 0 | 2048 | 2048 | 2048 (0)|

| 14 | UNION-ALL | |

1 | | | | | 2 |00:00:08.75 | 2818K|

0 | 0 | | | |

| 15 | HASH GROUP BY | |

1 | 1 | 44 | 4 (50)| 00:00:01 | 1 |00:00:08.73 | 2809K|

0 | 0 | 1097K| 1097K| 466K (0)|

| 16 | MERGE JOIN | |

1 | 1 | 44 | 3 (34)| 00:00:01 | 4 |00:00:08.73 | 2809K|

0 | 0 | | | |

|* 17 | TABLE ACCESS BY INDEX ROWID| GUESTPURCH |

1 | 1 | 33 | 0 (0)| | 2639K|00:00:07.42 | 2809K|

0 | 0 | | | |

| 18 | INDEX FULL SCAN | GUESTPURCHCARDNO |

1 | 1 | | 0 (0)| | 2877K|00:00:00.56 | 12793 |

0 | 0 | | | |

|* 19 | SORT JOIN | |

2639K| 1 | 11 | 3 (34)| 00:00:01 | 4 |00:00:00.96 | 2 |

0 | 0 | 2048 | 2048 | 2048 (0)|

| 20 | VIEW | |

1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

0 | 0 | | | |

| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68F6_CF5666DC |

1 | 1 | 99 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

0 | 0 | | | |

| 22 | HASH GROUP BY | |

1 | 1 | 35 | 4 (50)| 00:00:01 | 0 |00:00:00.02 | 9475 |

0 | 0 | 1048K| 1048K| |

| 23 | MERGE JOIN | |

1 | 1 | 35 | 3 (34)| 00:00:01 | 0 |00:00:00.02 | 9475 |

0 | 0 | | | |

| 24 | TABLE ACCESS BY INDEX ROWID| GUESTPURCH0 |

1 | 1 | 24 | 0 (0)| | 9612 |00:00:00.01 | 9473 |

0 | 0 | | | |

| 25 | INDEX FULL SCAN | GP0CARDNO |

1 | 1 | | 0 (0)| | 9612 |00:00:00.01 | 143 |

0 | 0 | | | |

|* 26 | SORT JOIN | |

9612 | 1 | 11 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 2 |

0 | 0 | 2048 | 2048 | 2048 (0)|

| 27 | VIEW | |

1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

0 | 0 | | | |

| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68F6_CF5666DC |

1 | 1 | 99 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

0 | 0 | | | |

| 29 | HASH GROUP BY | |

1 | 1 | 99 | 4 (50)| 00:00:01 | 1 |00:00:00.01 | 28 |

0 | 0 | 1097K| 1097K| 450K (0)|

| 30 | NESTED LOOPS | |

1 | 1 | 99 | 2 (0)| 00:00:01 | 154 |00:00:00.01 | 28 |

0 | 0 | | | |

| 31 | NESTED LOOPS | |

1 | 1 | 99 | 2 (0)| 00:00:01 | 164 |00:00:00.01 | 25 |

0 | 0 | | | |

| 32 | NESTED LOOPS | |

SQL_ID bwvcagnxygsrj, child number 0

-------------------------------------

WITH tmpCardInfo AS ( SELECT m.memberid,mobile,idcard,guestname,POIN

T,cardno FROM cardstock.memberguest m,cardstock.GUEST g WHERE (

m.memberid=g.memberid ) AND CardNO='201000722403' ) SELECT /*+

gather_plan_statistics */ i.memberid,i.mobile,i.idcard,i.guestname,i.car

dno,i.point,NVL(SUM(POINT2019),0) point2019,i.point-NVL(SUM(point2019),0

) point2018 FROM tmpCardInfo i LEFT JOIN ( SELECT 'N'

flag,t.cardno,NVL(sum(a.point),0) POINT2019 FROM tmpCardInfo

t,cardstock.GuestPurch a WHERE (t.cardno=a.cardno) AND

to_char(PurchDatetime,'yyyy-mm-dd') BETWEEN '2019-01-01' AND

'2019-12-31' GROUP BY t.cardno UNION SELECT 'T'

flag,t.cardno,SUM(NVL(a.point,0)) POINT FROM tmpCardInfo t,

cardstock.GuestPurch0 a WHERE (t.cardno=a.cardno) GROUP BY

t.cardNO UNION SELECT 'O',s.cardno,SUM(nvl(a.point,0)) FROM

cardstock.exchangesaleitem s,cardstock.ExchangeQuitItem q,tmpCardInfo

t, cardstock.CardAccClear a WHERE (s.sheetid=q.sheetid AND

s.cardno=t.Car

Plan hash value: 3115938894

--------------------------------------------------------------------------------

------------------------------------------

| Id | Operation | Name |

--------------------------------------------------------------------------------

------------------------------------------

| 0 | SELECT STATEMENT | |

1 | | | 15 (100)| | 1 |00:00:08.75 | 2818K|

1 | 1 | | | |

| 1 | TEMP TABLE TRANSFORMATION | |

1 | | | | | 1 |00:00:08.75 | 2818K|

1 | 1 | | | |

| 2 | LOAD AS SELECT | |

1 | | | | | 0 |00:00:00.01 | 12 |

0 | 1 | 270K| 270K| 270K (0)|

| 3 | NESTED LOOPS | |

1 | 1 | 111 | 0 (0)| | 1 |00:00:00.01 | 8 |

0 | 0 | | | |

| 4 | TABLE ACCESS BY INDEX ROWID | GUEST |

1 | 1 | 36 | 0 (0)| | 1 |00:00:00.01 | 4 |

0 | 0 | | | |

|* 5 | INDEX UNIQUE SCAN | PK_GUEST |

1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 3 |

0 | 0 | | | |

| 6 | TABLE ACCESS BY INDEX ROWID | MEMBERGUEST |

1 | 1 | 75 | 0 (0)| | 1 |00:00:00.01 | 4 |

0 | 0 | | | |

|* 7 | INDEX RANGE SCAN | I_MEMBERGUEST2 |

1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 3 |

0 | 0 | | | |

| 8 | HASH GROUP BY | |

1 | 1 | 123 | 15 (47)| 00:00:01 | 1 |00:00:08.75 | 2818K|

1 | 0 | 856K| 856K| 487K (0)|

|* 9 | HASH JOIN OUTER | |

1 | 1 | 123 | 14 (43)| 00:00:01 | 2 |00:00:08.75 | 2818K|

1 | 0 | 911K| 911K| 414K (0)|

| 10 | VIEW | |

1 | 1 | 99 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |

1 | 0 | | | |

| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68F6_CF5666DC |

1 | 1 | 99 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 5 |

1 | 0 | | | |

| 12 | VIEW | |

1 | 3 | 72 | 12 (50)| 00:00:01 | 2 |00:00:08.75 | 2818K|

0 | 0 | | | |

| 13 | SORT UNIQUE | |

1 | 3 | 178 | 12 (50)| 00:00:01 | 2 |00:00:08.75 | 2818K|

0 | 0 | 2048 | 2048 | 2048 (0)|

| 14 | UNION-ALL | |

1 | | | | | 2 |00:00:08.75 | 2818K|

0 | 0 | | | |

| 15 | HASH GROUP BY | |

1 | 1 | 44 | 4 (50)| 00:00:01 | 1 |00:00:08.73 | 2809K|

0 | 0 | 1097K| 1097K| 466K (0)|

| 16 | MERGE JOIN | |

1 | 1 | 44 | 3 (34)| 00:00:01 | 4 |00:00:08.73 | 2809K|

0 | 0 | | | |

|* 17 | TABLE ACCESS BY INDEX ROWID| GUESTPURCH |

1 | 1 | 33 | 0 (0)| | 2639K|00:00:07.42 | 2809K|

0 | 0 | | | |

| 18 | INDEX FULL SCAN | GUESTPURCHCARDNO |

1 | 1 | | 0 (0)| | 2877K|00:00:00.56 | 12793 |

0 | 0 | | | |

|* 19 | SORT JOIN | |

2639K| 1 | 11 | 3 (34)| 00:00:01 | 4 |00:00:00.96 | 2 |

0 | 0 | 2048 | 2048 | 2048 (0)|

| 20 | VIEW | |

1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

0 | 0 | | | |

| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68F6_CF5666DC |

1 | 1 | 99 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

0 | 0 | | | |

| 22 | HASH GROUP BY | |

1 | 1 | 35 | 4 (50)| 00:00:01 | 0 |00:00:00.02 | 9475 |

0 | 0 | 1048K| 1048K| |

| 23 | MERGE JOIN | |

1 | 1 | 35 | 3 (34)| 00:00:01 | 0 |00:00:00.02 | 9475 |

0 | 0 | | | |

| 24 | TABLE ACCESS BY INDEX ROWID| GUESTPURCH0 |

1 | 1 | 24 | 0 (0)| | 9612 |00:00:00.01 | 9473 |

0 | 0 | | | |

| 25 | INDEX FULL SCAN | GP0CARDNO |

1 | 1 | | 0 (0)| | 9612 |00:00:00.01 | 143 |

0 | 0 | | | |

|* 26 | SORT JOIN | |

9612 | 1 | 11 | 3 (34)| 00:00:01 | 0 |00:00:00.01 | 2 |

0 | 0 | 2048 | 2048 | 2048 (0)|

| 27 | VIEW | |

1 | 1 | 11 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

0 | 0 | | | |

| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68F6_CF5666DC |

1 | 1 | 99 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |

0 | 0 | | | |

| 29 | HASH GROUP BY | |

1 | 1 | 99 | 4 (50)| 00:00:01 | 1 |00:00:00.01 | 28 |

0 | 0 | 1097K| 1097K| 450K (0)|

| 30 | NESTED LOOPS | |

1 | 1 | 99 | 2 (0)| 00:00:01 | 154 |00:00:00.01 | 28 |

0 | 0 | | | |

| 31 | NESTED LOOPS | |

1 | 1 | 99 | 2 (0)| 00:00:01 | 164 |00:00:00.01 | 25 |

0 | 0 | | | |

| 32 | NESTED LOOPS | |

of 6

5墨值下载

oracle 官方文档安装部署

关注

评论