Any reason not to combine several views?

From: Sean McAfee <mcafee_at_waits.facilities.med.umich.edu>
Date: Wed, 24 Feb 1999 18:54:48 GMT
Message-ID: <YVXA2.8871$Ge3.34543875_at_news.itd.umich.edu>

In a project I'm working on I often need to combine a few columns from several different tables:

create view field1_field2_view as

    select field1, field2 from table1

        union
    select field1, field2 from table2

        union
-- and so on, for up to seven different tables

create view field3_view as

    select field3 from table1

        union
    select field3 from table2

        union
-- etc.

I have 4-5 views like this for some sets of tables. I recently had the idea of combining all of the views together into one, and using the single new view everywhere I previously used the more specific individual views:

create view aggregate_view as

    select field1, field2, field3, field4, field5, field6 from table1

        union
    select field1, field2, field3, field4, field5, field6 from table2

        union
-- etc.

This would certainly simplify some of my code, but before I implement it I'd like some advice about whether this would affect performance. If I used aggregate_view in code which is only concerned with field1, do I still consume resources gathering fields 2-6? Some of these tables are quite large. It would be even better if I could replace field1,...,field6 with "*", since many of the tables I'm union-ing together have the same column structure, but they also have a couple of dozen other fields that aren't used by any of my code.

Thanks in advance!

--

Sean McAfee | GS d->-- s+++: a26 C++ US+++$ P+++ L++ E- W+ N++ |
            | K w--- O? M V-- PS+ PE Y+ PGP?>++ t+() 5++ X+ R+ | mcafee_at_
            | tv+ b++ DI++ D+ G e++>++++ h- r y+>++**          | umich.edu

Received on Wed Feb 24 1999 - 19:54:48 CET