Re: Views into tables

Have you tried indexing the status column and performance testing the results with tkprof etc ..

Below seems a heavey method of solving a performance issue ??

Neill Atkins
Oracle Consultant
First Word Software Limited ..

.
.

In article <NEWTNews.834682093.18203.asaf_at_dialup.netvision.net.il>, asaf_h_at_netvision.net.il writes
>
>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.
>
>

Neill Atkins
Portsmouth, UK. neill_at_firstwrd.demon.co.uk Received on Thu Jun 13 1996 - 00:00:00 CEST