How to write this Query ??

From: Shariq Mansoor <opto2y_at_menudo.uh.edu>
Date: 1996/06/23
Message-ID: <4qkjv9$1hr_at_masala.cc.uh.edu>#1/1

Hi,

I have some question for Oracle gurus I would really appreciate if someone help me with these questions regarding this query.

CREATE OR REPLACE VIEW V1 AS
  (SELECT
      a.c1,b.c2,c.c3........(list of col. from the tables)    FROM
      R,ST,PS,P,E,ET,CO,OU,OT
   WHERE

      R.CODE = ST.CODE AND
      R.PS_ID = PS.PS_ID AND
      PS.P_ID = P.P_ID AND
      P.P_ID = E.P_ID AND
      E.E_CODE = ET.E_CODE AND
      E.PE_ID = CO.PE_ID AND
      PS.O_ID = CO.O_ID AND
      CO.RS_ORG = OU.IT_ID AND
      CO.O_CODE = OT.O_CODE);

CREATE OR REPLACE VIEW V2 AS
  (SELECT
      S.col1,V1.*

   FROM
     SR,S,V1
  WHERE

     S.PS_ID = SR.PS_ID AND
     V1.O_ID = SR.KEY_ID (+) AND
     SR.KEY_CODE = 'ORD' AND
     SR.SR_CODE IN ('INP','REQ')

   UNION
   SELECT
      s.col1,V1.*
   FROM
     SR,S, V1
   WHERE

     S.PS_ID = SR.PS_ID AND
     V1.O_ID = SR.KEY_ID (+) AND
     SR.KEY_CODE = 'EPISOD' AND
     SR.SR_CODE IN ('AT','AD'));

All the joins are on Index Keys for both views. (Size of the database is 15-20G)

• Is it OK to use two views for this query.(YES or NO) ? Whats are the advantages/disadvantages ?
• Should I use a procedure to divide this query into parts ?
• Is there a way to combine these two views into a single view ? Is there is any advantage of doing this ?
• What is your (Logical) opinion about views ? is it OK to use view or use a select statement instead ?
• Is it OK to use Union for this ?

(Note: The result of the first view will not be more than 5 to 7 rows)

Thanks in advance

Shariq Mansoor
e-mail shariq_at_uh.edu Received on Sun Jun 23 1996 - 00:00:00 CEST