Re: Views into tables

Joanne Woytek wrote:
> 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;
>
> 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.

What you're talking about is horizontal partitioning of your data based on status_code. Before taking this step, you might want to think a bit about exactly what type of update/select activity actually hits this table via status_code alone. If you have a huge table, I find it hard to believe you really need to update every row that has a certain status code value. Even selecting by status_code seems almost like a report/batch oriented activity, and one that is quite likely to hit virtually every row in the table anyway. In that case, you might want to go ahead and take a hit on the full table scan and just process every row anyway. If your only hits by status_code are batch/reporting, and the processing time fits in your batch window, you probably ought to think hard before partitioning.

On the other hand, there's no reason you can't create separate tables for each status_code value and create a merged view of all of them. The real problem here is that you can't update a view that references multiple tables, so you're stuck with select only. You'd have to make four paritioned calls to select the correct rows for updating.

Writing SQL for views is covered extensively in the manuals, so I won't reference it here.

arenn. Received on Mon Jun 10 1996 - 00:00:00 CEST