方法一:
select /*+ NO_PUSH_PRED(T1) */ p.otstock_id,
p.patch_no, --派工单号
p.vin, --vin码
p.customer_name, --客户名称
p.out_man, --出库人员
(select b.yg_name
from sys_users a, t_bic_yg b
where a.belong_person = b.yg_id
and a.login_name = p.out_man) as out_man_name,
to_char(p.out_date, 'yyyy-mm-dd') as out_date, --出库日期
to_char(p.pre_commit_date, 'yyyy-mm-dd') as pre_commit_date, --预交日期
p.pack_man, --领料人员
p.repair_outstock_no --维修出库单号
from T_PA_REPAIR_PATCH p
where p.otstock_id not in
(select /*+ NO_PUSH_PRED(M) NO_PUSH_PRED(N) */ m.otstock_id
from (select rp.otstock_id,
rp.patch_no,
sum(nvl(d.return_count, 0)) as thl
from T_OUTSTOCK_RETURN r --出库退货表
join t_Outstock_Return_Detail d
on d.return_id = r.return_id --出库退货明细
JOIN T_PA_REPAIR_PATCH rp
ON r.ref_no = rp.otstock_id --维修出库表
group by rp.patch_no, rp.otstock_id) m,
(select a.otstock_id,
patch_no,
sum(Out_Stock_Count) as ckl
from t_pa_repair_out_stock_detail a --维修出库明细表
group by a.otstock_id, a.patch_no) n
where m.patch_no = n.patch_no
and m.otstock_id = n.otstock_id
and thl = ckl)
方法二:在要执行的语句前加上alter session set "_optimizer_push_pred_cost_based" = FALSE;
alter session set "_optimizer_push_pred_cost_based" = FALSE;
select p.otstock_id,
p.patch_no, --派工单号
p.vin, --vin码
p.customer_name, --客户名称
p.out_man, --出库人员
(select b.yg_name
from sys_users a, t_bic_yg b
where a.belong_person = b.yg_id
and a.login_name = p.out_man) as out_man_name,
to_char(p.out_date, 'yyyy-mm-dd') as out_date, --出库日期
to_char(p.pre_commit_date, 'yyyy-mm-dd') as pre_commit_date, --预交日期
p.pack_man, --领料人员
p.repair_outstock_no --维修出库单号
from T_PA_REPAIR_PATCH p
where p.otstock_id not in
(select m.otstock_id
from (select rp.otstock_id,
rp.patch_no,
sum(nvl(d.return_count, 0)) as thl
from T_OUTSTOCK_RETURN r --出库退货表
join t_Outstock_Return_Detail d on d.return_id =
r.return_id --出库退货明细
JOIN T_PA_REPAIR_PATCH rp ON r.ref_no = rp.otstock_id --维修出库表
group by rp.patch_no, rp.otstock_id) m,
(select a.otstock_id, patch_no, sum(Out_Stock_Count) as ckl
from t_pa_repair_out_stock_detail a --维修出库明细表
group by a.otstock_id, a.patch_no) n
where m.patch_no = n.patch_no
and m.otstock_id = n.otstock_id
and thl = ckl)