碰到一个想不通的问题，求教大虾！

Richard345265669 2013-09-09 10:21:57

写了两段Script，感觉做的是同样的事情，但是执行计划的差距惊人的夸张，百思不得其解。
第一段：
INSERT INTO ODS_TEMP_SHIP_CYCLE_TIME
SELECT c1.site,
c1.shift_timekey,
c1.factory,
c1.mat_id,
c1.gls_id,
c1.lot_id,
c1.event_timekey,
c1.hour_timekey,
c1.oper_code,
c1.oper_ver,
c1.plan,
c1.plan_ver,
c1.product,
c1.product_ver,
c1.flow,
c1.flow_ver,
c1.production_type,
c1.owner_code,
c1.GROUP_ID,
c1.mat_state,
c1.mat_process_state,
c1.mat_grade,
c1.mat_judge,
c1.product_type,
c1.unit_type,
c1.user_id,
c1.eqp_id,
c1.recipe_id,
c1.cst_id,
c1.rework_state,
c1.logged_in_time,
c1.logged_out_time,
c1.arrive_event_name,
c1.arrive_time,
c1.arrive_gls_qty,
c1.arrive_pnl_qty,
c1.leave_event_name,
c1.leave_time,
c1.leave_gls_qty,
c1.leave_pnl_qty,
c1.cycle_time,
c1.main_oper_cycle_time,
c1.main_oper_process_time,
c1.main_oper_waiting_time,
c1.bank_time,
'N',
'',
'',
sysdate
FROM eds_cycle_time_mat@etl2edb c1
WHERE c1.site = 'C1'
AND c1.factory = 'CELL'
AND c1.unit_type = 'Panel'
AND EXISTS
(SELECT 0
FROM eds_first_ship_panel@etl2edb f
WHERE 1 = 1
AND c1.mat_id = f.pnl_id
AND c1.factory = f.factory
AND c1.site = f.site
AND f.shift_timekey >= '20130901 073000'
AND f.shift_timekey < '20130902 073000');

第二段：
INSERT INTO ODS_TEMP_SHIP_CYCLE_TIME
SELECT *
FROM eds_cycle_time_mat@etl2edb c1
WHERE c1.site = 'C1'
AND c1.factory = 'CELL'
AND c1.unit_type = 'Panel'
AND EXISTS
(SELECT 0
FROM eds_first_ship_panel@etl2edb f
WHERE 1 = 1
AND c1.mat_id = f.pnl_id
AND c1.factory = f.factory
AND c1.site = f.site
AND f.shift_timekey >= '20130901 073000'
AND f.shift_timekey < '20130902 073000');

第一段的Plan
INSERT STATEMENT ALL_ROWSCost: 116,144,469 Bytes: 782 Cardinality: 2
3 FILTER
1 REMOTE REMOTE SERIAL_FROM_REMOTE EDS_CYCLE_TIME_MAT ETL2EDBCost: 698,734 Bytes: 751,504,346 Cardinality: 1,922,006
2 REMOTE REMOTE SERIAL_FROM_REMOTE EDS_FIRST_SHIP_PANEL ETL2EDBCost: 3 Bytes: 75 Cardinality: 1

第二段的Plan
Plan
INSERT STATEMENT ALL_ROWSCost: 0
1 REMOTE REMOTE SERIAL_FROM_REMOTE ETL2EDB

ODS_TEMP_SHIP_CYCLE_TIME和eds_cycle_time_mat表结构是一样的，之所以想写成第一种方式是避免以后eds_cycle_time_mat变更时需要变更第一个表。

...全文