1. Using Oracle Sharding
  2. Query and DML Processing
  3. Supported DMLs and Examples

Supported DMLs and Examples

DMLs in Oracle sharding can target either duplicated tables or sharded tables. There are no limitations on DMLs when the target is a duplicated table.

DMLs (mainly Insert, Update and Delete) targeting sharded tables can be

  • Simple DMLs where only the target table is referenced
  • DMLs referencing other tables
  • Merge statements

Simple DMLs Where Only the Target Table is Referenced

The following are several examples of supported DMLs.

Example 9-13 Update all of the rows

UPDATE employees SET salary = salary *1.1;

Example 9-14 Insert one row

INSERT INTO employees VALUES (102494, 'Jane Doe, ...
    );

Example 9-15 Delete one row

DELETE employees WHERE employee_id = 103678;

DMLs Referencing Other Tables

DMLs on sharded tables can reference other sharded tables, duplicated tables, or local tables.

Example 9-16 DML referencing duplicated table

In this example, employees is a sharded table and ref_jobs is a duplicated table. 

DELETE employees
            WHERE job_id IN (SELECT job_id FROM ref_jobs
                            WHERE job_id = 'SA_REP');

Example 9-17 DML referencing another sharded table

UPDATE departments SET department_name = 'ABC‘
            WHERE department_id IN (SELECT department_id
                                    FROM employees
                                    WHERE salary < 10000);

Example 9-18 Insert as select from a local table

INSERT INTO employees SELECT * FROM local_employees;

Example 9-19 DML affecting one shard

A DML statement might affect only one shard, or it can involve multiple shards. For example, the DELETE statement shown here affects only one shard because there is a predicate on the sharding key (employee_id) in the WHERE clause.. 

DELETE employees WHERE employee_id = 103678;

Example 9-20 DML affecting multiple shards

The following statement affects all of the rows in the EMPLOYEES table because it does not have a WHERE clause. 

UPDATE employees SET salary = salary *1.1;

To run this UPDATE statement on all shards, the shard coordinator iterates over all of the primary shard databases and invokes the UPDATE statement remotely. The coordinator starts a distributed transaction and performs two phase commit to guarantee the consistency of the distributed transaction. If there is an in-doubt transaction, you must recover it manually.

Example Merge Statements

The MERGE statement can target a sharded table or a duplicated table. The merge is allowed as long as the MERGE operation itself can be pushed to the shards.

Example 9-21 Merge statement with sharded table employees as the target table

In this example, the employee_id column is the sharding key, and the join predicate on the source query is on the sharding key, so the MERGE statement will get pushed to all of the shards to be processed. 

MERGE INTO employees D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.salary = D.salary + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.salary)
     VALUES (S.employee_id, S.salary*0.1)
     WHERE (S.salary <= 8000);

Example 9-22 Merge statement with duplicated table as the target table

In this example, the target table is the duplicated table ref_employees. The source query references the sharded table employees and the join predicate is on the sharding key employee_id. 

MERGE INTO ref_employees D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.salary = D.salary + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.salary)
     VALUES (S.employee_id, S.salary*0.1)
      WHERE (S.salary <= 8000);

Limitations in Multi-Shard DML Support

The following DML features are not supported by multi-shard DML in Oracle Sharding.

  • Parallel DML Parallel DML is not supported by multi-shard DML. The DML will always run on one shard at a time (serially) in multi-shard DML.
  • Error Logging The ERROR LOG clause with DML is not supported by multi-shard DML. A user error is raised in this case.
  • Array DML Array DML is not supported by multi-shard DML. ORA-2681 is raised in this cases.
  • RETURNING Clause The RETURNING INTO clause is not supported by regular distributed DMLs; therefore, it is not supported by Oracle Sharding. ORA-22816 is raised if you try to use the RETURNING INTO clause in multi-shard DMLs.
  • MERGE and UPSERT The MERGE statement is partially supported by Oracle Sharding, that is, a MERGE statement affecting only single shard is supported. ORA error is raised if a MERGE statement requires the modification of multiple shards.
  • Multi-Table INSERT Multi-table inserts are not supported by database links; therefore, multi-table inserts are not supported by Oracle Sharding.
  • Updatable Join View ORA-1779 is thrown when the updatable join view has a join on a sharded table on sharding keys. The reason for this error is that the primary key defined on a sharded table is combination of internal column SYS_HASHVAL + sharding key and you cannot specify SYS_HASHVAL in the updatable join view. Because of this restriction you cannot establish the key-preserved table resulting in raising ORA-1779.
  • Triggers