Re: help with Unions

After working with several variations of what you mention, there are pluses and minuses with each.
1) Create a view that is a union of the 50 tables. Simple, no maintenance, but you're right, it won't query as quickly as a single table. 2) Create a single table with the columns you need. Downside is it needs to be maintained. But gives very fast response. 3) As far as the in-between - tables with 1.5 million records, don't mess with it. Oracle will have no trouble managing a table with 7 million records (that's why you boughtit, right?), and your performance will be fine.

So, the view is slower, but before going with the single table, try the view. If it turns out to be too slow, drop the view and create a table of the same name. My guess is, unless you're really hammering it, the view will be fast enough.

John Alexander
www.SummitSoftwareDesign.com
St. Petersburg, FL

Patrick J. Hayes <pjh_at_ngdc.noaa.gov> wrote in message news:3A7888FE.FF3811D3_at_ngdc.noaa.gov...
> I have 50 tables with 7,000,000 records that need to be cross searched.
> The method that has been suggested is to do the cross search using
> unions. The tables all have different columns(record length of 80 to
> 200) and only 3 columns will be searched location, year and magnitude.
>
> Does any database people have experience with using unions between 50
> tables with 7,000,000 records?
>
> I asked around the office and here are the suggestions that have been
> offered.
> Create one large table with all 7,000,000 records. Another is make maybe
> 5 tables with ~1.5 million records. Another suggestions is to use views.
> Another is to search a look up table with only the 3 columns that are
> searched and then have a link to each specific table. I've done unions,
> but never with this many tables and records. I'm really worried that the
> speed will really slow down when the unions searchs all these tables.
> I don't want to develop a system then find that its too slow to for the
> end user to use.
>
> Is this a good approach to use unions?
> Does anyone have any other suggestions to do this?
>
> Is there a degradation of performance when you hit a certain limit of
> tables in with a union?
> In other words, say at 10 tables in a union the performance really
> degrades.
>
> Patrick Hayes
>
>
>
>
>
Received on Thu Feb 01 2001 - 05:12:27 CET