Oracle JAVA变量类型与字段类型不一致，分区表全扫(INTERNAL_FUNCTION隐含转换)的优化
链接：http://blog.itpub.net/28602568/viewspace-2134772/
标题： Oracle JAVA变量类型与字段类型不一致，分区表全扫(NTERNAL_FUNCTION隐含转换)的优化
作者：ｌōττéｒｙ©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
开发反应[增量库存]功能慢，并反馈查询有指定分区条件，量较小;
1）通过 Oracle DBA常用sql分享 (正在执行的sql) 发现，该SQL的COST>1400000；
2）再通过select * from table(dbms_xplan.display_cursor( 'SQL_ID','0','advanced')); 分析该sql的执行计划和绑定变量情况发现
PARTITION RANGE ALL 分区全扫；---->createtm分区字段传参数了 为啥会全扫？
|
绑定变量中createtm分区条件未显示有传值
|
分区条件createtm 部分有【INTERNAL_FUNCTION 隐含转换函数】；---> 猜测 传参类型和DB字段类型不一致导致分区表全扫；
|
通过DBA_HIST_SQLBIND查到createtm分区条件传参(2017/01/17 14:20:00 -2017/01/17 14:25:00),字段类型为timestamp，而createtm字段为date类型；
总结：
开发在java层用的mybatis框架，把date类型字段转换成timestamp类型【and createtm $gt:=#{params.begintm} and createtm $lt:#{params.endtm}】，出现INTERNAL_FUNCTION 隐含转换,导致及时分区条件createtm传分区条件也被识别成未传变量，导致分区表全扫；
优化后 and createtm>= to_date(#{params.begintm},'yyyy-mm-dd hh24:mi:ss') and createtm< to_date(#{params.endtm},'yyyy-mm-dd hh24:mi:ss')
【增量库存】优化前SQL的详细信息
SQL> select * from table(dbms_xplan.display_cursor( 'd7a6vd69x8agf','0','advanced'));
--------------------------------------------------------------------------------------
SQL_ID d7a6vd69x8agf, child number 0
-------------------------------------
SQL文本：
select x1.locno , x1.item_code, x1.size_no , x1.brand_no , sum( qty ) qty
from ( select ccm.locno ,
SUBSTR (ccm.size_no , 3) size_no ,
it.item_code ,
it.brand_no ,
case when ccm.direction = '-1' then 0 - ccm.move_qty
else ccm.move_qty end qty
from (select locno,
cell_no ,
item_no ,
size_no ,
direction ,
move_qty ,
paper_no ,
paper_type
from usr_wms_city.con_content_move
where 1 = 1
and locno = :1
and CREATETM >= :2
and createtm < :3
and item_type = '0'
and quality = '0'
and PRE_FLAG = '0'
and paper_type != 'CN'
and (box_no is null or box_no = 'N' )) ccm
join (select c.locno, c.cell_no
FROM usr_wms_city.CM_DEFAREA cd
JOIN usr_wms_city.CM_DEFCELL c
on cd.locno = c.locno
and cd.ware_no = c.ware_no
and cd.area_no = c.area_no
and cd.ATTRIBUTE_TYPE = '0') bd
on bd.locno = ccm.locno
and bd.cell_no = ccm.cell_no
LEFT JOIN ( select t2.locno , t2.outstock_no
from usr_wms_city.bill_om_exp t1
join usr_wms_city.bill_om_outstock_dtl t2
on t1.locno = t2.locno
and t1.exp_no = t2.exp_no
where 1 = 1
and t1.locno = :4
and business_type = '20'
and t1.exp_date > sysdate - 31
group by t2.locno, t2.outstock_no ) boe
on ccm.locno = boe.locno
and ccm.paper_no = boe.outstock_no
and ccm.paper_type = 'HO'
join usr_wms_city.item it
on it.item_no = ccm.item_no
where 1 = 1
and it.sys_no =:5
and boe.outstock_no is null
union all
select t1.locno , t1.size_no, t1.item_code , t1.brand_no , t1.qty
from (select bct.locno,
SUBSTR (bctd.size_no , 3) size_no ,
it.item_code ,
it.brand_no ,
case when bct.use_type = 'A' then - qty else
qty end qty , case
when bct.use_type = 'A' then
bctd.d_cell_no
when bct.use_type = 'C' then
bctd.s_cell_no else '' end cell_no
from usr_wms_city.bill_container_task bct
join usr_wms_city.bill_container_task_dtl bctd
on bct.locno = bctd.locno
and bct.contask_no = bctd.contask_no
join usr_wms_city.item it
on it.item_no = bctd.item_no
where 1 = 1
and bct.locno =:6
and it.sys_no =:7
and bct.business_type = '0'
and bct.use_type in ( 'A' , 'C' )
and bct.status = '13'
and bctd.quality = '0'
and bctd.item_type = '0'
and audittm >= :8
and audittm < :9 ) t1
join (select c.locno, c.cell_no
FROM usr_wms_city.CM_DEFAREA cd
JOIN usr_wms_city.CM_DEFCELL c
on cd.locno = c.locno
and cd.ware_no = c.ware_no
and cd.area_no = c.area_no
and cd.ATTRIBUTE_TYPE = '0') bd
on bd.locno = t1.locno
and bd.cell_no = t1.cell_no) x1
group by x1.locno , x1.item_code, x1.size_no , x1.brand_no;
执行计划：
Plan hash value: 1935380104
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1410K(100)| | | |
| 1 | HASH GROUP BY | | 4 | 228 | 1410K (1)| 04:42:01 | | |
| 2 | VIEW | | 4 | 228 | 1410K (1)| 04:42:01 | | |
| 3 | UNION-ALL | | | | | | | |
|* 4 | FILTER | | | | | | | |
| 5 | NESTED LOOPS | | 3 | 705 | 1405K (1)| 04:41:03 | | |
| 6 | NESTED LOOPS | | 3 | 705 | 1405K (1)| 04:41:03 | | |
|* 7 | FILTER | | | | | | | |
|* 8 | HASH JOIN OUTER | | 3 | 588 | 1405K (1)| 04:41:03 | | |
|* 9 | HASH JOIN | | 3 | 396 | 1404K (1)| 04:40:49 | | |
| 10 | NESTED LOOPS | | 465 | 18135 | 108 (0)| 00:00:02 | | |
| 11 | NESTED LOOPS | | 465 | 18135 | 108 (0)| 00:00:02 | | |
|* 12 | TABLE ACCESS FULL | CM_DEFAREA | 27 | 405 | 27 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | IDX_CM_DEFCELL | 3 | | 2 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS BY INDEX ROWID | CM_DEFCELL | 17 | 408 | 3 (0)| 00:00:01 | | |
| 15 | PARTITION RANGE ALL | | 6622 | 601K| 1403K (1)| 04:40:47 | 1 |1048575|
|* 16 | TABLE ACCESS FULL | CON_CONTENT_MOVE | 6622 | 601K| 1403K (1)| 04:40:47 | 1 |1048575|
| 17 | VIEW | | 589 | 37696 | 1206 (1)| 00:00:15 | | |
| 18 | HASH GROUP BY | | 589 | 42408 | 1206 (1)| 00:00:15 | | |
|* 19 | FILTER | | | | | | | |
| 20 | NESTED LOOPS | | 589 | 42408 | 1205 (1)| 00:00:15 | | |
|* 21 | TABLE ACCESS BY INDEX ROWID | BILL_OM_EXP | 193 | 6948 | 46 (0)| 00:00:01 | | |
|* 22 | INDEX RANGE SCAN | IDX1_BILL_OM_EXP | 36 | | 25 (0)| 00:00:01 | | |
|* 23 | INDEX RANGE SCAN | INDXBILL_OM_OUTSTOCK_DTL_MID_1 | 3 | 108 | 6 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | SYS_C0014916 | 1 | | 1 (0)| 00:00:01 | | |
|* 25 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 39 | 2 (0)| 00:00:01 | | |
|* 26 | FILTER | | | | | | | |
| 27 | NESTED LOOPS | | 1 | 184 | 4809 (1)| 00:00:58 | | |
| 28 | NESTED LOOPS | | 1 | 184 | 4809 (1)| 00:00:58 | | |
| 29 | NESTED LOOPS | | 1 | 145 | 4807 (1)| 00:00:58 | | |
|* 30 | HASH JOIN | | 1 | 130 | 4806 (1)| 00:00:58 | | |
| 31 | NESTED LOOPS | | 163 | 17278 | 4554 (1)| 00:00:55 | | |
| 32 | NESTED LOOPS | | 168 | 17278 | 4554 (1)| 00:00:55 | | |
|* 33 | TABLE ACCESS FULL | BILL_CONTAINER_TASK | 28 | 1120 | 4442 (1)| 00:00:54 | | |
|* 34 | INDEX RANGE SCAN | PK_BILL_CONTAINER_TASK_DTL_MID | 6 | | 3 (0)| 00:00:01 | | |
|* 35 | TABLE ACCESS BY GLOBAL INDEX ROWID| BILL_CONTAINER_TASK_DTL | 6 | 396 | 4 (0)| 00:00:01 | ROWID | ROWID |
| 36 | TABLE ACCESS BY INDEX ROWID | CM_DEFCELL | 8071 | 189K| 252 (0)| 00:00:04 | | |
|* 37 | INDEX RANGE SCAN | IDX_CM_DEFCELL | 8071 | | 48 (0)| 00:00:01 | | |
|* 38 | TABLE ACCESS BY INDEX ROWID | CM_DEFAREA | 1 | 15 | 1 (0)| 00:00:01 | | |
|* 39 | INDEX UNIQUE SCAN | M1_PK_CM_DEFAREA | 1 | | 0 (0)| | | |
|* 40 | INDEX UNIQUE SCAN | SYS_C0014916 | 1 | | 1 (0)| 00:00:01 | | |
|* 41 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 39 | 2 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id): ...篇幅问题不在此显示
OutlineData /*固定hint*/...篇幅问题不在此显示
绑定变量传值：
Peeked Binds (identified by position):
---------------------------------------------------
1 - : 1 (VARCHAR2 ( 30), CSID =873 ): 'K7551'
4 - : 4 (VARCHAR2 ( 30), CSID =873 ): 'K7551'
5 - : 5 (VARCHAR2 ( 30), CSID =873 ): 'TM'
6 - : 6 (VARCHAR2 ( 30), CSID =873 ): 'K7551'
7 - : 7 (VARCHAR2 ( 30), CSID =873 ): 'TM'
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(:3>:2)
7 - filter("BOE"."OUTSTOCK_NO" IS NULL)
8 - access("PAPER_NO"="BOE"."OUTSTOCK_NO" AND "LOCNO"="BOE"."LOCNO" AND "PAPER_TYPE"=CASE WHEN ("BOE"."LOCNO" IS NOT NULL) THEN
'HO' ELSE 'HO' END )
9 - access("C"."LOCNO"="LOCNO" AND "C"."CELL_NO"="CELL_NO")
12 - filter(("CD"."LOCNO"=:1 AND "CD"."ATTRIBUTE_TYPE"='0'))
13 - access("C"."LOCNO"=:1 AND "CD"."WARE_NO"="C"."WARE_NO" AND "CD"."AREA_NO"="C"."AREA_NO")
16 - filter(("LOCNO"=:1 AND ("BOX_NO" IS NULL OR "BOX_NO"='N') AND "PRE_FLAG"='0' AND INTERNAL_FUNCTION("CREATETM")>=:2 AND
INTERNAL_FUNCTION("CREATETM")<:3 AND "PAPER_TYPE"<>'CN' AND "QUALITY"='0' AND "ITEM_TYPE"='0'))
19 - filter(:4=:1)
21 - filter(("T1"."BUSINESS_TYPE"='20' AND "T1"."EXP_DATE">SYSDATE@!-31))
22 - access("T1"."LOCNO"=:1)
filter("T1"."LOCNO"=:4)
23 - access("T1"."EXP_NO"="T2"."EXP_NO" AND "T2"."LOCNO"=:4)
filter(("T2"."LOCNO"=:1 AND "T2"."LOCNO"=:4))
24 - access("IT"."ITEM_NO"="ITEM_NO")
25 - filter("IT"."SYS_NO"=:5)
26 - filter(:9>:8)
30 - access("C"."LOCNO"="BCT"."LOCNO" AND "C"."CELL_NO"=CASE "BCT"."USE_TYPE" WHEN 'A' THEN "BCTD"."D_CELL_NO" WHEN 'C' THEN
"BCTD"."S_CELL_NO" ELSE '' END )
33 - filter(("BCT"."LOCNO"=:6 AND "BCT"."BUSINESS_TYPE"='0' AND INTERNAL_FUNCTION("BCT"."USE_TYPE") AND
INTERNAL_FUNCTION("BCT"."AUDITTM")>=:8 AND INTERNAL_FUNCTION("BCT"."AUDITTM")<:9 AND "BCT"."STATUS"='13'))
34 - access("BCTD"."LOCNO"=:6 AND "BCT"."CONTASK_NO"="BCTD"."CONTASK_NO")
35 - filter(("BCTD"."ITEM_TYPE"='0' AND "BCTD"."QUALITY"='0'))
37 - access("C"."LOCNO"=:6)
38 - filter("CD"."ATTRIBUTE_TYPE"='0')
39 - access("CD"."AREA_NO"="C"."AREA_NO" AND "CD"."LOCNO"=:6 AND "CD"."WARE_NO"="C"."WARE_NO")
40 - access("IT"."ITEM_NO"="BCTD"."ITEM_NO")
41 - filter("IT"."SYS_NO"=:7)
Column Projection Information (identified by operation id): ...篇幅问题不在此显示
实际传的绑定变量如下：
SQL> SELECT * FROM (
2 select sql_id,
3 name,
4 datatype_string,
5 case datatype
6 when 180 then --TIMESTAMP
7 to_char(ANYDATA.accesstimestamp(t.value_anydata),
8 'YYYY/MM/DD HH24:MI:SS')
9 else
10 t.value_string
11 end as bind_value,
12 last_captured
13 from DBA_HIST_SQLBIND t
14 where sql_id = 'd7a6vd69x8agf' order by last_captured desc) where rownum<10 order by name;
SQL_ID NAME DATATYPE_STRING BIND_VALUE LAST_CAPTURED
------------- ---- --------------- ---------------------- -------------
d7a6vd69x8agf :1 VARCHAR2(32) K7551 17-1月-17 14:24:33
d7a6vd69x8agf :2 TIMESTAMP 2017/01/17 14:20:00 17-1月-17 14:24:33
d7a6vd69x8agf :3 TIMESTAMP 2017/01/17 14:25:00 17-1月-17 14:24:33
d7a6vd69x8agf :4 VARCHAR2(32) K7551 17-1月-17 14:24:33
d7a6vd69x8agf :5 VARCHAR2(32) SD 17-1月-17 14:24:33
d7a6vd69x8agf :6 VARCHAR2(32) K7551 17-1月-17 14:24:33
d7a6vd69x8agf :7 VARCHAR2(32) SD 17-1月-17 14:24:33
d7a6vd69x8agf :8 TIMESTAMP 2017/01/17 14:20:00 17-1月-17 14:24:33
d7a6vd69x8agf :9 TIMESTAMP 2017/01/17 14:25:00 17-1月-17 14:24:33
9 rows selected
SQL>DB 字段类型 select table_name,column_name,data_type from user_tab_columns t where table_name=upper('CON_CONTENT_MOVE') and column_name='CREATETM';
TABLE_NAME COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------ -----------
CON_CONTENT_MOVE CREATETM DATE
SQL>
优化后的执行计划介绍：
select * from table( dbms_xplan.display_cursor( '1cz9naf22d7jw','0' ,'advanced'));
SQL_ID 1cz9naf22d7jw, child number 0
-------------------------------------
Plan hash value: 773930837
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21826 (100)| | | |
| 1 | HASH GROUP BY | | 2 | 114 | 21826 (1)| 00:04:22 | | |
| 2 | VIEW | | 2 | 114 | 21825 (1)| 00:04:22 | | |
| 3 | UNION-ALL | | | | | | | |
|* 4 | FILTER | | | | | | | |
| 5 | NESTED LOOPS | | 1 | 236 | 17374 (1)| 00:03:29 | | |
| 6 | NESTED LOOPS | | 1 | 236 | 17374 (1)| 00:03:29 | | |
| 7 | NESTED LOOPS | | 1 | 197 | 17372 (1)| 00:03:29 | | |
| 8 | NESTED LOOPS | | 1 | 182 | 17371 (1)| 00:03:29 | | |
|* 9 | FILTER | | | | | | | |
|* 10 | HASH JOIN OUTER | | 1 | 158 | 17369 (1)| 00:03:29 | | |
|* 11 | TABLE ACCESS BY GLOBAL INDEX ROWID | CON_CONTENT_MOVE | 1 | 94 | 8 (0)| 00:00:01 | ROWID | ROWID |
|* 12 | INDEX RANGE SCAN | INDXCON_CONTENT_MOVE_MID_21 | 37 | | 4 (0)| 00:00:01 | | |
| 13 | VIEW | | 609 | 38976 | 17361 (1)| 00:03:29 | | |
| 14 | HASH GROUP BY | | 609 | 43848 | 17361 (1)| 00:03:29 | | |
|* 15 | FILTER | | | | | | | |
|* 16 | HASH JOIN | | 609 | 43848 | 17360 (1)| 00:03:29 | | |
|* 17 | TABLE ACCESS BY INDEX ROWID | BILL_OM_EXP | 211 | 7596 | 38 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | IDX1_BILL_OM_EXP | 45 | | 12 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS BY GLOBAL INDEX ROWID| BILL_OM_OUTSTOCK_DTL | 927K| 31M| 17320 (1)| 00:03:28 | ROWID | ROWID |
|* 20 | INDEX RANGE SCAN | INDXBILL_OM_OUTSTOCK_DTL_MID_9 | 26390 | | 6350 (1)| 00:01:17 | | |
| 21 | TABLE ACCESS BY INDEX ROWID | CM_DEFCELL | 1 | 24 | 2 (0)| 00:00:01 | | |
|* 22 | INDEX UNIQUE SCAN | M1_PK_CM_DEFCELL | 1 | | 1 (0)| 00:00:01 | | |
|* 23 | TABLE ACCESS BY INDEX ROWID | CM_DEFAREA | 1 | 15 | 1 (0)| 00:00:01 | | |
|* 24 | INDEX UNIQUE SCAN | M1_PK_CM_DEFAREA | 1 | | 0 (0)| | | |
|* 25 | INDEX UNIQUE SCAN | SYS_C0014916 | 1 | | 1 (0)| 00:00:01 | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 39 | 2 (0)| 00:00:01 | | |
|* 27 | FILTER | | | | | | | |
| 28 | NESTED LOOPS | | 1 | 184 | 4451 (1)| 00:00:54 | | |
| 29 | NESTED LOOPS | | 1 | 184 | 4451 (1)| 00:00:54 | | |
| 30 | NESTED LOOPS | | 1 | 145 | 4449 (1)| 00:00:54 | | |
| 31 | NESTED LOOPS | | 1 | 130 | 4448 (1)| 00:00:54 | | |
| 32 | NESTED LOOPS | | 1 | 106 | 4446 (1)| 00:00:54 | | |
|* 33 | TABLE ACCESS FULL | BILL_CONTAINER_TASK | 1 | 40 | 4442 (1)| 00:00:54 | | |
|* 34 | TABLE ACCESS BY GLOBAL INDEX ROWID | BILL_CONTAINER_TASK_DTL | 6 | 396 | 4 (0)| 00:00:01 | ROWID | ROWID |
|* 35 | INDEX RANGE SCAN | PK_BILL_CONTAINER_TASK_DTL_MID | 6 | | 3 (0)| 00:00:01 | | |
| 36 | TABLE ACCESS BY INDEX ROWID | CM_DEFCELL | 1 | 24 | 2 (0)| 00:00:01 | | |
|* 37 | INDEX UNIQUE SCAN | M1_PK_CM_DEFCELL | 1 | | 1 (0)| 00:00:01 | | |
|* 38 | TABLE ACCESS BY INDEX ROWID | CM_DEFAREA | 1 | 15 | 1 (0)| 00:00:01 | | |
|* 39 | INDEX UNIQUE SCAN | M1_PK_CM_DEFAREA | 1 | | 0 (0)| | | |
|* 40 | INDEX UNIQUE SCAN | SYS_C0014916 | 1 | | 1 (0)| 00:00:01 | | |
|* 41 | TABLE ACCESS BY INDEX ROWID | ITEM | 1 | 39 | 2 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id): ...篇幅问题不在此显示
Outline Data ...篇幅问题不在此显示
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=873): 'K7551'
2 - :2 (VARCHAR2(30), CSID=873): '2017-01-18 23:45:32'
3 - :3 (VARCHAR2(30), CSID=873): '2017-01-20 15:12:35'
4 - :4 (VARCHAR2(30), CSID=873): 'K7551'
5 - :5 (VARCHAR2(30), CSID=873): 'SD'
6 - :6 (VARCHAR2(30), CSID=873): 'BL'
7 - :7 (VARCHAR2(30), CSID=873): 'TM'
8 - :8 (VARCHAR2(30), CSID=873): 'TT'
9 - :9 (VARCHAR2(30), CSID=873): 'BS'
10 - :10 (VARCHAR2(30), CSID=873): 'K7551'
11 - :11 (VARCHAR2(30), CSID=873): 'SD'
12 - :12 (VARCHAR2(30), CSID=873): 'BL'
13 - :13 (VARCHAR2(30), CSID=873): 'TM'
14 - :14 (VARCHAR2(30), CSID=873): 'TT'
15 - :15 (VARCHAR2(30), CSID=873): 'BS'
16 - :16 (VARCHAR2(30), CSID=873): '2017-01-18 23:45:32'
17 - :17 (VARCHAR2(30), CSID=873): '2017-01-20 15:12:35'
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(TO_DATE(:3,'yyyy-mm-dd hh24:mi:ss')>TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss'))
9 - filter("BOE"."OUTSTOCK_NO" IS NULL)
10 - access("PAPER_NO"="BOE"."OUTSTOCK_NO" AND "LOCNO"="BOE"."LOCNO" AND "PAPER_TYPE"=CASE WHEN ("BOE"."LOCNO" IS NOT NULL) THEN 'HO'
ELSE 'HO' END )
11 - filter(("LOCNO"=:1 AND ("BOX_NO" IS NULL OR "BOX_NO"='N') AND "PRE_FLAG"='0' AND "ITEM_TYPE"='0' AND "QUALITY"='0' AND
"PAPER_TYPE"<>'CN'))
12 - access("CREATETM">=TO_DATE(:2,'yyyy-mm-dd hh24:mi:ss') AND "CREATETM"<to_date(:3,'yyyy-mm-dd hh24:mi:ss'))
15 - filter(:4=:1)
16 - access("T1"."LOCNO"="T2"."LOCNO" AND "T1"."EXP_NO"="T2"."EXP_NO")
17 - filter(("T1"."BUSINESS_TYPE"='20' AND "T1"."EXP_DATE">SYSDATE@!-31))
18 - access("T1"."LOCNO"=:1)
filter("T1"."LOCNO"=:4)
20 - access("T2"."LOCNO"=:4)
filter("T2"."LOCNO"=:1)
22 - access("C"."CELL_NO"="CELL_NO" AND "C"."LOCNO"=:1)
23 - filter("CD"."ATTRIBUTE_TYPE"='0')
24 - access("CD"."AREA_NO"="C"."AREA_NO" AND "CD"."LOCNO"=:1 AND "CD"."WARE_NO"="C"."WARE_NO")
25 - access("IT"."ITEM_NO"="ITEM_NO")
26 - filter(("IT"."SYS_NO"=:5 OR "IT"."SYS_NO"=:6 OR "IT"."SYS_NO"=:7 OR "IT"."SYS_NO"=:8 OR "IT"."SYS_NO"=:9))
27 - filter(TO_DATE(:17,'yyyy-mm-dd hh24:mi:ss')>TO_DATE(:16,'yyyy-mm-dd hh24:mi:ss'))
33 - filter(("BCT"."LOCNO"=:10 AND "BCT"."BUSINESS_TYPE"='0' AND INTERNAL_FUNCTION("BCT"."USE_TYPE") AND
"BCT"."AUDITTM">=TO_DATE(:16,'yyyy-mm-dd hh24:mi:ss') AND "BCT"."STATUS"='13' AND "BCT"."AUDITTM"<to_date(:17,'yyyy-mm-dd hh24:mi:ss')))
34 - filter(("BCTD"."ITEM_TYPE"='0' AND "BCTD"."QUALITY"='0'))
35 - access("BCTD"."LOCNO"=:10 AND "BCT"."CONTASK_NO"="BCTD"."CONTASK_NO")
37 - access("C"."CELL_NO"=CASE "BCT"."USE_TYPE" WHEN 'A' THEN "BCTD"."D_CELL_NO" WHEN 'C' THEN "BCTD"."S_CELL_NO" ELSE '' END AND
"C"."LOCNO"=:10)
38 - filter("CD"."ATTRIBUTE_TYPE"='0')
39 - access("CD"."AREA_NO"="C"."AREA_NO" AND "CD"."LOCNO"=:10 AND "CD"."WARE_NO"="C"."WARE_NO")
40 - access("IT"."ITEM_NO"="BCTD"."ITEM_NO")
41 - filter(("IT"."SYS_NO"=:11 OR "IT"."SYS_NO"=:12 OR "IT"."SYS_NO"=:13 OR "IT"."SYS_NO"=:14 OR "IT"."SYS_NO"=:15))
Column Projection Information (identified by operation id): ....篇幅问题，不在此显示
【源于本人笔记】 若有书写错误，表达错误，请指正...
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
