Re: Inquiry: Beyond replication

In a certain newsgroup called comp.databases.oracle.server, there was a poster called <jh33378_at_deere.com>, for it means John P. Higgins. And the
Lord Bob spoke unto John P. Higgins and bade John P. Higgins relay
<3622CCA4.185A1BB1_at_deere.com> unto the people, saying:

>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.

Yeah, we tried that idea. When a user gets to an ancillary, 50% of the time they're going to key in a unique ID we've never seen before, and I need to satisfy the query--fast, to save gobs of rekeying time. But the stuff tracked at the departmental level still is less than 5% of what's available at the data warehouse, and funds for storage are allocated in accordance with that. Departmental research wants to run CPU-hog queries against only the rows of interest to the department... and all the departments have research functions.

>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 Tue Oct 13 1998 - 00:00:00 CEST