How can index on the remote database be used ? [message #370562] |
Tue, 21 December 1999 04:38 |
Indra
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
<CONDITION> .....
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.
Indra
|
|
|
Re: How can index on the remote database be used ? [message #370566 is a reply to message #370562] |
Wed, 22 December 1999 04:58 |
hmg
Messages: 40 Registered: March 1999
|
Member |
|
|
Hi,
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.
Bye
|
|
|