help with Unions

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 Wed Jan 31 2001 - 22:51:58 CET