Managing Statement Transparency

The database allows the following standard DML statements to reference remote tables:

  • SELECT (queries)

  • INSERT

  • UPDATE

  • DELETE

  • SELECT...FOR UPDATE (not always supported in Heterogeneous Systems)

  • LOCK TABLE

Queries including joins, aggregates, subqueries, and SELECT...FOR UPDATE can reference any number of local and remote tables and views. For example, the following query joins information from two remote tables:

SELECT e.empno, e.ename, d.dname 
  FROM scott.emp@sales.division3.acme.com e, jward.dept@hq.acme.com d 
  WHERE e.deptno = d.deptno;

In a homogeneous environment, UPDATE, INSERT, DELETE, and LOCK TABLE statements can reference both local and remote tables. No programming is necessary to update remote data. For example, the following statement inserts new rows into the remote table emp in the scott.sales schema by selecting rows from the emp table in the jward schema in the local database: 

INSERT INTO scott.emp@sales.division3.acme.com 
  SELECT * FROM jward.emp;

Restrictions for Statement Transparency:

Several restrictions apply to statement transparency.

  • Data manipulation language statements that update objects on a remote non-Oracle Database system cannot reference any objects on the local Oracle Database. For example, a statement such as the following will cause an error to be raised:

    INSERT INTO remote_table@link as SELECT * FROM local_table;

  • Within a single SQL statement, all referenced LONG and LONG RAW columns, sequences, updated tables, and locked tables must be located at the same node.

  • The database does not allow remote DDL statements (for example, CREATE, ALTER, and DROP) in homogeneous systems except through remote execution of procedures of the DBMS_SQL package, as in this example:

    DBMS_SQL.PARSE@link_name(crs, 'drop table emp', v7);

    Note that in Heterogeneous Systems, a pass-through facility lets you execute DDL.

  • The LIST CHAINED ROWS clause of an ANALYZE statement cannot reference remote tables.

  • In a distributed database system, the database always evaluates environmentally-dependent SQL functions such as SYSDATE, USER, UID, and USERENV with respect to the local server, no matter where the statement (or portion of a statement) executes.

    Note:

    Oracle Database supports the USERENV function for queries only.

  • A number of performance restrictions relate to access of remote objects:

    • Remote views do not have statistical data.

    • Queries on partitioned tables may not be optimized.

    • No more than 20 indexes are considered for a remote table.

    • No more than 20 columns are used for a composite index.

  • There is a restriction in the Oracle Database implementation of distributed read consistency that can cause one node to be in the past with respect to another node. In accordance with read consistency, a query may end up retrieving consistent, but out-of-date data. See "Managing Read Consistency" to learn how to manage this problem.

    See Also:

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