How to make this query better?

From: Stephen Schow <sjs_at_netcom.com>
Date: 28 Oct 92 19:06:56 GMT
Message-ID: <1992Oct28.190656.10753_at_netcom.com>

I am trying to join about 5 different tables together and I don't know whether I should create layers of views upon views, where I join 2 tables at a time and keep joining an additional table to each successive view.... or whether I should try to figure out the proper relations to do it with one view.

Does anyone know if the performance is much slower this way or the other, or if my join conditions can even be met in only one view?

Below is the sql script that creates the view of view of view of view of....

---

prompt Dropping View - AE_VIEW1;

drop view ae_view1;

prompt Creating View - AE_VIEW1;

create view ae_view1 (lot_no,description,man_id,systemic) as select

        B.lot_number,
        A.ae_pref_term,
        A.man_id,
        A.systemic

from ${OWNER}.terms A, ${OWNER}.lots B
where A.man_id = B.man_id;

prompt Dropping View - AE_VIEW2;

drop view ae_view2;

prompt Creating View - AE_VIEW2;

create view ae_view2 (lot_no,description,man_id,customer,onset,systemic) as select

        A.lot_no,
        A.description,
        A.man_id,
        B.cust_no,
        B.onset,
        A.systemic

from ${OWNER}.ae_view1 A,${OWNER}.pats B where A.man_id = B.man_id (+);

prompt Dropping View - AE_VIEW3;

drop view ae_view3;

prompt Creating View - AE_VIEW3;

create view ae_view3 (lot_no,item_no,no_syringes,no_ccs,no_units,

                description,man_id,customer,onset,systemic) as
select
        A.lot_no,
        B.item,
        B.syrqty,
        B.ccqty,
        B.units,
        A.description,
        A.man_id,
        A.customer,
        A.onset,
        A.systemic

from ${OWNER}.ae_view2 A,${OWNER}.lotdtl B where B.lot (+) = A.lot_no;

prompt Dropping View - AE_VIEW4;

drop view ae_view4;

prompt Creating View - AE_VIEW4;

create view ae_view4 (lot_no,item_no,item_desc,no_syringes,no_ccs,no_units,

                description,man_id,customer,onset,systemic) as
select
        A.lot_no,
        A.item_no,
        B.imdes,
        A.no_syringes,
        A.no_ccs,
        A.no_units,
        A.description,
        A.man_id,
        A.customer,
        A.onset,
        A.systemic

from ${OWNER}.ae_view3 A,${OWNER}.psitemastf B where A.item_no = B.imitm (+);

prompt Dropping View - AE_VIEW;

drop view ae_view;

prompt Creating View - AE_VIEW;

create view ae_view (lot_no,item_no,item_desc,no_syringes,no_ccs,no_units,

                type,log_no,code,description,defect,rsp,returned,
                man_id,customer,onset,systemic) as
select
        A.lot_no,
        A.item_no,
        A.item_desc,
        A.no_syringes,
        A.no_ccs,
        A.no_units,
        'AE',
        B.five,
        B.six,
        A.description,
        B.ten,
        B.six,
        B.six,
        A.man_id,
        A.customer,
        A.onset,
        A.systemic

from ${OWNER}.ae_view4 A,${OWNER}.neednull B where B.pk (+) = 1;

-- 


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

Steve Schow    | But you don't have to use the claw, if you
sjs_at_netcom.com | pick the pear with the big paw paw......
               | Have I given you a clue......?
               |                       - Baloo the Bear


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

Received on Wed Oct 28 1992 - 20:06:56 CET