Oracle JAVA變數型別與欄位型別不一致，分割槽表全掃(INTERNAL_FUNCTION隱含轉換)的優化
開發反應[增量庫存]功能慢，並反饋查詢有指定分割槽條件，量較小;
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): ....篇幅問題，不在此顯示
【源於本人筆記】 若有書寫錯誤，表達錯誤，請指正...
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28602568/viewspace-2134772/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- (轉) JAVA,Mybatis,Oracle變數型別與欄位型別不一致，分割槽表全掃的優化JavaMyBatisOracle變數型別優化
- 保留兩位小數：資料庫欄位型別NUMBER，Java欄位型別Double型別資料庫型別Java
- 含LONG型別欄位的表無法MOVE型別
- 變數型別轉換變數型別
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- 欄位的資料型別隱式轉換有關係資料型別
- java型別轉換與強制型別轉換(轉)Java型別
- oracle的欄位型別Oracle型別
- 改變表中非空欄位的型別型別
- OCM實驗-建立含特殊欄位型別的表型別
- 修改表的欄位型別型別
- MySQL表的四種分割槽型別MySql型別
- JAVA基礎：Java變數型別間的相互轉換(轉)Java變數型別
- java- 型別-轉換：基本型別以及包裝型別的轉換Java型別
- Pytorch變數型別轉換PyTorch變數型別
- JAVA型別轉換怎麼變Java型別
- Mysql 的分割槽型別MySql型別
- Windows分割槽型別Windows型別
- C#3.0新特性――隱含型別區域性變數和匿名型別C#型別變數
- oracle 修改欄位型別的方法Oracle型別
- 【轉】修改表的欄位資料型別的方法資料型別
- JavaScript隱式型別轉換JavaScript型別
- MySQL 隱式型別轉換MySql型別
- Java 變數型別Java變數型別
- 引用型別變數的強制轉換型別變數
- Javascript基礎：變數型別轉換JavaScript變數型別
- 主流資料庫欄位型別轉.Net型別的方法資料庫型別
- 日期型別與String型別的轉換型別
- C#特性-匿名型別與隱式型別區域性變數C#型別變數
- Java資料型別及型別轉換Java資料型別
- Oracle Long型別轉換為Clob型別Oracle型別
- 從Java的型別轉換看MySQL和Oracle中的隱式轉換(二)Java型別MySqlOracle
- DATE型別INTERVAL分割槽型別
- 時間型分割槽欄位不走分割槽的解決
- dataguard型別轉換與模式轉化型別模式
- 轉載：Oracle常用的資料庫欄位型別Oracle資料庫型別
- lob欄位型別轉換ora-22858型別
- Java資料型別與資料庫欄位型別對應關係Java資料型別資料庫