Linked Database - where is processing done

[cmmrfrds]

I am running a query where all the data except 1 table is on a linked database. First, this query runs much slower on the server I am executing from. Question. Is all the data from each table loaded onto the server I am executing from before any criteria is applied or does the part of the query execute on the remote server and only return the resultset? Is this type of query going to cause performance problems. Oracle 9I now going to 10G soon.

Here is the query.

SELECT ITEM.ITCPTMOD0, ITEM.ITCHARGECD, FACILITY.FCLCODE,
eap.proc_code as epic_code, eap.modifier as epic_mod,
VISITPROC.VPRWRKST, ITEM.ITCPTCD, CPTTABLE.CPTDESC,
CHRGCODE.CPTDESC, INVOICE.INLBDT, INVOICE.INBQNTF,
STAY.STBILNO, HLSYS_WORKSTATION.WSTEXT1, TEST.TSTTYPE
FROM ((AR.CPTTABLE@or1.ntcampus.mycompany.org CHRGCODE
INNER JOIN
(((((((AR.VISITPROC@or1.ntcampus.mycompany.org VISITPROC
INNER JOIN AR.FACILITY@or1.ntcampus.mycompany.org FACILITY
ON VISITPROC.VPRORDFCLTY = FACILITY.FCLCODE)
INNER JOIN
AR.VISIT@or1.ntcampus.mycompany.org VISIT
ON VISITPROC.VPRVTINTN = VISIT.VTINTN)
INNER JOIN
AR.VPRITLINK@or1.ntcampus.mycompany.org VPRITLINK
ON (VISITPROC.VPRINTN = VPRITLINK.LNKVPRINTN)
AND (VISIT.VTINTN = VPRITLINK.LNKVTINTN))
INNER JOIN
AR.TEST@or1.ntcampus.mycompany.org TEST
ON (VISITPROC.VPRTSTCODE = TEST.TSTCODE)
AND (VISITPROC.VPRSYSCODE = TEST.TSTSYSCODE))
INNER JOIN
AR.STAY@or1.ntcampus.mycompany.org STAY
ON VISIT.VTSTINTN = STAY.STINTN)
INNER JOIN
AR.ITEM@or1.ntcampus.mycompany.org ITEM
ON VPRITLINK.LNKITINTN = ITEM.ITINTN)
LEFT OUTER JOIN
LAB.HLSYS_WORKSTATION@or1.ntcampus.mycompany.org HLSYS_WORKSTATION
ON VPRITLINK.LNKWRKST = HLSYS_WORKSTATION.WSCODE)
ON CHRGCODE.CPTCODE = ITEM.ITCHARGECD)
INNER JOIN
AR.CPTTABLE@or1.ntcampus.mycompany.org CPTTABLE
ON ITEM.ITCPTCD = CPTTABLE.CPTCODE)
INNER JOIN
AR.INVOICE@or1.ntcampus.mycompany.org INVOICE
ON ( (ITEM.ITVTINTN = INVOICE.INVTINTN)
AND (ITEM.ITINEXT = INVOICE.INEXT)
)
AND (VISIT.VTINTN = INVOICE.INVTINTN)
LEFT OUTER JOIN
clarity.clarity_eap eap
ON ITEM.ITCHARGECD = eap.proc_code

WHERE TEST.TSTTYPE <> 1
AND ( INVOICE.INLBDT >=
TO_DATE ('12-04-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
AND INVOICE.INLBDT <
TO_DATE ('20-04-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
)
ORDER BY HLSYS_WORKSTATION.WSTEXT1,
FACILITY.FCLCODE,
VISITPROC.VPRWRKST,
ITEM.ITCPTCD

 

[Dagon]

Other people may have different points of view but, personally, I always avoid queries which join tables across multiple databases. They are invariably optimized very badly and it is nearly always better to copy the tables to a common database first.

 

[cmmrfrds]

Here is the background.
1. Business Object Enterprise (BOE) will schedule these queries.
2. BOE needs to connect to the Oracle server(s).
3. It is a vendor supplied database.
4. Some queries report off the vendor database.
5. Some queries audit between the vendor database and the data warehouse Oracle server.

The reports that go against only the vendor database can have a connection to that databse. This brings up some maintenance issues that I had hoped to avoid, but I can connect directly.

The audit reports will be using the linked database and I still would like an answer to my original question to be proactive on possible performance issues. I don't want to get into copying tables from the vendor database.

Thank you.

 

[cmmrfrds]

This issue forced me to do more extensive investigation of what information is available in Explain Plan. I discovered that the "OTHER" column in the Plan Table contains the syntax of SQL sent to the remote database. It showed that the query was rewritten and some of the remote tables were joined and others done in a single pass. Most importantly, it showed that the predicate information was supplied to the remote database so that only the data requested was returned to the local server. I am more comfortable with the remote query knowing that the data volume can be pared down with a selective predicate. I think this answers my original question, but I am happy to get feedback if I came to the wrong conclusion.