Re: Views into tables
From: Dr. Blue <OU79138_at_deere.com>
Date: 1996/06/14
Message-ID: <31C18111.FB3_at_deere.com>
Neill Atkins wrote:
>
> 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 Fri Jun 14 1996 - 00:00:00 CEST
Date: 1996/06/14
Message-ID: <31C18111.FB3_at_deere.com>
What about clusters?
Richard
Neill Atkins wrote:
>
> 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 Fri Jun 14 1996 - 00:00:00 CEST