Consider the following statement where you create a table in the Oracle database with 10 rows:
CREATE TABLE T1 (C1 number);
Analyze the table using the DBMS_STATS package. For example:
DBMS_STATS.GATHER_TABLE_STATS ('SCOTT','T1');
DBMS_STATS.GENERATE_STATS ('SCOTT','T1');
The preceding example assumes the schema name is SCOTT and the table name is T1. See the Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_STATS package.
Create a table in the non-Oracle system with 1000 rows.
Issue the following SQL statement:
SELECT a.* FROM remote_t1@remote_db a, T1 b
WHERE a.C1 = b.C1;
The Oracle optimizer issues the following SQL statement to the agent:
SELECT C1 FROM remote_t1@remote_db;
This fetches all 1000 rows from the non-Oracle system and performs the join in the Oracle database.
If we add a unique index on the column C1 in the table remote_t1, and issue the same SQL statement again, the agent receives the following SQL statement for each value of C1 in the local t1:
... SELECT C1 FROM remote_t1@remote_db WHERE C1 = ?; ...
Note:
? is the bind parameter marker. Also, join predicates containing bind variables generated by Oracle are generated only for nested loop join methods.
To verify the SQL execution plan, generate an explain plan for the SQL statement. First, load utlxplan in the admin directory.
Enter the following:
EXPLAIN PLAN FOR SELECT a.* FROM remote_t1@remote_db a, T1 b
WHERE a.C1 = b.C1;
Execute the utlxpls utility script by entering the following statement.
@utlxpls
OPERATION REMOTE indicates that remote SQL is being referenced.
To find out what statement is sent, enter the following statement:
SELECT ID, OTHER FROM PLAN_TABLE WHERE OPERATION = 'REMOTE';