You could create a view for each day "arcv_xxxx" that has the same number of
fields for each day. For days with fewer fields, you could have the view
select constant values for the columns. When you want to add fields to a
new day, you would just need to change all the views.
Michael Cordner wrote in message <7kpm4q$nud$1_at_cougar.golden.net>...
> I'm working on an extremely high volume archive system, the core of
>which is an Oracle 7 database. The archive is structured to store items on
a
>daily basis, and creates a new table for every working day. Each table is
>named according to the unix day on which the table was created. For
example,
>five days worth of tables would look like this in the system table:
>
>arc_10750
>arc_10751
>arc_10752
>arc_10753
>arc_10754
>
> Given this structure, it follows that to search several days worth of
>tables for an item would involve an SQL UNION statement. For instance, if I
>wanted to search these tables to find an item in which, say, fuzzy=2, it
>would require the following statement:
>
>select * from arc_10750 where fuzzy=2 UNION select * from arc_10751 where
>fuzzy=2 UNION select * from arc_10752 . . .
>
>And so forth. This in itself is not a problem.
>
>The problem arises when we allow fields to be added to next day's table.
>When we add a field to the schema, the field is included in all future
>tables.
>
>Say that these are the fields in arc_10750:
>
>key, field1, field2, fuzzy
>
>The next day, we decide to add a field to the archive system. arc_10751,
and
>all later tables, have these fields:
>
>key, field1, field2, fuzzy, newfield
>
>If I execute the above UNION statement across these two tables, I get an
>Oracle error which tells me that the query block has an invalid number of
>result columns. This makes sense, since I'm trying to UNION tables with
>different fields.
>
>So here's the problem: I need to somehow modify this statement to make it
>work. I need to be able to search across the entire range of tables,
>regardless of whether later tables have added fields. I'm using the JDBC
>thin driver to access the db, and I have access to a set of metadata tables
>which describe what the fields are in a table for any given date.
>
>If anyone has come across a similar situation, I'd appeciate some advice.
Is
>there a way to somehow "widen" a resultset?
>
>I'd appreciate any advice whatsoever.
>
>-Michael Cordner
>
>
>
>
>
>
Received on Wed Jun 23 1999 - 10:27:58 CDT
