Home » RDBMS Server » Server Administration » How can index on the remote database be used ?
How can index on the remote database be used ? [message #370562] Tue, 21 December 1999 04:38 Go to next message
Messages: 6
Registered: December 1999
Junior Member
Hi All,
I have a problem and need help :

query 1 :
select a.field1 from table1@databaselink a,
tabel2@databaselink b,
tabel3 c where <CONDITION>

my question to query 1 is why only the index
for table3 on local database that is being used ?, however if I write my query like this :

query 2:
select a.field1 from table1@databaselink a,
tabel2@databaselink b,
tabel3@databaselink c where

then all the indexes on the remote database are
used. Now how can I write a sql query to make use of the indexes both in local and remote database? Is there any special command need to add in query 1 to tell the remote tables to use their indexes ? Please help. Thanks in advance.

Re: How can index on the remote database be used ? [message #370566 is a reply to message #370562] Wed, 22 December 1999 04:58 Go to previous message
Messages: 40
Registered: March 1999

look in the Online Docu:
Oracle 8 Tuning, Chapter 9, Tuning distributed Queries.

May be you find something. Here are some extracts:

Remote and Distributed Queries
If a SQL statement references one or more remote tables, the optimizer first determines whether all remote tables are located at the same site. If all tables are
located at the same remote site, Oracle sends the entire query to the remote site for execution. The remote site sends the resulting rows back to the local site. This is
called a remote SQL statement. If the tables are located at more than one site, the optimizer decomposes the query into separate SQL statements to access each of
the remote tables. This is called a distributed SQL statement. The site where the query is executed, called the "driving site," is normally the local site.

Rule-Based Optimization
Rule-based optimization does not have information about indexes for remote tables. It never, therefore, generates a nested loops join between a local table and a
remote table with the local table as the outer table in the join. It uses either a nested loops join with the remote table as the outer table or a sort merge join,
depending on the indexes available for the local table.

Cost-Based Optimization
Cost-based optimization can consider more execution plans than rule-based optimization. Cost-based optimization knows whether indexes on remote tables are
available, and in which cases it would make sense to use them. Cost-based optimization considers index access of the remote tables as well as full table scans,
whereas rule-based optimization considers only full table scans.

Previous Topic: Sort Key too long
Next Topic: SQL Help
Goto Forum:

Current Time: Sat Oct 31 21:01:18 CDT 2020