oracle 执行计划 u,执行计划的理解，请大家根据执行计划，找出SQL语句中对应的关系...

最新推荐文章于 2021-04-13 02:00:19 发布

weixin_39615808

最新推荐文章于 2021-04-13 02:00:19 发布

阅读量176

点赞数

文章标签： oracle 执行计划 u

ORACLE 11203

下面的语句，我仔细浏览了一遍，发现，SQL语句的部分约束关系，没在执行计划中体现，也就是，计划里没执行某些约束，如暗红色部分的表，执行计划中并未体现它们的存在，

请大家把执行计划中的操作，和SQL 语句中的关联要求，一一匹配起来，

另外，索引，CPK_OPE_WAYBILLBP7 和 UQ_CT_OPE_WAYBILL_IT_ZX 都是 ct_ope_waybill 表。

SQL>SELECT distinct apfeeentry.fid AS fid, expenseitem.fnumber AS itemnumber, apfeeentry.cfprice AS 单价, apfeeentry.cfamount AS 金额,

jmscode.cfhandoverid AS 交接单id, jmscode.cffeetype as 费用类型 , trader.fid as apFeeID, wa.fnumber as 运单

FROM ct_fin_joiningtraderapfeeentry apfeeentry

INNER JOIN ct_fin_joiningtraderapfee trader ON trader.fid = apfeeentry.fparentid

INNER JOIN ct_fin_jmshandoverrecode jmscode ON jmscode.cfhandoverid = trader.fsourcebillid

left join ct_ope_waybill wa on wa.fid = trader.cfwaybillid

LEFT JOIN t_scm_expenseitem expenseitem ON expenseitem.fid = apfeeentry.cfexpenseitemid

where (trader.cfhandtype = '2' OR trader.cfhandtype = '4' OR trader.cfhandtype = '7')

AND jmscode.fid IN ( SELECT jmscode.fid AS jmscodeid

FROM ct_fin_jmshandoverrecode jmscode

LEFT JOIN ct_ope_waybill wa ON wa.fnumber = jmscode.cfwaybillnumber

LEFT JOIN ct_ope_handover handover ON handover.fid = jmscode.cfhandoverid

LEFT JOIN ct_fin_joiningtrader joiningtrader ON jmscode.cfjoiningtraderid = joiningtrader.fid

LEFT JOIN ct_bas_transportationtype transportation ON transportation.fid = wa.cftransporttypeid

where jmscode.fid IS NOT NULL

AND jmscode.cffeetype IN ('21', '15')

AND (jmscode.cfeditstate IS NULL OR jmscode.cfeditstate = '2')

and jmsCode.FCreateTime >= to_date('2015-12-10 00:00:00', 'yyyy-MM-dd HH24:mi:ss')

and jmsCode.FCreateTime <= to_date('2015-12-11 00:00:00', 'yyyy-MM-dd HH24:mi:ss'))

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

SQL>/

246780 rows selected.

Elapsed: 00:00:37.94

Execution Plan

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

Plan hash value: 2392790773

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

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

| 0 | SELECT STATEMENT | | 1466K| 390M| | 205K (12)| 00:05:16 | | |

| 1 | HASH UNIQUE | | 1466K| 390M| 409M| 205K (12)| 00:05:16 | | |

|* 2 | HASH JOIN RIGHT OUTER | | 1466K| 390M| | 124K (16)| 00:03:12 | | |

| 3 | TABLE ACCESS FULL | T_SCM_EXPENSEITEM | 47 | 1363 | | 3 (0)| 00:00:01 | | |

|* 4 | HASH JOIN | | 1466K| 349M| 22M| 123K (16)| 00:03:11 | | |

|* 5 | HASH JOIN RIGHT OUTER | | 114K| 21M| | 45586 (13)| 00:01:11 | | |

| 6 | TABLE ACCESS FULL | CT_FIN_JOININGTRADER | 1102 | 19836 | | 12 (9)| 00:00:01 | | |

|* 7 | HASH JOIN OUTER | | 114K| 19M| 17M| 45560 (13)| 00:01:11 | | |

|* 8 | HASH JOIN | | 114K| 15M| | 13485 (15)| 00:00:21 | | |

| 9 | PARTITION RANGE SINGLE| | 3319 | 269K| | 976 (17)| 00:00:02 | 21 | 21 |

|* 10 | TABLE ACCESS FULL | CT_FIN_JMSHANDOVERRECODE | 3319 | 269K| | 976 (17)| 00:00:02 | 21 | 21 |

| 11 | PARTITION RANGE ALL | | 615K| 35M| | 12433 (14)| 00:00:20 | 1 | 38 |

|* 12 | TABLE ACCESS FULL | CT_FIN_JOININGTRADERAPFEE | 615K| 35M| | 12433 (14)| 00:00:20 | 1 | 38 |

| 13 | VIEW | index$_join$_006 | 3741K| 107M| | 26977 (11)| 00:00:42 | | |

|* 14 | HASH JOIN | | | | | | | | |

| 15 | INDEX FAST FULL SCAN | CPK_OPE_WAYBILLBP7 | 3741K| 107M| | 12404 (6)| 00:00:20 | | |

| 16 | INDEX FAST FULL SCAN | UQ_CT_OPE_WAYBILL_IT_ZX | 3741K| 107M| | 11088 (7)| 00:00:18 | | |

| 17 | TABLE ACCESS FULL | CT_FIN_JOININGTRADERAPFEEENTRY | 17M| 980M| | 43906 (20)| 00:01:08 | | |

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

Predicate Information (identified by operation id):

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

2 - access("EXPENSEITEM"."FID"(+)="APFEEENTRY"."CFEXPENSEITEMID")

4 - access("TRADER"."FID"="APFEEENTRY"."FPARENTID")

5 - access("JMSCODE"."CFJOININGTRADERID"="JOININGTRADER"."FID"(+))

7 - access("WA"."FID"(+)="TRADER"."CFWAYBILLID")

8 - access("CFHANDOVERID"="TRADER"."FSOURCEBILLID")

10 - filter("JMSCODE"."FCREATETIME">=TIMESTAMP' 2015-12-10 00:00:00' AND ("JMSCODE"."CFFEETYPE"='15' OR

"JMSCODE"."CFFEETYPE"='21') AND ("JMSCODE"."CFEDITSTATE" IS NULL OR "JMSCODE"."CFEDITSTATE"='2') AND

"JMSCODE"."FCREATETIME"<=TIMESTAMP' 2015-12-11 00:00:00')

12 - filter("TRADER"."CFHANDTYPE"='2' OR "TRADER"."CFHANDTYPE"='4' OR "TRADER"."CFHANDTYPE"='7')

14 - access(ROWID=ROWID)

SQL>set line 999

col index_type for a15;

col index_name for a30;

col pos for 99;

col column_position for 99;

col column_name for a25;

col constraint_name for a25;

col tablespace_name for a25;

select a.constraint_name,a.constraint_type,b.index_name,c.index_type,c.uniqueness,c.status,c.visibility,b.column_name,

b.column_position pos,c.tablespace_name

from user_constraints a, user_ind_columns b, user_indexes c where b.table_name=a.table_name(+)

and b.table_name=upper(trim('&tname')) and b.index_name=a.index_name(+) and b.index_name=c.index_name

order by b.index_name, b.column_position;

SQL>SQL>SQL>SQL>SQL>SQL>SQL>SQL> 2 3 4 5 Enter value for tname: ct_ope_waybill

old 4: and b.table_name=upper(trim('&tname')) and b.index_name=a.index_name(+) and b.index_name=c.index_name

new 4: and b.table_name=upper(trim('ct_ope_waybill')) and b.index_name=a.index_name(+) and b.index_name=c.index_name

CONSTRAINT_NAME CO INDEX_NAME INDEX_TYPE UNIQUENESS STATUS VISIBILITY COLUMN_NAME POS TABLESPACE_NAME

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

CPK_OPE_WAYBILLBP7 P CPK_OPE_WAYBILLBP7 NORMAL UNIQUE VALID VISIBLE FID 1 EAS_D_HLWL_STANDARD

IDX$$_47CB0003 NORMAL NONUNIQUE VALID VISIBLE CFCUSTOMERTRANSNUMBER 1 EAS_D_HLWL_STANDARD

IDX$$_47CB0004 NORMAL NONUNIQUE VALID VISIBLE CFCUSTOMERORDERNUMBER 1 EAS_D_HLWL_STANDARD

IDX_CT_OPE_WAYBILL_HLIT_01 NORMAL NONUNIQUE N/A VISIBLE FCREATETIME 1

IDX_CT_OPE_WAYBILL_IT03 NORMAL NONUNIQUE VALID VISIBLE CFCREATEDEPID 1 HLWL_INDEX_TS

IDX_CT_OPE_WAYBILL_IT03 NORMAL NONUNIQUE VALID VISIBLE FID 2 HLWL_INDEX_TS

IDX_CT_OPE_WAYBILL_IT04 NORMAL NONUNIQUE VALID VISIBLE CFDEPOFCUSTOMERID 1 HLWL_INDEX_TS

IDX_CT_OPE_WAYBILL_IT04 NORMAL NONUNIQUE VALID VISIBLE FID 2 HLWL_INDEX_TS

UQ_CT_OPE_WAYBILL_IT_ZX U UQ_CT_OPE_WAYBILL_IT_ZX NORMAL UNIQUE VALID VISIBLE FNUMBER 1 EAS_D_HLWL_STANDARD

9 rows selected.

Elapsed: 00:00:00.93

weixin_39615808

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 执行计划 u,执行计划的理解，请大家根据执行计划，找出SQL语句中对应的关系...

ORACLE 11203下面的语句，我仔细浏览了一遍，发现，SQL语句的部分约束关系，没在执行计划中体现，也就是，计划里没执行某些约束，如暗红色部分的表，执行计划中并未体现它们的存在，请大家把执行计划中的操作，和SQL 语句中的关联要求，一一匹配起来，另外，索引，CPK_OPE_WAYBILLBP7 和 UQ_CT_OPE_WAYBILL_IT_ZX 都是 ct_ope_waybill 表。SQL...
复制链接

扫一扫