31 Developing Applications for a Distributed Database System

Managing the Distribution of Application Data

In a distributed database environment, coordinate with the database administrator to determine the best location for the data. Some issues to consider are:

• Number of transactions posted from each location

• Amount of data (portion of table) used by each node

• Performance characteristics and reliability of the network

• Speed of various nodes, capacities of disks

• Importance of a node or link when it is unavailable

• Need for referential integrity among tables

Controlling Connections Established by Database Links

When a global object name is referenced in a SQL statement or remote procedure call, database links establish a connection to a session in the remote database on behalf of the local user. The remote connection and session are only created if the connection has not already been established previously for the local user session.

The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application or user explicitly terminates them. Note that when you issue a SELECT statement across a database link, a transaction lock is placed on the undo segments. To rerelease the segment, you must issue a COMMIT or ROLLBACK statement.

Terminating remote connections established using database links is useful for disconnecting high cost connections that are no longer required by the application. You can terminate a remote connection and session using the ALTER SESSION statement with the CLOSE DATABASE LINK clause. For example, assume you issue the following transactions:

SELECT * FROM emp@sales;
COMMIT;

The following statement terminates the session in the remote database pointed to by the sales database link:

ALTER SESSION CLOSE DATABASE LINK sales;

To close a database link connection in your user session, you must have the ALTER SESSION system privilege.

Maintaining Referential Integrity in a Distributed System

If a part of a distributed statement fails, for example, due to an integrity constraint violation, the database returns error number ORA-02055. Subsequent statements or procedure calls return error number ORA-02067 until a ROLLBACK or ROLLBACK TO SAVEPOINT is issued.

Design your application to check for any returned error messages that indicate that a portion of the distributed update has failed. If you detect a failure, you should roll back the entire transaction before allowing the application to proceed.

The database does not permit declarative referential integrity constraints to be defined across nodes of a distributed system. In other words, a declarative referential integrity constraint on one table cannot specify a foreign key that references a primary or unique key of a remote table. Nevertheless, you can maintain parent/child table relationships across nodes using triggers.

If you decide to define referential integrity across the nodes of a distributed database using triggers, be aware that network failures can limit the accessibility of not only the parent table, but also the child table. For example, assume that the child table is in the sales database and the parent table is in the hq database. If the network connection between the two databases fails, some DML statements against the child table (those that insert rows into the child table or update a foreign key value in the child table) cannot proceed because the referential integrity triggers must have access to the parent table in the hq database.

Tuning Distributed Queries

The local Oracle Database server breaks the distributed query into a corresponding number of remote queries, which it then sends to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.

You have several options for designing your application to optimize query processing. This section contains the following topics:

• Using Collocated Inline Views

• Using Cost-Based Optimization

• Using Hints

• Analyzing the Execution Plan

CREATE TABLE AS (
                 SELECT l.a, l.b, r1.c, r1.d, r1.e, r2.b, r2.c
                 FROM local l, remote1 r1, remote2 r2 
                    WHERE l.c = r.c 
                    AND r1.c = r2.c 
                    AND r.e > 300
                );

CREATE TABLE AS (
                 SELECT l.a, l.b, v.c, v.d, v.e
                 FROM (
                        SELECT r1.c, r1.d, r1.e, r2.b, r2.c
                         FROM remote1 r1, remote2 r2
                         WHERE r1.c = r2.c 
                         AND r1.e > 300
                      ) v, local l
                 WHERE l.c = r1.c
                );

ALTER SESSION OPTIMIZER_MODE = CHOOSE;
ALTER SESSION OPTIMIZER_MODE = COST;

ANALYZE TABLE remote@remote.com COMPUTE STATISTICS; 

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS ('scott', 'dept');
END;

/* with argument */

SELECT /*+NO_MERGE(v)*/ t1.x, v.avg_y
   FROM t1, (SELECT x, AVG(y) AS avg_y FROM t2 GROUP BY x) v,
   WHERE t1.x = v.x AND t1.y = 1;

/* in query block */

SELECT t1.x, v.avg_y
   FROM t1, (SELECT /*+NO_MERGE*/ x, AVG(y) AS avg_y FROM t2 GROUP BY x) v,
   WHERE t1.x = v.x AND t1.y = 1;

SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, dept@remote.com
   WHERE emp.deptno = dept.deptno;

SQL> @UTLXPLAN.SQL

EXPLAIN PLAN FOR
   SELECT d.dname 
   FROM dept d
      WHERE d.deptno 
      IN (SELECT deptno 
          FROM emp@orc2.world
          GROUP BY deptno
          HAVING COUNT (deptno) >3
          )
/

@UTLXPLS.SQL

Plan Table
-------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------
| SELECT STATEMENT          |          |       |      |        |       |      |
|  NESTED LOOPS             |          |       |      |        |       |      |
|   VIEW                    |          |       |      |        |       |      |
|    REMOTE                 |          |       |      |        |       |      |
|   TABLE ACCESS BY INDEX RO|DEPT      |       |      |        |       |      |
|    INDEX UNIQUE SCAN      |PK_DEPT   |       |      |        |       |      |
-------------------------------------------------------------------------------

SELECT OTHER 
FROM PLAN_TABLE 
   WHERE operation = 'REMOTE';

SELECT DISTINCT "A1"."DEPTNO" FROM "EMP" "A1" 
   GROUP BY "A1"."DEPTNO" HAVING COUNT("A1"."DEPTNO")>3

SET LONG 9999999 

Handling Errors in Remote Procedures

When the database executes a procedure locally or at a remote location, four types of exceptions can occur:

• PL/SQL user-defined exceptions, which must be declared using the keyword EXCEPTION

• PL/SQL predefined exceptions such as the NO_DATA_FOUND keyword

• SQL errors such as ORA-00900 and ORA-02015

• Application exceptions generated using the RAISE_APPLICATION_ERROR() procedure

When using local procedures, you can trap these messages by writing an exception handler such as the following

BEGIN
  ...
EXCEPTION
   WHEN ZERO_DIVIDE THEN
   /* ... handle the exception */
END;

Notice that the WHEN clause requires an exception name. If the exception does not have a name, for example, exceptions generated with RAISE_APPLICATION_ERROR, you can assign one using PRAGMA_EXCEPTION_INIT. For example:

DECLARE
  null_salary EXCEPTION;
  PRAGMA EXCEPTION_INIT(null_salary, -20101);
BEGIN
  ...
  RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
...
EXCEPTION
  WHEN null_salary THEN
  ...
END;

When calling a remote procedure, exceptions can be handled by an exception handler in the local procedure. The remote procedure must return an error number to the local, calling procedure, which then handles the exception as shown in the previous example. Note that PL/SQL user-defined exceptions always return ORA-06510 to the local procedure.

Therefore, it is not possible to distinguish between two different user-defined exceptions based on the error number. All other remote exceptions can be handled in the same manner as local exceptions.