3,491
社区成员
发帖
与我相关
我的任务
分享
FROM dcp1 dccp1,t06020dca91 dca91,t06020dca92 dca92
WHERE dca92.co=dca91.co AND dca92.fctclass=dca91.fctclass AND dca92.hmat=dca91.hmat AND dca91.hmat LIKE 'G%'
AND dccp1.co=dca92.co AND dccp1.mtno=dca91.mtsid||dca91.colorid||dca91.misectscalesid
AND dccp1.co='3' AND dccp1.txemp='DSP'
--现在csdn格式OK了,重新用格式编辑下该SQL语句
INSERT INTO S06020DCCP1(CO,COABR,FCTCLASS,FCTCLASSNM,TGDSDAY,DSTPORT,DSTPORTNM,LINECLASS
,SOURODERNO,RAMTXSNOYD,OPNRARGN,OPNRADAT,SPEC,SPECNM,SKU,MTCHCLRS
,ODQTY,ITM,LVID,VER,SZNUMBER,RGNID
,RGNNM,MTNO,MTNM,COLOR,UN
,UQTY,XREM,JMPGASACO,SORTASACO,TXEMP,TXDAT,TXTM
,RFLDONE,LISMPID, QTY,LISDP)
SELECT CO, COABR, FCTCLASS, FCTCLASSNM, TGDSDAY, DSTPORT, DSTPORTNM, LINECLASS,
SOURODERNO, RAMTXSNOYD, OPNRARGN,OPNRADAT, SPEC, SPECNM, SKU, MTCHCLRS,
ODQTY, ITM, LVID, VER, SZNUMBER, RGNID,
RGNNM, MTNO, MTNM, COLOR,UN,
UQTY, XREM,JMPGASACO, SORTASACO, TXEMP, TXDAT, TXTM,
RFLDONE, LISMPID, QTY,LISDP
FROM (
SELECT DISTINCT dccp1.CO, dccp1.COABR, dccp1.FCTCLASS, dccp1.FCTCLASSNM, dccp1.TGDSDAY, dccp1.DSTPORT, dccp1.DSTPORTNM,dccp1.LINECLASS,
dccp1.SOURODERNO, dccp1.RAMTXSNOYD, dccp1.OPNRARGN, dccp1.OPNRADAT, dccp1.SPEC, dccp1.SPECNM,dccp1.SKU, dccp1.MTCHCLRS,
dccp1.ODQTY, dccp1.ITM, dccp1.LVID, dccp1.VER, dccp1.SZNUMBER, dccp1.RGNID,
dccp1.RGNNM,dca92.mtsid||dca92.colorid||dca92.misectscalesid mtno,'' mtnm, dca92.colorid COLOR, dccp1.UN,
dccp1.uqty * dca92.unuqty UQTY,dccp1.XREM, dccp1.JMPGASACO,dccp1.SORTASACO, dccp1.TXEMP, dccp1.TXDAT, dccp1.TXTM,
dccp1.RFLDONE,dccp1.LisMpId, dccp1.QTY ,dccp1.LISDP
FROM dcp1 dccp1,t06020dca91 dca91,t06020dca92 dca92
WHERE dca92.co=dca91.co AND dca92.fctclass=dca91.fctclass AND dca92.hmat=dca91.hmat AND dca91.hmat LIKE 'G%'
AND dccp1.co=dca92.co AND dccp1.mtno=dca91.mtsid||dca91.colorid||dca91.misectscalesid
AND dccp1.co='3' AND dccp1.txemp='DSP'
);
--同志们,有没有啥子想法啊???
--几个表的数据一直式没变过,笔数都没变过:
SELECT Count(*) FROM S06020DCCP1; --115333
SELECT Count(*) FROM dcp1 ; --3
SELECT Count(*) FROM t06020dca91 ; --488174
SELECT Count(*) FROM t06020dca92 ; --1167802
--现在我还真有点搞不懂了,确实执行计划不一样:
--1、查询6笔,插入4306笔,执行计划分别是
PLAN_TABLE_OUTPUT
Plan hash value: 921494302
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2130 | 1329 (2)| 00:00:16 |
| 1 | VIEW | | 1 | 2130 | 1329 (2)| 00:00:16 |
| 2 | HASH UNIQUE | | 1 | 300 | 1329 (2)| 00:00:16 |
| 3 | TABLE ACCESS BY INDEX ROWID| T06020DCA92 | 1 | 50 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 300 | 1328 (2)| 00:00:16 |
|* 5 | HASH JOIN | | 5 | 1250 | 1313 (2)| 00:00:16 |
|* 6 | TABLE ACCESS FULL | DCP1 | 1 | 205 | 111 (1)| 00:00:02 |
|* 7 | TABLE ACCESS FULL | T06020DCA91 | 21730 | 954K| 1201 (2)| 00:00:15 |
|* 8 | INDEX RANGE SCAN | PK_T06020DCA92 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DCCP1"."MTNO"="DCA91"."MTSID"||"DCA91"."COLORID"||"DCA91"."MISECTSCALESID
")
6 - filter("DCCP1"."CO"='3' AND "DCCP1"."TXEMP"='DSP')
7 - filter("DCA91"."CO"='3' AND "DCA91"."HMAT" LIKE 'G%')
8 - access("DCA92"."CO"='3' AND "DCA92"."FCTCLASS"="DCA91"."FCTCLASS" AND
"DCA92"."HMAT"="DCA91"."HMAT")
filter("DCA92"."HMAT" LIKE 'G%')
--insert into ...selelct ... 4306
PLAN_TABLE_OUTPUT
Plan hash value: 528790147
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 2130 | 1329 (2)| 00:00:16 |
| 1 | VIEW | | 1 | 2130 | 1329 (2)| 00:00:16 |
| 2 | SORT UNIQUE | | 1 | 300 | 1329 (2)| 00:00:16 |
| 3 | TABLE ACCESS BY INDEX ROWID| T06020DCA92 | 1 | 50 | 3 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 300 | 1328 (2)| 00:00:16 |
|* 5 | HASH JOIN | | 5 | 1250 | 1313 (2)| 00:00:16 |
|* 6 | TABLE ACCESS FULL | DCP1 | 1 | 205 | 111 (1)| 00:00:02 |
|* 7 | TABLE ACCESS FULL | T06020DCA91 | 21730 | 954K| 1201 (2)| 00:00:15 |
|* 8 | INDEX RANGE SCAN | PK_T06020DCA92 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DCCP1"."MTNO"="DCA91"."MTSID"||"DCA91"."COLORID"||"DCA91"."MISECTSCALESID
")
6 - filter("DCCP1"."CO"='3' AND "DCCP1"."TXEMP"='DSP')
7 - filter("DCA91"."CO"='3' AND "DCA91"."HMAT" LIKE 'G%')
8 - access("DCA92"."CO"='3' AND "DCA92"."FCTCLASS"="DCA91"."FCTCLASS" AND
"DCA92"."HMAT"="DCA91"."HMAT")
filter("DCA92"."HMAT" LIKE 'G%')
--2、插入和查询都是6笔,执行计划:
--insert into ...selelct ...6
PLAN_TABLE_OUTPUT
Plan hash value: 3005576431
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 3697 | 7690K| | 5094 (2)| 00:01:02 |
| 1 | VIEW | | 3697 | 7690K| | 5094 (2)| 00:01:02 |
| 2 | SORT UNIQUE | | 3697 | 1144K| 2968K| 5094 (2)| 00:01:02 |
|* 3 | HASH JOIN | | 3697 | 1144K| | 4840 (2)| 00:00:59 |
|* 4 | TABLE ACCESS FULL | DCP1 | 2 | 444 | | 110 (0)| 00:00:02 |
|* 5 | HASH JOIN | | 1774 | 164K| | 4729 (2)| 00:00:57 |
|* 6 | TABLE ACCESS FULL| T06020DCA91 | 21730 | 954K| | 1201 (2)| 00:00:15 |
|* 7 | TABLE ACCESS FULL| T06020DCA92 | 39012 | 1904K| | 3527 (1)| 00:00:43 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DCCP1"."CO"="DCA92"."CO" AND
"DCCP1"."MTNO"="DCA91"."MTSID"||"DCA91"."COLORID"||"DCA91"."MISECTSCALESID")
4 - filter("DCCP1"."CO"='3' AND "DCCP1"."TXEMP"='DSP')
5 - access("DCA92"."CO"="DCA91"."CO" AND "DCA92"."FCTCLASS"="DCA91"."FCTCLASS"
AND "DCA92"."HMAT"="DCA91"."HMAT")
6 - filter("DCA91"."CO"='3' AND "DCA91"."HMAT" LIKE 'G%')
7 - filter("DCA92"."CO"='3' AND "DCA92"."HMAT" LIKE 'G%')
--selelct ....
PLAN_TABLE_OUTPUT
Plan hash value: 2477468782
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3697 | 1144K| | 5094 (2)| 00:01:02 |
| 1 | HASH UNIQUE | | 3697 | 1144K| 2968K| 5094 (2)| 00:01:02 |
|* 2 | HASH JOIN | | 3697 | 1144K| | 4840 (2)| 00:00:59 |
|* 3 | TABLE ACCESS FULL | DCP1 | 2 | 444 | | 110 (0)| 00:00:02 |
|* 4 | HASH JOIN | | 1774 | 164K| | 4729 (2)| 00:00:57 |
|* 5 | TABLE ACCESS FULL| T06020DCA91 | 21730 | 954K| | 1201 (2)| 00:00:15 |
|* 6 | TABLE ACCESS FULL| T06020DCA92 | 39012 | 1904K| | 3527 (1)| 00:00:43 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DCCP1"."CO"="DCA92"."CO" AND
"DCCP1"."MTNO"="DCA91"."MTSID"||"DCA91"."COLORID"||"DCA91"."MISECTSCALESID")
3 - filter("DCCP1"."CO"='3' AND "DCCP1"."TXEMP"='DSP')
4 - access("DCA92"."CO"="DCA91"."CO" AND "DCA92"."FCTCLASS"="DCA91"."FCTCLASS"
AND "DCA92"."HMAT"="DCA91"."HMAT")
5 - filter("DCA91"."CO"='3' AND "DCA91"."HMAT" LIKE 'G%')
6 - filter("DCA92"."CO"='3' AND "DCA92"."HMAT" LIKE 'G%')