Oracle7 Server Distributed Systems Volume I: Distributed Data

Query and Update Transparency

Oracle allows the following standard DML statements to reference remote data:

SELECT (queries)

INSERT

UPDATE

DELETE

SELECT...FOR UPDATE

LOCK TABLE

A query, 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 empno, ename, dname

  FROM scott.emp@sales.acme.com a,

	jward.dept@hq.acme.com b

  WHERE a.deptno = b.deptno;

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 SCOTT.EMP in the SALES database by selecting rows from the JWARD.EMP table in the local database:

INSERT INTO scott.emp@sales.division3.acme.com

  SELECT * FROM jward.emp;

Restrictions

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. Oracle does not allow remote Data Definition Language (DDL) statements (for example, CREATE, ALTER, and DROP). Also, the LIST CHAINED ROWS clause of an ANALYZE statement cannot reference remote tables.