Re: Views into tables

From: <asaf_h_at_netvision.net.il>
Date: 1996/06/13
Message-ID: <NEWTNews.834682093.18203.asaf_at_dialup.netvision.net.il>#1/1

In Article<31BC5106.1788_at_daac.gsfc.nasa.gov>, <joanne_at_daac.gsfc.nasa.gov> writes:
> Path:

news.NetVision.net.il!psinntp!psinntp!psinntp!howland.reston.ans.net!swrinde!ne wsfeed.internetmci.com!chi-news.cic.net!news.cais.net!news.mathworks.com!newsga te.duke.edu!news.eff.org!news.umbc.edu!cs.umd.edu!newsfeed.gsfc.nasa.gov!usenet

> From: Joanne Woytek <joanne_at_daac.gsfc.nasa.gov>
> Newsgroups: comp.databases.oracle
> Subject: Views into tables
> Date: Mon, 10 Jun 1996 12:44:54 -0400
> Organization: NASA Goddard Space Flight Center -- Greenbelt, Maryland USA
> Lines: 48
> Message-ID: <31BC5106.1788_at_daac.gsfc.nasa.gov>
> Reply-To: joanne_at_daac.gsfc.nasa.gov
> NNTP-Posting-Host: tiffany.gsfc.nasa.gov
> Mime-Version: 1.0
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
> X-Mailer: Mozilla 2.01 (Macintosh; U; PPC)
>
> We have a very large table (called large_table) which has a column
> named status which has a small number of valid values, making it a
> poor candidate for indexing. Unfortunately a large number of queries
> and updates are done with status in the where statement. Most of the
> rest of the queries are on well-indexed columns. So one possibility
> I'm considering is breaking the table up into smaller tables based on
> the status values; e.g. table_A would have those rows which were in
> the original table with a status='A' and table_B would have those rows
> which were in the original table with a status='B'. We could then
> choose which table to use in our PL_SQL or PRO*C programs rather than
> constantly doing full-table scans of the very large table. (Another
> assumption is that most queries are on only one possible status at a
> time.)
>
> The problem is that we have a number of queries and updates which
> expect the original table structure to always be there. What I was
> hoping to do was to create a view which was named large_table which
> selected columns from either table_A or table_B depending on the
> status query and had the same structure as the table originally had;
> e.g.
>
> table_a
> id
> data
>
> table_b
> id
> data
>
> large_table (now a view)
> id
> data
> status
>
> A pseudo-create view statement is:
> create view large_table (id, data, status)
> as select id, data, 'A' from table_A when status in where is 'A' or
> select id, data, 'B' from table_B when status in where is 'B'
>
> Any ideas, suggestions, pointers on how to do this in a real create
> view statement, or simply letting me know that I am asking the
> impossible, would be appreciated.
>
> --
> Joanne Woytek
> joanne_at_daac.gsfc.nasa.gov
> Code 902
> NASA/GSFC

Joanne,

Using a view with "UNION ALL" should work well for you, especially if you know where your data resides (which segment of the view ) You could do the following :

1. Define the view

   Create view Big_view as
   Select 'A' Table_id,

           Fld1,
           Fld2, ...

   From table_A
   union all
   select 'B',

          Fld1,
          Fld2 ...

   from table_B

2. Select from the view

Select Fld1, Fld2, Fld3 ...
From Big_view
where Table_id = 'A' <<===== will tell Oracle to go to this table and Key = :Key_value... <<==== Will search the index on table_A

My tests have shown that Oracle does NOT generate a physical call to all other tables that are members in the "Big_view".

Now all you need to solve is the Update problem. Currently you cannot update this kind on View ... :(

In Oracle7 release 7.3 Oracle added some logic to the CBO in order to support such cases. The tests I have conducted were on 7.1.4.

Good luck !

Asaf.   Received on Thu Jun 13 1996 - 00:00:00 CEST