SQL> select * from ( select row_.*, rownum rownum_ from ( select i.itemname, i.ITEMDESC,
3 i.LOTNUM,
5 case when o.requestflag = 'Y' then '物资申领' else (select dd.dictionaryname from datadictionary dd
6 where dd.datadictionarytypecode='binbin1' and dd.dictionarycode=o.ordertype) end as ORDERTYPENAME,
7
8 o.binbin1id ORDERID, i.QUANTITY,
(select b.buildingprojectid from buildingprojectorder b where b.projectsendid=o.binbin1id) buildingprojectid,
12 (select item.quantity from instoretransactionitem isto, itemstorage item where isto.instoreitemid =
13 item.instoreitemid and isto.binbin1itemid =i.binbin1itemid) NEWQUANTITY ,
14
15 i.PRICE,
16
17 i.quantity * i.price summoney,
18
19 nvl((select mu.description from misunitsmeasure mu where mu.uomcode = i.unit), i.unit) unit, i.NUMATTR4,
20
21 Trunc((SYSDATE-o.LASTMODIFYTIME), 0) INSTOREAGE,
22
23 to_char(o.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') AS INTIME,
24
25 I.POLINEID ,
26 27 i.POLINEDESC,
28
29 (select p.projectcode from project p where p.projectid = o.toprojectid) as projectcode,
30 (select p.name from project p where p.projectid = o.toprojectid) as projectname,
31 (select '['||p.mistaskcode||']'||p.mistaskname from task p where p.mistaskid = i.tomistaskid) as taskname, O.TEXTATTR3 OWNERDEPARTMENT,
32 --(select w.housename from warehouse w where w.houseid = i.Fromhouseid) houseid,
33 --(select a.areaname from storearea a where a.areaid = i.Fromareaid) areaid,
34 --(select p.positionname from storeposition p where p.positionid = i.Frompositionid) positionid
35 (select ci.constructionname from constructioninfo ci where ci.constructionid=o.textattr15 and rownum=1) constrCode,
(SELECT EW.ENTITYWAREHOUSENAME FROM ENTITYWAREHOUSE EW WHERE EW.ENTITYWAREHOUSEID=O.TEXTATTR13)ENTITYWAREHOUSENAME,
O.PROJECTPRINCIPAL
41 from binbin1item i, binbin1 o where i.binbin1id=o.binbin1id
42 -- and o.requestflag = 'Y'
44 and i.quantity>0 and i.isintask='N' and o.status='F' and i.status='F' and o.fromprojectid is not null and o.textattr15
45 in (select t.constructionid from ConstructionInfo t where t.constructionid <>'-1')
47 and i.tomistaskid in (select t.mistaskid from task t where t.mistaskcode like '%' || upper( 100) || '%')
49 order by o.createtime desc ) row_ where rownum <= 20) where rownum_ > 1;
19 rows selected.
Elapsed: 00:05:14.55
Execution Plan
----------------------------------------------------------
Plan hash value: 2714434513
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 36700 | 18075 (4)| 00:03:37 |
| 1 | TABLE ACCESS BY INDEX ROWID | DATADICTIONARY | 1 | 35 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DATADICTIONARY | 1 | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | BUILDINGPROJECTORDER | 1 | 41 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | RELATIONSHIP_319_FK | 1 | | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | ITEMSTORAGE | 1 | 12 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 28 | 7 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | INSTORETRANSACTIONITEM | 1 | 16 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | RELATIONSHIP_162_FK | 1 | | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | RELATIONSHIP_76_FK | 1 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | MISUNITSMEASURE | 1 | 8 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_MISUNITSMEASURE | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PROJECT | 1 | 15 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_PROJECT | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PROJECT | 1 | 31 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_PROJECT | 1 | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | TASK | 1 | 29 | 3 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_TASK | 1 | | 2 (0)| 00:00:01 |
|* 18 | COUNT STOPKEY | | | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | CONSTRUCTIONINFO | 2 | 76 | 3 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_CONSTRUCTIONID | 8 | | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | ENTITYWAREHOUSE | 1 | 25 | 2 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | ENTITYWAREHOUSEID_PK | 1 | | 1 (0)| 00:00:01 |
|* 23 | VIEW | | 20 | 36700 | 18075 (4)| 00:03:37 |
|* 24 | COUNT STOPKEY | | | | | |
| 25 | VIEW | | 448 | 797K| 18075 (4)| 00:03:37 |
|* 26 | SORT ORDER BY STOPKEY | | 448 | 105K| 18075 (4)| 00:03:37 |
| 27 | NESTED LOOPS | | 448 | 105K| 18074 (4)| 00:03:37 |
| 28 | NESTED LOOPS | | 1140 | 250K| 15790 (4)| 00:03:10 |
|* 29 | HASH JOIN | | 698 | 62122 | 12577 (5)| 00:02:31 |
| 30 | SORT UNIQUE | | 11387 | 122K| 13 (0)| 00:00:01 |
|* 31 | INDEX FAST FULL SCAN | PK_CONSTRUCTIONID | 11387 | 122K| 13 (0)| 00:00:01 |
|* 32 | TABLE ACCESS FULL | binbin1 | 6358 | 484K| 12561 (5)| 00:02:31 |
|* 33 | TABLE ACCESS BY INDEX ROWID| binbin1ITEM | 2 | 272 | 7 (0)| 00:00:01 |
|* 34 | INDEX RANGE SCAN | RELATIONSHIP_38_FK | 25 | | 3 (0)| 00:00:01 |
|* 35 | TABLE ACCESS BY INDEX ROWID | TASK | 1 | 15 | 2 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | PK_TASK | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DD"."DATADICTIONARYTYPECODE"='binbin1' AND "DD"."DICTIONARYCODE"=:B1)
4 - access("B"."PROJECTSENDID"=:B1)
8 - access("ISTO"."binbin1ITEMID"=:B1)
9 - access("ITEM"."INSTOREITEMID"="ISTO"."INSTOREITEMID")
11 - access("MU"."UOMCODE"=:B1)
13 - access("P"."PROJECTID"=:B1)
15 - access("P"."PROJECTID"=:B1)
17 - access("P"."MISTASKID"=:B1)
18 - filter(ROWNUM=1)
20 - access("CI"."CONSTRUCTIONID"=:B1)
22 - access("EW"."ENTITYWAREHOUSEID"=:B1)
23 - filter("ROWNUM_">1)
24 - filter(ROWNUM<=20)
26 - filter(ROWNUM<=20)
29 - access("O"."TEXTATTR15"="T"."CONSTRUCTIONID")
31 - filter("T"."CONSTRUCTIONID"<>'-1')
32 - filter("O"."FROMPROJECTID" IS NOT NULL AND "O"."STATUS"='F' AND "O"."TEXTATTR15"<>'-1')
33 - filter("I"."TOMISTASKID" IS NOT NULL AND "I"."STATUS"='F' AND "I"."ISINTASK"='N' AND
"I"."QUANTITY">0)
34 - access("I"."binbin1ID"="O"."binbin1ID")
35 - filter("T"."MISTASKCODE" LIKE '%100%')
36 - access("I"."TOMISTASKID"="T"."MISTASKID")
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
5852133 consistent gets
11446 physical reads
0 redo size
4600 bytes sent via SQL*Net to client
1357 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
19 rows processed
原始sql和执行计划如上，从原始sql可以看出，此sql为分页语句，将”binbin1item i, binbin1 o where i.binbin1id=o.binbin1id“连接顺序更改为”binbin1 o,binbin1item i where o.binbin1id=i.binbin1id“ 。并将”where t.constructionid <>'-1'“，注释掉，因为不等于会导致走不了索引。经检查注释掉”where t.constructionid <>'-1'“后不影响业务。
可以看出返回的行为空行。
改写后sql如下：
SQL> select * from ( select row_.*, rownum rownum_ from ( select i.itemname, i.ITEMDESC,
3 i.LOTNUM,
5 case when o.requestflag = 'Y' then '物资申领' else (select dd.dictionaryname from datadictionary dd
6 where dd.datadictionarytypecode='binbin1' and dd.dictionarycode=o.ordertype) end as ORDERTYPENAME,
8 o.binbin1id ORDERID, i.QUANTITY,
10 (select b.buildingprojectid from buildingprojectorder b where b.projectsendid=o.binbin1id) buildingprojectid,
12 (select item.quantity from instoretransactionitem isto, itemstorage item where isto.instoreitemid =
13 item.instoreitemid and isto.binbin1itemid =i.binbin1itemid) NEWQUANTITY ,
15 i.PRICE,
17 i.quantity * i.price summoney,
19 nvl((select mu.description from misunitsmeasure mu where mu.uomcode = i.unit), i.unit) unit, i.NUMATTR4,
21 Trunc((SYSDATE-o.LASTMODIFYTIME), 0) INSTOREAGE,
23 to_char(o.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') AS INTIME,
25 I.POLINEID ,
27 i.POLINEDESC,
29 (select p.projectcode from project p where p.projectid = o.toprojectid) as projectcode,
30 (select p.name from project p where p.projectid = o.toprojectid) as projectname,
31 (select '['||p.mistaskcode||']'||p.mistaskname from task p where p.mistaskid = i.tomistaskid) as taskname, O.TEXTATTR3 OWNERDEPARTMENT,
32 --(select w.housename from warehouse w where w.houseid = i.Fromhouseid) houseid,
33 --(select a.areaname from storearea a where a.areaid = i.Fromareaid) areaid,
34 --(select p.positionname from storeposition p where p.positionid = i.Frompositionid) positionid
35 (select ci.constructionname from constructioninfo ci where ci.constructionid=o.textattr15 and rownum=1) constrCode,
37 (SELECT EW.ENTITYWAREHOUSENAME FROM ENTITYWAREHOUSE EW WHERE EW.ENTITYWAREHOUSEID=O.TEXTATTR13)ENTITYWAREHOUSENAME,
39 O.PROJECTPRINCIPAL
41 from binbin1 o,binbin1item i where o.binbin1id=i.binbin1id
42 -- and o.requestflag = 'Y'
44 and i.quantity>0 and i.isintask='N' and o.status='F' and i.status='F' and o.fromprojectid is not null and o.textattr15
45 in (select t.constructionid from ConstructionInfo t /*where t.constructionid <>'-1'*/)
47 and i.tomistaskid in (select t.mistaskid from task t where t.mistaskcode like '%' || upper( 100) || '%')
49 order by o.createtime desc ) row_ where rownum <= 20) where rownum_ > 1;
19 rows selected.
Elapsed: 00:00:00.51
Execution Plan
----------------------------------------------------------
Plan hash value: 1183747735
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 36700 | 13019 (1)| 00:02:37 |
| 1 | TABLE ACCESS BY INDEX ROWID | DATADICTIONARY | 1 | 35 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DATADICTIONARY | 1 | | 0 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | BUILDINGPROJECTORDER | 1 | 41 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | RELATIONSHIP_319_FK | 1 | | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | ITEMSTORAGE | 1 | 12 | 3 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 28 | 7 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | INSTORETRANSACTIONITEM | 1 | 16 | 4 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | RELATIONSHIP_162_FK | 1 | | 3 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | RELATIONSHIP_76_FK | 1 | | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | MISUNITSMEASURE | 1 | 8 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_MISUNITSMEASURE | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PROJECT | 1 | 15 | 2 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PK_PROJECT | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PROJECT | 1 | 31 | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_PROJECT | 1 | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | TASK | 1 | 29 | 3 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_TASK | 1 | | 2 (0)| 00:00:01 |
|* 18 | COUNT STOPKEY | | | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | CONSTRUCTIONINFO | 2 | 76 | 3 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | PK_CONSTRUCTIONID | 8 | | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | ENTITYWAREHOUSE | 1 | 25 | 2 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | ENTITYWAREHOUSEID_PK | 1 | | 1 (0)| 00:00:01 |
|* 23 | VIEW | | 20 | 36700 | 13019 (1)| 00:02:37 |
|* 24 | COUNT STOPKEY | | | | | |
| 25 | VIEW | | 20 | 36440 | 13019 (1)| 00:02:37 |
| 26 | NESTED LOOPS | | 20 | 4800 | 13019 (1)| 00:02:37 |
| 27 | NESTED LOOPS SEMI | | 51 | 11475 | 12917 (1)| 00:02:36 |
| 28 | NESTED LOOPS | | 131 | 28034 | 12786 (1)| 00:02:34 |
|* 29 | TABLE ACCESS BY INDEX ROWID| binbin1 | 6364 | 484K| 12418 (1)| 00:02:30 |
| 30 | INDEX FULL SCAN | INDEX_CREATETIME_DESC | 21050 | | 117 (1)| 00:00:02 |
|* 31 | TABLE ACCESS BY INDEX ROWID| binbin1ITEM | 2 | 272 | 7 (0)| 00:00:01 |
|* 32 | INDEX RANGE SCAN | RELATIONSHIP_38_FK | 25 | | 3 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | PK_CONSTRUCTIONID | 4485 | 49335 | 1 (0)| 00:00:01 |
|* 34 | TABLE ACCESS BY INDEX ROWID | TASK | 1 | 15 | 2 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | PK_TASK | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DD"."DATADICTIONARYTYPECODE"='binbin1' AND "DD"."DICTIONARYCODE"=:B1)
4 - access("B"."PROJECTSENDID"=:B1)
8 - access("ISTO"."binbin1ITEMID"=:B1)
9 - access("ITEM"."INSTOREITEMID"="ISTO"."INSTOREITEMID")
11 - access("MU"."UOMCODE"=:B1)
13 - access("P"."PROJECTID"=:B1)
15 - access("P"."PROJECTID"=:B1)
17 - access("P"."MISTASKID"=:B1)
18 - filter(ROWNUM=1)
20 - access("CI"."CONSTRUCTIONID"=:B1)
22 - access("EW"."ENTITYWAREHOUSEID"=:B1)
23 - filter("ROWNUM_">1)
24 - filter(ROWNUM<=20)
29 - filter("O"."TEXTATTR15" IS NOT NULL AND "O"."FROMPROJECTID" IS NOT NULL AND "O"."STATUS"='F')
31 - filter("I"."TOMISTASKID" IS NOT NULL AND "I"."STATUS"='F' AND "I"."ISINTASK"='N' AND
"I"."QUANTITY">0)
32 - access("O"."binbin1ID"="I"."binbin1ID")
33 - access("O"."TEXTATTR15"="T"."CONSTRUCTIONID")
34 - filter("T"."MISTASKCODE" LIKE '%100%')
35 - access("I"."TOMISTASKID"="T"."MISTASKID")
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
73017 consistent gets
21 physical reads
204 redo size
4562 bytes sent via SQL*Net to client
1368 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
19 rows processed
改写之后执行时间从五分钟降到0.5秒。但是还不是最优的，还需在binbin1.createtime建如下索引。
create index ind_createtime_desc. on binbin1(createtime desc);
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
