Re: Views into tables

You have to use unions:

create view large_table (id,data,status) as select id,data,'A' from A

   union
   select id,data,'B' from B;

Of course, you have to consider how this might perform for you. When you write a query using this unioned view you probably want it to actually apply you're more detailed query first and then do the union. But...Oracle will most likely union the two tables together and then apply you're additional where clauses to the data in memory. Not good for large tables. I can't testify that this is how Oracle will resolve the SQL 100%, but I'm pretty sure that this is their algorithm.

I think, like it or not, that the large table with the not very good queries are your best bet. If you are querying a field "status" that returns a large percentage of the rows then you will be doing a full-table-scan. But, given the state of the Oracle technology, you probably have a few tuning options you can look into to make your scan run faster.

good luck. Received on Tue Jun 11 1996 - 00:00:00 CEST