I was reading an interview with Chris Date the other day, which got me
thinking about a problem I'm currently having:
I have an application that keeps information in 6 denormalized tables,
D1 through D6. To tie things together, all these tables have a common
column, let's call it obj_id.
There is an additional table P, that is generated from a query that uses
D1 through D3 and JOINS them to another table, X. P also has this obj_id
column.
All these tables are pretty big, so I keep them indexed by obj_id. Table
P in particular is a huge time series which I also need to keep indexed
by date, so there is an index on P on (obj_id, date).
Now, my problem: I need to keep P indexed, because I need to use it in
queries very often, and I need them to be quick. However, due to the
indexing, putting new rows in P takes forever, which it didn't used to
when it wasn't indexed. I know that the typical solution is to drop the
index, import the new data, and create the index again. However, this
doesn't work for me, as the application is pretty interactive and users
could be querying P and adding rows to it (on different obj_id's)
simultaneously.
The ideal situation would be if reindexing didn't reindex the whole
table, but just those entries added with the given obj_id. This, as far
as I know, is not possible.
I don't really need table-wide indexes. The data in different obj_id's
is not compared very often - what happens all the time is the
cross-table relationships on the same obj_id.
So what Date seems to wish for RDBMs to handle objects would work here:
I would have a table of obj_id's, with columns D1 .. D6 and P. The
object stored in a (row, column) would be a table with the appropriate
entries, and in the case of P, an index on date. Retrieving all the
related entries for a given obj_id would be trivial, as would inserting
a new table object into the P column, for a given obj_id.
Are there plans to handle table objects as possible values in other
tables, as Date seems to propose?
Is there currently a way of easing the design problem here? Any advice
on redesigning the data?
Thanks
Jaime
***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.
Bear Stearns does not provide tax, legal or accounting advice. You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of: (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************
