Re: Inquiry: Beyond replication

Maybe an end run will get what you need:

Periodically select the SQL from the cache. Do some stats. See what needs to be downloaded.

Dr.Matt wrote:

> Hello,
> I need something that looks like a table but is actually a code
> block, or looks like a server but is actually a customizable code
> block, or some way to fire a trigger in response to a mere query
> without any update.
> More and more I'm finding places in my work where I need a local
> departmental copy of a view of an institution-wide table for heavy
> departmental research processing that shouldn't run against the
> institutional table because of sheer bandwidth and processing demands.
> Self-refreshing replication ("CREATE SNAPSHOT...") helps a bit.
> At the departmental and subdepartmental level, the clients to the
> tables are a large number of different custom apps, mostly built
> by third parties. When a user keys in a UniqueID, first thing a
> client app does is generally query the departmental table to load
> the form. If the data has previously been tracked within the department,
> the data will have been loaded from the institutional table.
> There's the rub. Often data is requested in one department as
> a follow-up to a transaction in another department, and the desired
> data is now in the institutional data warehouse but hasn't been
> tracked by my department.
> So what I'm needing is to intercept certain types of queries that
> match some fairly simple patterns, and go into the following kind of
> code block:
>
> 1) Try the query against the department's table. If it returns
> any rows, return the resulting cursor to the client.
> 2) Else, if no data was found in the department's table, open
> a link to the institution's data warehouse and attempt to
> insert into the department's table the results of the same
> query issued against the data warehouse;
> 3) Re-issue the query against the department's table and return
> whatever comes back unconditionally.
>
> So from the client's point of view, it looks like queries by unique ID
> against the departmental table respond with as much data as is in the
> data warehouse. The departmental table only contains rows for
> entities that are of interest to that department. End users are not
> asked to re-key any data that has been collected elsewhere in the
> institution. And none of the existing servers or clients should need
> to be rewritten as the potential complexity should be encapsulated.
>
> I've been reading Oracle documentation and that of some other competitors
> and I haven't found a way of doing this sort of encapsulation, call-
> by-need updating, etc.
> The closest I've come is to break into the code for a client,
> replacing the failure condition on the initial query with the
> insertion of a stub record in the departmental table, and running an
> insert trigger there to try to flesh out the record with data from the
> institutional data warehouse. This approach is unacceptable here as
> most of our client apps are not our property.
>
> Has anybody else encountered this need and found a decent solution?
>
> Matt Fields
>
> Ann Arbor, MI
Received on Mon Oct 12 1998 - 00:00:00 CDT