Hi Geeks,
My project has a requirement to fetch information of a remote database with huge data through the local database (mostly having constant data). I have implemented it with following approach:
1> Created local user account
2> Created views for all the tables having constant data in the remote database using database links .
e.g. CREATE OR REPLACE VIEWS REMOTE_TABLE_NAME AS SELECT * FROM REMOTE_TABLE_NAME@DATABASELINK;
3> This way I am able to implement abstraction , the developer will not be able to know whether the Remote_table_name object is a view from local user account or the actual table from the remote database. Application behaves as if all data are present locally. All good here.
The real problem starts when performance comes into the picture. When running datamining which uses tables on the remote db (now through views with dblink in local database) , I found that some of the jobs are taking 4 to 5 times more time to complete the same task than when the same run is executed on the remote database directly.
I tried couple of things like:
1> Using performance hints while creating views for the: e.g. remote tables in the local account e.g.
CREATE VIEW "REMOTE_TABLE_NAME" AS SELECT /*+DRIVING_SITE(REMOTE_TABLE_NAME) */ * FROM REMOTE_TABLE_NAME@DBLINK;
2> Using Performance hints which querying the remote database tables through views in the local account e.g.
SELECT /*+DRIVING_SITE(REMOTE_TABLE_NAME) */ * FROM REMOTE_TABLE_NAME; // HERE 'REMOTE_TABLE_NAME' is the view in the local database account
3> Using Performance hints which querying the remote database tables by hadcoding dblink with the table namee.g.
SELECT /*+DRIVING_SITE(REMOTE_TABLE_NAME) */ * FROM REMOTE_TABLE_NAME@DBLINK; // HERE 'REMOTE_TABLE_NAME' is the name of the table in the remote account
I tried many more things but could not observe performance improvement.
Any suggestions ??
Thanks ,
Amrit Pandey