
-- best configuration of build plan class and instance 
create or replace temporary view
v_bp_opt as (
  select * 
  from build_plan_instance, build_plan_class
  where       bpc_name = 'BpTrad'
                 and  bpi_cf_join_bundle = 0
                 and  bpi_cf_mat    = 0
                 and  bpi_cf_concat = 0
                 and  bpi_bpc = bpc_id
);

---
--- optimal plans
---

-- work packages of optimal plans
create or replace temporary view
v_wp_opt as (
  select *
  from work_package, plan_generator, card_provider, v_bp_opt
  where       pg_name  = 'DPccp'
         and  cp_name  = 'cp_true'
         and  wp_pg    = pg_id
         and  wp_cp    = cp_id
         and  wp_bp    = bpi_id
);

-- plan summary of optimal plans
create or replace temporary view 
v_ps_opt as (
  select ps.*
  from   plan_summary ps
  where  exists(select * from v_wp_opt where ps.ps_wp = wp_id)
);


---
--- pessimistic plans
---

-- work packages of pessimistic plans
create or replace temporary view
v_wp_pessimistic as (
  select *
  from work_package, plan_generator, card_provider, v_bp_opt
  where       pg_name  = 'DPccp'
         and  cp_name  = 'cp_join_base'
         and  wp_pg    = pg_id
         and  wp_cp    = cp_id
         and  wp_bp    = bpi_id
);

-- plan summary of pessimistic plans

create or replace temporary view
v_ps_pessimistic as (
  select ps.*
  from   plan_summary ps
  where  exists(select * from v_wp_pessimistic where ps.ps_wp = wp_id)
);



