Skip Headers
Oracle® Database Heterogeneous Connectivity User's Guide
11g Release 2 (11.2)

Part Number E11050-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Features of Oracle Database Gateways

This chapter describes the major features provided by Oracle Database gateways. Descriptions of these features are in the following topics:

3.1 SQL and PL/SQL Support

SQL statements are translated and data types are mapped according to capabilities. PL/SQL calls are mapped to non-Oracle system stored procedures. With SQL statements, if functionality is missing at the remote system, then either a simpler query is issued or the statement is broken up into multiple queries. Then, the desired results are obtained by postprocessing in the Oracle database.

Even though Heterogeneous Services can, for the most part, incorporate non-Oracle systems into Oracle distributed sessions, there are several limitations to this. Some of the generic limitations are:

3.2 Heterogeneous Replication

Data can be replicated between a non-Oracle system and Oracle Database using materialized views.

Note:

There is another means of replicating information between Oracle and non-Oracle databases called Oracle Streams.

For information about using Oracle Streams, see Oracle Streams Concepts and Administration.

Materialized views instantiate data captured from tables at the non-Oracle master site at a particular time. This instant is defined by a refresh operation, which copies this data to Oracle Database and synchronizes the copy on the Oracle system with the master copy on the non-Oracle system. The materialized data is then available as a view on Oracle Database.

Replication facilities provide mechanisms to schedule refreshes and to collect materialized views into replication groups to facilitate their administration. Refresh groups permit refreshing multiple materialized views as if they were a single object.

Heterogeneous replication support is necessarily limited to a subset of the full Oracle-to-Oracle replication functionality:

Other restrictions apply to any access to non-Oracle data through Oracle's Heterogeneous Services facilities. The most important of these are:

The following examples illustrate basic setup and use of three materialized views to replicate data from a non-Oracle system to an Oracle data store.

Note:

For the following examples, remote_db refers to the non-Oracle system that you are accessing from Oracle Database.

Modify these examples for your environment. Do not try to execute them as they are written.

Example 1: Create Materialized Views for Heterogeneous Replication

This example creates three materialized views that are then used in subsequent examples.

  1. Create a primary key materialized view of table customer@remote_db.

CREATE MATERIALIZED VIEW pk_mv REFRESH COMPLETE AS
      SELECT * FROM customer@remote_db WHERE "zip" = 94555;
  1. Create a subquery materialized view of tables orders@remote_db and customer@remote_db.

CREATE MATERIALIZED VIEW sq_mv REFRESH COMPLETE AS
      SELECT * FROM orders@remote_db o WHERE EXISTS
        (SELECT c."c_id" FROM customer@remote_db c
           WHERE c."zip" = 94555 and c."c_id"  = o."c_id" );
  1. Create a complex materialized view of data from multiple tables on remote_db.

CREATE MATERIALIZED VIEW cx_mv
      REFRESH COMPLETE AS
      SELECT  c."c_id", o."o_id"
        FROM customer@remote_db c,
             orders@remote_db o,
             order_line@remote_db ol
        WHERE c."c_id" = o."c_id"
        AND o."o_id" = ol."o_id";

Example 2: Set Up a Refresh Group for Heterogeneous Replication

BEGIN
  dbms_refresh.make('refgroup1',
   'pk_mv, sq_mv, cx_mv',
   NULL, NULL);
 END;
 /

Example 3: Force Refresh of All Three Materialized Views

BEGIN
   dbms_refresh.refresh('refgroup1');
END;
 /

See Also:

Oracle Database Advanced Replication for a complete description of materialized views and replication facilities

3.3 Passthrough SQL

The passthrough SQL feature enables you to send a statement directly to a non-Oracle system without first being interpreted by Oracle Database. This feature can be useful if the non-Oracle system allows for operations in statements for which there is no equivalent in Oracle.

This section contains the following topics:

3.3.1 Using the DBMS_HS_PASSTHROUGH Package

You can execute passthrough SQL statements directly on the non-Oracle system using the PL/SQL package DBMS_HS_PASSTHROUGH. Any statement executed with this package is executed in the same transaction as standard SQL statements.

The DBMS_HS_PASSTHROUGH package is a virtual package. It conceptually resides on the non-Oracle system. In reality, however, calls to this package are intercepted by Heterogeneous Services and are mapped to one or more Heterogeneous Services calls. The driver then maps these Heterogeneous Services calls to the API of the non-Oracle system. The client application invokes the procedures in the package through a database link in the same way as it would invoke a non-Oracle system stored procedure. The special processing done by Heterogeneous Services is transparent to the user.

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about this package

3.3.2 Considering the Implications of Using Passthrough SQL

When you execute a passthrough SQL statement that implicitly commits or rolls back a transaction in a non-Oracle system, the transaction is affected. For example, some systems implicitly commit the transaction containing a data definition language (DDL) statement. Because Oracle Database is bypassed, Oracle Database is unaware that a transaction was committed in the non-Oracle system. Consequently, the data at the non-Oracle system can be committed, while the transaction in Oracle Database is not.

If the transaction in Oracle Database is rolled back, data inconsistencies between Oracle Database and the non-Oracle system can occur. This situation results in global data inconsistency.

Note that if the application executes a typical COMMIT statement, Oracle Database can coordinate the distributed transaction with the non-Oracle system. The statement executed with the passthrough facility is part of the distributed transaction.

3.3.3 Executing Passthrough SQL Statements

The following table shows the functions and procedures provided by the DBMS_HS_PASSTHROUGH package that enable you to execute passthrough SQL statements.

Procedure/Function Description
OPEN_CURSOR Opens a cursor.
CLOSE_CURSOR Closes a cursor.
PARSE Parses the statement.
BIND_VARIABLE Binds IN variables.
BIND_OUT_VARIABLE Binds OUT variables.
BIND_INOUT_VARIABLE Binds IN OUT variables.
EXECUTE_NON_QUERY Executes a nonquery statement.
EXECUTE_IMMEDIATE Executes a nonquery statement without bind variables.
FETCH_ROW Fetches rows from query.
GET_VALUE Retrieves column value from SELECT statement or retrieves OUT bind parameters.

3.3.3.1 Executing Nonqueries

Nonqueries include the following statements and types of statements:

  • INSERT

  • UPDATE

  • DELETE

  • DDL

To execute nonquery statements, use the EXECUTE_IMMEDIATE function. For example, to execute a DDL statement on a non-Oracle system that you can access using the database link salesdb, enter the following:

DECLARE
  num_rows INTEGER;

BEGIN
  num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@salesdb
            ('CREATE TABLE dept1 (n SMALLINT, loc CHARACTER(10))');
END;

The variable num_rows is assigned the number of rows affected by the statements that were executed. For DDL statements, zero is returned. Note that you cannot execute a query with EXECUTE_IMMEDIATE function and you cannot use bind variables.

3.3.3.1.1 Using Bind Variables: Overview

Bind variables let you use the same SQL statement multiple times with different values, reducing the number of times a SQL statement needs to be parsed. For example, when you insert four rows in a table, you can parse the SQL statement once, and bind and execute the SQL statement for each row. One SQL statement can have zero or more bind variables.

To execute passthrough SQL statements with bind variables, you must:

  1. Open a cursor.

  2. Parse the SQL statement on the non-Oracle system.

  3. Bind the variables.

  4. Execute the SQL statement on the non-Oracle system.

  5. Close the cursor.

Figure 3-1 shows the flow diagram for executing nonqueries with bind variables.

Figure 3-1 Flow Diagram for Nonquery Passthrough SQL

Description of Figure 3-1 follows
Description of "Figure 3-1 Flow Diagram for Nonquery Passthrough SQL"

3.3.3.1.2 Using IN Bind Variables

The syntax of the non-Oracle system determines how a statement specifies a bind variable. For example, on an Oracle system you define bind variables with a preceding colon. For example:

...
UPDATE emp
SET sal=sal*1.1
WHERE ename=:ename;
...

In this statement, ename is the bind variable. On non-Oracle systems, you may need to specify bind variables with a question mark. For example:

...
UPDATE emp
SET sal=sal*1.1
WHERE ename= ?;
...

In the bind variable step, you must positionally associate host program variables (in this case, PL/SQL) with each of these bind variables. For example, to execute the preceding statement, use the following PL/SQL program:

DECLARE
  c INTEGER;
  nr INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb;
  DBMS_HS_PASSTHROUGH.PARSE@salesdb(c,
        'UPDATE emp SET SAL=SAL*1.1 WHERE ename=?');
  DBMS_HS_PASSTHROUGH.BIND_VARIABLE@salesdb(c,1,'JONES');
  nr:=DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@salesdb(c);
  DBMS_OUTPUT.PUT_LINE(nr||' rows updated');
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c);
END;
3.3.3.1.3 Using OUT Bind Variables

The non-Oracle system can support OUT bind variables. With OUT bind variables, the value of the bind variable is not known until after the execution of the SQL statement.

Although OUT bind variables are populated after executing the SQL statement, the non-Oracle system must know that the particular bind variable is an OUT bind variable before the SQL statement is executed. You must use the BIND_OUT_VARIABLE procedure to specify that the bind variable is an OUT bind variable.

After executing the SQL statement, you can retrieve the value of the OUT bind variable using the GET_VALUE procedure.

3.3.3.1.4 Using IN OUT Bind Variables

A bind variable can be both an IN and an OUT variable. This means that the value of the bind variable must be known before executing the SQL statement, but you can change the value after the SQL statement is executed.

For IN OUT bind variables, you must use the BIND_INOUT_VARIABLE procedure to provide a value before executing the SQL statement. After executing the SQL statement, you must use the GET_VALUE procedure to retrieve the new value of the bind variable.

3.3.3.2 Executing Queries

The difference between queries and nonqueries is that queries retrieve a result set from a SELECT statement. The result set is retrieved by using a cursor.

Figure 3-2 illustrates the steps in a passthrough SQL query. After the system parses the SELECT statement, each row of the result set can be retrieved with the FETCH_ROW procedure. After the row is retrieved, use the GET_VALUE procedure to retrieve the selected list of items into program variables. After all rows are retrieved, you can close the cursor.

Figure 3-2 Passthrough SQL for Queries

Description of Figure 3-2 follows
Description of "Figure 3-2 Passthrough SQL for Queries"

You do not have to retrieve all the rows. You can close the cursor at any time after opening the cursor.

Note:

Although you are retrieving one row at a time, Heterogeneous Services optimizes the round-trips between Oracle Database and the non-Oracle system by buffering multiple rows and fetching from the non-Oracle data system in one round-trip.

The following example executes a query:

DECLARE
   val  VARCHAR2(100);
   c    INTEGER;
   nr   INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@salesdb; 
  DBMS_HS_PASSTHROUGH.PARSE@salesdb(c, 
    'select ENAME
     from   EMP
     where  DEPTNO=10');
  LOOP
    nr := DBMS_HS_PASSTHROUGH.FETCH_ROW@salesdb(c);
    EXIT WHEN nr = 0;
    DBMS_HS_PASSTHROUGH.GET_VALUE@salesdb(c, 1, val);
    DBMS_OUTPUT.PUT_LINE(val);
  END LOOP;  
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@salesdb(c); 
END;

After the SELECT statement has been parsed, the rows are fetched and printed in a loop until the FETCH_ROW function returns the value 0.

3.4 Result Set Support

Various relational databases enable stored procedures to return result sets (one or more sets of rows).

Traditionally, database stored procedures worked exactly like procedures in any high-level programming language. They had a fixed number of arguments which could be of types IN, OUT, or IN OUT. If a procedure had n arguments, it could return at most n values as results. However, suppose that you wanted a stored procedure to execute a query such as SELECT * FROM emp and return the results. The emp table might have a fixed number of columns, but there is no way of telling, at procedure creation time, the number of rows it has. Because of this, no traditional stored procedure could be created that returned the results of this type of query. As a result, several relational database vendors added the ability to return results sets from stored procedures, but each relational database returns result sets from stored procedures differently.

Oracle has a data type called a REF CURSOR. Like every other Oracle data type, a stored procedure can take this data type as an IN or OUT argument. With Oracle Database, a stored procedure must have an output argument of type REF CURSOR. It then opens a cursor for a SQL statement and places a handle to that cursor in that output parameter. The caller can then retrieve from the REF CURSOR the same way as from any other cursor.

Oracle Database can do a lot more than return result sets. The REF CURSOR data type can be passed as an input argument to PL/SQL routines to be passed back and forth between client programs and PL/SQL routines or as an input argument between several PL/SQL routines.

This section contains the following topics:

3.4.1 Result Set Support for Non-Oracle Systems

Several non-Oracle systems allow stored procedures to return result sets, but they do so in different ways. Result set support for non-Oracle databases is typically based on one of the following two models.

  • Model 1: Result Set Support

    When creating a stored procedure, you can explicitly specify the maximum number of result sets that can be returned by that stored procedure. While executing, the stored procedure can open anywhere from zero up to its specified maximum number of result sets. After the execution of the stored procedure, a client program gets handles to these result sets by using either an embedded SQL directive or by calling a client library function. After that, the client program can retrieve from the result set in the same way as from a typical cursor.

  • Model 2: Result Set Support

    In this model, there is no specified limit to the number of result sets that can be returned by a stored procedure. Both Model 1 and Oracle Database have a limit. For Oracle Database, the number of result sets returned by a stored procedure can be at most the number of REF CURSOR OUT arguments. For Model 1, the upper limit is specified using a directive in the stored procedure language. Another way that Model 2 differs from Oracle Database and Model 1 is that they do not return a handle to the result sets. Instead, they place the entire result set on the wire when returning from a stored procedure. For Oracle Database, the handle is the REF CURSOR OUT argument. For Model 1, it is obtained separately after the execution of the stored procedure. For both Oracle Database and Model 1, after the handle is obtained, data from the result set is obtained by doing a fetch on the handle; there are several cursors open and the fetch can be in any order. In the case of Model 2, however, all the data is already on the wire, with the result sets coming in the order determined by the stored procedure and the output arguments of the procedures coming at the end. The entire first result set must be retrieved, then the entire second result set, until all of the results are retrieved. Finally, the stored procedure OUT arguments are retrieved.

3.4.2 Heterogeneous Services Support for Result Sets

Result set support exists among non-Oracle databases in different forms. All of these must be mapped to the Oracle REF CURSOR model. Due to the differences in behavior among the non-Oracle systems, Heterogeneous Services result set support acts in one of two different ways depending on the non-Oracle system to which it is connected.

Note the following about Heterogeneous Services result set support:

  • Result set support is part of the Heterogeneous Services generic code, but for the feature to work in a gateway, the driver has to implement it. Not all drivers have implemented result set support and you must verify that your gateway is supported.

  • Heterogeneous Services supports REF CURSOR OUT arguments from stored procedures. IN and IN OUT arguments are not supported.

  • The REF CURSOR OUT arguments are all anonymous reference cursors. REF CURSORs that are returned by Heterogeneous Services do not have types.

3.4.2.1 Results Sets: Cursor Mode

Each result set returned by a non-Oracle system stored procedure is mapped by an Oracle driver to an OUT argument of type REF CURSOR. The client program detects a stored procedure with several OUT arguments of type REF CURSOR. After executing the stored procedure, the client program can fetch from the REF CURSOR the same way as it would from a REF CURSOR returned by an Oracle stored procedure. When connecting to the gateway as described in Section 3.4.1.1, Heterogeneous Services will be in cursor mode.

3.4.2.2 Result Sets: Sequential Mode

There is a maximum number of result sets that a particular stored procedure can return. The number of result sets returned is at most the number of REF CURSOR OUT arguments for the stored procedure. It can return fewer result sets, but it can never return more.

For the system described in Section 3.4.1.2, there is no maximum number of result sets that can be returned. In the case of Model 1 (in Section 3.4.1.1), the maximum number of result sets that a procedure can return is known, and that the driver can return to Heterogeneous Services, is specified in the stored procedure by the number of REF CURSOR OUT arguments. If, when the stored procedure is executed, fewer result sets than the maximum are returned, then the other REF CURSOR OUT arguments are set to NULL.

Another problem for Model 2 database servers is that result sets must be retrieved in the order in which they were placed on the wire by the database. This prevents Heterogeneous Services from running in cursor mode when connecting to these databases. To access result sets returned by these stored procedures, Heterogeneous Services must be in sequential mode.

In sequential mode, the procedure description returned by the driver contains the following:

  • All the input arguments of the remote stored procedure

  • None of the output arguments

  • One OUT argument of type REF CURSOR (corresponding to the first result set returned by the stored procedure)

The client fetches from this REF CURSOR and then calls the virtual package function DBMS_HS_RESULT_SET.GET_NEXT_RESULT_SET to fetch the REF CURSOR corresponding to the next result set. This function call repeats until all result sets are retrieved. The last result set returned will be the OUT arguments of the remote stored procedure.

The primary limitations of sequential mode are:

  • Result sets returned by a remote stored procedure must be retrieved in the order in which they were placed on the wire.

  • When a stored procedure is executed, all result sets returned by a previously executed stored procedure are closed (regardless of whether or not the data was retrieved).

    See Also:

    Your gateway-specific manual for more information about how result sets are supported through the gateway

3.5 Data Dictionary Translations

Most database systems have some form of data dictionary. A data dictionary is a collection of information about the database objects that were created by various users of the system. For a relational database, a data dictionary is a set of tables and views that contain information about the data in the database. This information includes information about the users who are using the system and about the objects that they created (such as tables, views, and triggers). Almost all data dictionaries (regardless of the database system) contain the same information, but each database system organizes the information differently.

For example, the ALL_CATALOG Oracle data dictionary view gives a list of tables, views, and sequences in the database. It has three columns: the first is called OWNER, and it is the name of the owner of the object; the second is called TABLE_NAME, and it is the name of the object; and the third is called TABLE_TYPE, and it is the data type. This field has value TABLE, VIEW, SEQUENCE and so forth depending on the object type. However, in Sybase, the same information is stored in two tables called sysusers and sysobjects whose column names are different from those of the Oracle ALL_CATALOG table. Additionally, in Oracle Database, the table type is a string with a value such as TABLE or VIEW. With Sybase, it is a letter, for example, U means user table; S means system table; V means view, and so forth.

If the client program requires information from the table ALL_CATALOG on a Sybase system, it sends a query referencing ALL_CATALOG@database_link to a gateway. Heterogeneous Services translates this query to an appropriate query on systables and then sends the translated query to the Sybase system, for example:

SELECT SU."name" OWNER, SO."name" TABLE_NAME,
   DECODE(SO."type", 'U ','TABLE', 'S ', 'TABLE', 'V ', 'VIEW')
TABLE_TYPE
FROM "dbo"."sysusers"@remote_db  SU, "dbo"."sysobjects"@remote_db SO
WHERE SU."uid" = SO."uid" AND
   (SO."type" = 'V' OR SO."type" = 'S' OR SO."type" = 'U');

To relay the translation of a query on an Oracle data dictionary table to the equivalent one on the non-Oracle system data dictionary table, Heterogeneous Services needs data dictionary translations for that non-Oracle system. A data dictionary translation is a view definition (essentially a SELECT statement) of one or more non-Oracle system data dictionary tables that look like the Oracle data dictionary table, with the same column names and the same formatting. Most data dictionary translations are not as simple as the preceding example. Often, the information is scattered over many tables, and the data dictionary translation is a complex join of those tables.

In some cases, an Oracle data dictionary table does not have a translation because the information does not exist on the non-Oracle system. In such cases, the gateway must not upload a translation, or the gateway might implement an alternative approach called mimicking. If the gateway mimics a data dictionary table, it informs Heterogeneous Services, and Heterogeneous Services will get the description of the data dictionary table by querying the local database. When asked to retrieve data, it will report that no rows were selected.

3.6 Date-Time Data Types

Oracle Database has five date-time data types:

Heterogeneous Services generic code supports Oracle date-time data types in SQL and stored procedures. Heterogeneous Services does not support these data types in data dictionary translations or queries involving data dictionary translations.

Even though Heterogeneous Services generic code supports date-time data types, support for a particular gateway depends on whether or not the driver for that non-Oracle system implemented date-time support. Support, even when the driver implements it, may be partial because of the limitations of the non-Oracle system. For more information, see your gateway-specific documentation.

You must set the timestamp formats of the non-Oracle system in the gateway initialization file. The parameters to set are HS_NLS_TIMESTAMP_FORMAT and HS_NLS_TIMESTAMP_TZ_FORMAT. You should also set the local time zone for the non-Oracle system in the initialization file by setting HS_TIME_ZONE.

See Also:

Oracle Database SQL Language Reference for information about datetime data types

3.7 Two-Phase Commit Protocol

Heterogeneous Services provides the infrastructure to implement the two-phase commit protocol. The extent to which this is supported depends on the gateway and the remote system. For more information, see your gateway-specific documentation.

See Also:

Oracle Database Administrator's Guide for more information about the two-phase commit protocol

3.8 Piecewise LONG Data Type

Earlier versions of gateways had limited support for the LONG data type. LONG is an Oracle data type that can store up to 2 GB of character data or raw data (LONG RAW). These earlier versions restricted the amount of LONG data to 4 MB because they treated LONG data as a single piece. This caused memory and network bandwidth restrictions on the size of the data that could be handled. Current gateways extended the functionality to support the full 2 GB of heterogeneous LONG data. The gateways now manage the data piecewise between the agent and Oracle Database, eliminating the large memory and network bandwidth requirements.

The HS_LONG_PIECE_TRANSFER_SIZE Heterogeneous Services initialization parameter can be used to set the size of the transferred pieces. For example, consider retrieving 2 GB of LONG data from a heterogeneous source. A smaller piece requires less memory, but it requires more round-trips to retrieve all the data. A larger piece requires fewer round-trips, but it requires a larger amount of memory to store the intermediate pieces internally. The initialization parameter can be used to tune a system for the best performance, that is, for the best trade-off between round-trips and memory requirements. If the initialization parameter is not set, the system uses 64 KB as the default piece size.

Note:

Do not confuse this feature with piecemeal operations on LONG data on the client side. Piecemeal fetch and insert operations on the client side worked with the earlier versions of the gateways, and they continue to do so. The only difference on the client side is that, where earlier versions of the gateways were able to fetch a maximum of 4 MB of LONG data, now they can retrieve the 2 GB of LONG data. This is a significant improvement because 4 MB is only 0.2 percent of the data type's capacity.

3.9 SQL*Plus DESCRIBE Command

You can describe non-Oracle system objects using the SQL*Plus DESCRIBE command. However, there are some limitations. For example, using heterogeneous links, you cannot describe packages, sequences, synonyms, or types.

3.10 Constraints on SQL in a Distributed Environment

This section explains some of the constraints on SQL in a distributed environment. These constraints apply to distributed environments that access non-Oracle systems or remote Oracle databases.

This section contains the following topics:

3.10.1 Remote and Heterogeneous References

Note:

Many of the rules for heterogeneous access also apply to remote references. For more information, see the distributed database section of the Oracle Database Administrator's Guide.

A statement can, with restrictions, be executed on any database node referenced in the statement or the local node. If all objects referenced are resolved to a single, referenced node, Oracle attempts to execute a query at that node. You can force execution at a referenced node by using the /*+ REMOTE_MAPPED */ or /*+ DRIVING_SITE */ hints. If a statement is forwarded to a node other than the node from where the statement was issued, the statement is said to be remote-mapped.

There is complete data type checking support for remote-mapped statements. The result provides consistent data type checking and complete data type coercion.

See Also:

Section 4.5, "Oracle Database Server SQL Construct Processing"

The ways in which statements can be remote-mapped are subject to specific rules or restrictions. If these rules are not followed, an error occurs. As long as the statements issued are consistent with all these rules, the order in which the rules are applied does not matter. See Section 3.10.2 for these rules or restrictions.

Different constraints exist when you are using SQL for remote mapping in a distributed environment. This distributed environment can include remote Oracle databases as well as non-Oracle databases that are accessed through Oracle Database gateways.

3.10.2 Important Restrictions

The following section lists some of the different rules or restrictions that exist when you are using SQL for remote mapping in a distributed environment.

Note:

In the examples that follow, remote_db refers to a remote non-Oracle system while remote_oracle_db refers to a remote Oracle Database.

Rule A: A data definition language statement cannot be remote-mapped.

In Oracle data definition language, the target object syntactically has no place for a remote reference. Data definition language statements that contain remote references are always executed locally. For Heterogeneous Services, this means it cannot directly create database objects in a non-Oracle database using SQL.

However, there is an indirect way using passthrough SQL as shown in the following example:

DECLARE
  num_rows INTEGER;
BEGIN 
  num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@remote_db 
  (
     'create table x1 (c1 char, c2 int)'
  ); 
END; 

Rule B: INSERT, UPDATE and DELETE statements with a remote target table must be remote-mapped.

This rule is more restrictive for non-Oracle remote databases than for a remote Oracle database. This is because the remote system cannot fetch data from the originating Oracle database while executing data manipulation language (DML) statements targeting tables in a non-Oracle system.

For example, to insert all local employees from the local emp table to a remote non-Oracle emp table, use the following statement:

INSERT INTO emp@remote_db SELECT * FROM emp;

This statement is remote-mapped to the remote database. The remote-mapped statement sent to the remote database contains a remote reference back to the originating database for emp. Such a remote link received by the remote database is called a callback link.

Note:

Even though callback links are supported in generic Heterogeneous Services, they may not be implemented in all Heterogeneous Services agents. Refer to your database gateway documentation to determine if callback links work with the database gateway that you are using.

If callback links are not supported by a particular gateway, the previous INSERT statements returns the following error:

ORA-02025: all tables in the SQL statement must be at the remote database

The workaround is to write a PL/SQL block. For example:

DECLARE 
CURSOR remote_insert IS SELECT * FROM emp;
BEGIN 
   FOR rec IN remote_insert LOOP
    INSERT INTO emp@remote_db (empno, ename, deptno) VALUES (
      rec.empno,
      rec.ename, 
      rec.deptno 
    );  
   END loop;
END;

Another special case involves session-specific SQL functions such as USER, USERENV, and SYSDATE. These functions need to be executed at the originating site. A remote-mapped statement containing these functions contains a callback link. For a non-Oracle database for which callbacks are not supported, this can (by default) result in a restriction error.

For example, consider the following statement:

DELETE FROM emp@remote_db WHERE hiredate > sysdate;

The previous statement returns the following error message:

ORA-02070: database REMOTE_DB does not support special functions in this context 

This can be resolved by replacing special functions with a bind variable. For example:

DELETE FROM emp@remote_db WHERE hiredate > :1;

Rule C: Object features like tables with nested table columns, ADT columns, Opaque columns, or Ref Columns cannot be remote-mapped.

Currently, these column types are not supported for heterogeneous access. Hence, this limitation is not directly encountered.

Rule D: SQL statements containing operators and constructs that are not supported at the remote site cannot be remote-mapped.

In the case of an INSERT, UPDATE, or DELETE, this means that the SQL statement cannot be executed (see Rule B). However, you might still be able to execute the SQL statement if the unsupported operator or construct can be executed through a callback link.

In the case of a SELECT, you can always execute a statement affected by this rule as long as none of the remaining rules require the statement to be remote mapped. The SELECT statements affected by this rule are executed by fetching all the necessary data through a remote SELECT operation, and processing the unsupported operator or construct locally using the local SQL engine.

A remote SELECT operation is the operation that retrieves rows from the remote table as opposed to an operation that retrieves data from the local table. In the worse possible case of a remote SELECT, Oracle does a full table scan. A full table scan is a fetch of all the data in the remote table across the network without any filtering (for example, SELECT * FROM EMP).

Full table scans are expensive and, therefore, Oracle attempts to avoid them. If there are indexes on the remote table that can be used, these indexes are used in a WHERE clause predicate to reduce the number of rows fetched across the network.

You can check the SQL statement generated by Oracle Database by explaining the statement and querying the OTHER column of the explain plan table for each REMOTE operation.

See Also:

Section 3.11.1 for more information on how to interpret explain plans with remote references

For example, consider the following statement:

SELECT COUNT(*) FROM emp@remote_db WHERE hiredate < sysdate;

The statement returns the following output:

COUNT(*)  
----------
        14
1 row selected.

The remote table scan is:

SELECT hiredate FROM emp;

The predicate converted to a filter cannot be generated back and passed down to the remote operation because sysdate is not supported by the remote_db or evaluation rules. Thus sysdate must be executed locally.

Note:

Because the remote table scan operation is only partially related to the original query, the number of rows retrieved can be significantly larger than expected and can have a significant impact on performance.

Rule E: SQL statement containing a table expression cannot be remote-mapped.

This limitation is not directly encountered because table expressions are not supported in the heterogeneous access module.

Rule F: If a SQL statement selects LONG data, the statement must be mapped to the node where the table containing the LONG data resides.

Consider the following type of statement:

SELECT long1 FROM table_with_long@remote_db, dual; 

The previous statement returns the following error message (if callback links are not supported):

ORA-02025: all tables in the SQL statement must be at the remote database

Rule G: The statement must be mapped to the node on which the table or tables with columns referenced in the FOR UPDATE OF clause resides when the SQL statement is of form "SELECT...FOR UPDATE OF..."

When the SQL statement is of the form SELECT...FOR UPDATE OF..., the statement must be mapped to the node on which the table or tables with columns referenced in the FOR UPDATE OF clause resides.

For example, consider the following statement:

SELECT ename FROM emp@remote_db WHERE hiredate < sysdate FOR UPDATE OF empno;

The previous statement returns the following error message if it cannot be remote-mapped:

ORA-02070: database REMOTE_DB does not support special functions in this context 

Rule H: If the SQL statement contains a SEQUENCE or sequences, then the statement must be mapped to the site where each sequence resides.

This rule is not encountered for the heterogeneous access module because remote non-Oracle sequences are not supported.

Rule I: If the statement contains a user-defined operator or operators, then the statement must be mapped to the node where each operator is defined.

If the statement contains a user-defined operator, the entire statement needs to be remote-mapped to the database node where the operator is defined.

Rule J: A statement containing duplicate bind variables cannot be remote-mapped.

The workaround for this restriction is to use unique bind variables and bind by number.

3.11 Oracle's Optimizer and Heterogeneous Services

Oracle's optimizer can be used with Heterogeneous Services. Heterogeneous Services collects certain table and index statistics information on the respective non-Oracle system tables and passes this information back to Oracle Database. The Oracle cost-based optimizer uses this information when building the query plan.

There are several other optimizations that the cost-based optimizer performs. The most important ones are remote sort elimination and remote joins.

This section contains the following topics:

3.11.1 Example: Using Index and Table Statistics

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';

3.11.2 Example: Remote Join Optimization

The following is an example of the remote join optimization capability of the Oracle database.

Note:

The explain plan that uses tables from a non-Oracle system can differ from similar statements with local or remote Oracle table scans. This is because of the limitation on the statistics available to Oracle for non-Oracle tables. Most importantly, column selectivity is not available for non-unique indexes of non-Oracle tables. Because of the limitation of the statistics available, the following example is not necessarily what you encounter when doing remote joins and is intended for illustration only.

Consider the following example:

EXPLAIN PLAN FOR
SELECT e.ename, d.dname, f.ename, f.deptno FROM
   dept d,
   emp@remote_db e,
   emp@remote_db f
 WHERE e.mgr = f.empno
  AND e.deptno = d.deptno 
  AND e.empno = f.empno;
  
@utlxpls

You should see output similar to the following:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id    | Operation                           | Name  | Rows  | Bytes  | Cost
| Inst  |IN-OUT|
---------------------------------------------------------------------------
|   0   | SELECT STATEMENT   |                |  2000   |   197K|   205 |
|*  1   | HASH JOIN          |                |  2000   |   197K|
205 |
|   2   | TABLE ACCESS FULL  | DEPT           |  21     |   462 |     2 |
|*  3   | HASH JOIN          |                |  2000   |   154K|
201 |
|   4   | REMOTE             |                |  2000   | 66000 
|    52 |
|   5   | REMOTE             |                |  2000   | 92000
|    52 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Query Block Name / Hint Alias (identified by operation id):
-----------------------------------------------------------

   1 - sel$1 / D
   2 - sel$1 / D
   3 - sel$1 / F
   4 - sel$1 / F
   5 - sel$1 / E

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - access("E"."MGR"="F"."EMPNO" AND "E"."EMPNO"="F"."EMPNO")

Issue the following statement:

SET long 300
SELECT other FROM plan_table WHERE operation = 'REMOTE'; 

You should see output similar to the following:

OTHER
--------------------------------------------------------------------------------

SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"

3.11.3 Optimizer Restrictions for Non-Oracle Access

The following are optimizer restrictions for non-Oracle system access:

  • There are no column statistics for remote objects. This can result in poor execution plans. Verify the execution plan and use hints to improve the plan.

  • There is no optimizer hint to force a remote join. However, there is a remote query block optimization that can be used to rewrite the query slightly in order to get a remote join.

    The example from the previous section can be rewritten to the following form:

    SELECT v.ename, d.dname, d.deptno FROM dept d,
            (SELECT /*+ NO_MERGE */ 
             e.deptno deptno, e.ename ename emp@remote_db e, emp@remote_db f
                 WHERE e.mgr = f.empno
                 AND e.empno = f.empno;
            )
          WHERE v.deptno = d.deptno;
    
    
    

This example guarantees a remote join because it has been isolated in a nested query with the NO_MERGE hint.