Working with database links
Database links may not be a thing to be bothered about with no, or very meagre use of remote
queries. But these are handle-with-care stuffs when used enormously. What points should be
considered for performance when writing a query involving dblink?
*What Oracle does when you use dblink*
- Oracle verifies if the query has all tables with dblinks. If yes, then the entire query is sent to the remote database for execution. If not, then oracle decomposes the sql statement (splits the statement to run independently in local and remote database).
- If the sql statement references multiple tables, then the optimizer must determine which columns belong to which tables before it can decompose the sql statement. The optimizer collects the data dictionary information of all remote tables.
- The remote sql query is sent to the remote database for execution. The data from both tables is joined locally. All this is done automatically and transparently for the user or application.
*Best practices*
- Consider using separate queries for remote and local tables, rather than a single query.
- Consider creating a view or a routine in the remote database. Analyse the network traffic involved when the query is run locally as well as remotely. If the master table is local and transaction table is remote, it is better to execute query remotely, as master data will be sent across the network. Note that results will be sent back to the local database.
- Using hints in Rule-based environment will force CBO. CBO supports hints for both local and remote database.
- In RBO, the remote query will not use indexes. In CBO, Indexes are considered in choosing an execution path.
- If using CBO, consider Materialized views. Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was created on 16-dec-2001 and last updated on 16-dec-2001.
please forward all queries to amar_padhi@fastmail.fm