请问如何优化这个script

chinayokel

這個script效能很差,有辦法優化嗎?
select
bd.department_code dep_code,
sys.inventory_item_id item_id,
sys.planner_code ,
to_char(wdj.scheduled_completion_date,'RRRR') || to_char(wdj.scheduled_completion_date,'WW') weeks,
wdj.scheduled_completion_date
from wip_discrete_jobs wdj,
     wip_operations wo,
     bom_departments bd,
            mtl_system_items_kfv sys
where wdj.organization_id = :v_org_id
  and bd.organization_id = :v_org_id
  and wdj.wip_entity_id = wo.wip_entity_id
  and wo.department_id = bd.department_id
  and wdj.status_type =3
  and sys.organization_id = :v_org_id
  and sys.inventory_item_id = wdj.primary_item_id
  and wdj.wip_entity_id in
      (select job.wip_entity_id from wip_discrete_jobs job,wip_operations wo,bom_departments bd1
       where job.organization_id = :v_org_id and bd1.organization_id = :v_org_id
         and job.wip_entity_id = wo.wip_entity_id and wo.department_id = bd1.department_id
         and bd1.department_code <= bd.department_code and job.status_type =3
         and nvl(wo.quantity_in_queue,0) + nvl(wo.quantity_running,0) >0
         and to_char(wdj.scheduled_completion_date,'RRRR') = to_char(:v_plan_date,'RRRR')
         and to_char(wdj.scheduled_completion_date,'WW') = to_char(:v_plan_date,'WW')         
        group by job.wip_entity_id)         
  and to_char(wdj.scheduled_completion_date,'RRRR') = to_char(:v_plan_date,'RRRR')
  and to_char(wdj.scheduled_completion_date,'WW') = to_char(:v_plan_date,'WW')
group by  bd.department_code ,
sys.inventory_item_id ,
sys.planner_code ,
to_char(wdj.scheduled_completion_date,'RRRR'),
to_char(wdj.scheduled_completion_date,'WW') ,
wdj.scheduled_completion_date

sunweijie22

关注

alexl

select
bd.department_code dep_code,
sys.inventory_item_id item_id,
sys.planner_code ,
to_char(wdj.scheduled_completion_date,'RRRR') || to_char(wdj.scheduled_completion_date,'WW') weeks,
wdj.scheduled_completion_date
from wip_discrete_jobs wdj,
     wip_operations wo,
     bom_departments bd,
            mtl_system_items_kfv sys
where wdj.organization_id = :v_org_id
  and bd.organization_id = :v_org_id
  and wdj.wip_entity_id = wo.wip_entity_id
  and wo.department_id = bd.department_id
  and wdj.status_type =3
  and sys.organization_id = :v_org_id
  and sys.inventory_item_id = wdj.primary_item_id
--  and wdj.wip_entity_id in
  AND EXISTS
      (select job.wip_entity_id
            from wip_discrete_jobs job,wip_operations wo,bom_departments bd1
       where job.organization_id = :v_org_id
             and bd1.organization_id = :v_org_id
         and job.wip_entity_id = wo.wip_entity_id and wo.department_id = bd1.department_id
         and bd1.department_code <= bd.department_code and job.status_type =3
         and nvl(wo.quantity_in_queue,0) + nvl(wo.quantity_running,0) >0
         and to_char(wdj.scheduled_completion_date,'RRRR') = to_char(:v_plan_date,'RRRR')
         and to_char(wdj.scheduled_completion_date,'WW') = to_char(:v_plan_date,'WW')
                 AND job.wip_entity_id = wdj.wip_entity_id  )      
--        group by job.wip_entity_id)         
  and to_char(wdj.scheduled_completion_date,'RRRR') = to_char(:v_plan_date,'RRRR')
  and to_char(wdj.scheduled_completion_date,'WW') = to_char(:v_plan_date,'WW')
group by  bd.department_code ,
sys.inventory_item_id ,
sys.planner_code ,
to_char(wdj.scheduled_completion_date,'RRRR'),
to_char(wdj.scheduled_completion_date,'WW') ,
wdj.scheduled_completion_date