RE: Remote table performance on complex queries/views

On 2016/09/13 03:34, Jonathan Lewis wrote:

> That type of code is bad within a single instance and guaranteed to be a disaster if the scalar subqueries go remote. Distributed queries are bad enough because they typically give you a choice of nested loop: large numbers of very small messages flooding the network (which is the effect you get from the subquery approach) or hash joins which pull excessive volumes of data across the network.
>
> Mladen's suggested bodge has some merit - if you create read-only snapshots of the remote tables, with the minimum set of columns, any row restrictions you can invent, and only the minimum set of indexes you could then do an atomic fast refresh every few minutes.
>
> For each MV you can create a view which reuses the original table name (create or replace view TABLEX as select * from MV_TABLEX) so that your local code doesn't have to change.
>
> Oracle 12c can unnest inline scalar suqbueries and turn them into outer joins - but that won't necessariy help much, even if it did it right every time.

I had mentioned that I ruled out MVs (and DataGuard, too). But trying to keep my post briefish-like, I didn't elaborate. The main app user says the response time for a particular function is "1-2 minutes", which is not acceptable. From what I know of the data flow, I believe that the MV would only change where the delay in response time occurs, but would not remove it. Yes, the phrases "from what I know" and "I believe" just scream for a test to prove/disprove.... :)

This app is a bolt-on to our ERP. It attempts to supplement core functionality using its own data repository schema which extends what data is able to be stored in the base ERP. The tables in the app's schema are then joined to the ERP tables via these views. At least the DML is handled via the ERP's API, which is at least a known entity.

SELECT magic_bullet FROM dual;
select magic_bullet from dual
 *
ERROR at line 1:
ORA-00904: "MAGIC_BULLET": invalid identifier

Huh. Still doesn't work. I'll try again in a few years, when DBAs are obsolete...

Thanks Jonathan and Mladen,
Rich

--
http://www.freelists.org/webpage/oracle-l