Unanswered: retrieving records from remote tables using dblink
i m firing a select query in my local database.
SELECT T1.csy AS POLICY_NO,
T2.cmn AS CLIENT_ID,
T2.GIVNAME AS GIVEN_NAME,
T2.SURNAME AS SURNAME,
TO_CHAR(T2.CLTDOB,'DD/MM/YYYY') AS DATEOFBIRTH,
T2.sap AS ,
T2.PHONE02 AS PHONE,
T2.PCODE AS PINCODE
FROM chman@ODS_UAT_PS T1, emp@ODS_UAT_PS T2
WHERE T1.man = T2.cman
this query and other querries using dblink are taking lot of time.
how can i improve its performance whether i should use hints or any other option is der????????
Performance might be improved if you create materialized views in your local database, whose source tables are located in remote databases. Refresh rate? It depends; if it can be set to rare (for example, once a day, possibly during the non-working hours), it might be an acceptable solution.
Have you had a look at the query as it is running on the other database? It could be that there are some tuning opportunities on the database that you are linking to. Also, is the linked to database local or distant?
Right, check the execution plan in the other database. DBLinks can change the SQL syntax.
You might try running the SQL directly in the other db and check for timings. If things run more quickly, then maybe you need to create a VIEW in that db which combines those 2 tables together, and then you just invoke the VIEW@dbname.