Using the ONLINE keyword in DETACH operations

The ONLINE keyword instructs the database server to commit the ALTER FRAGMENT ... DETACH work internally, if there are no errors, and to apply an intent exclusive lock to the table from which the fragment was detached, rather than an exclusive lock. An exclusive lock is applied to the table that is created from the detached fragment.

You can use the DETACH option to the ALTER FRAGMENT ONLINE ON TABLE statement only for a table that uses a range interval fragmentation scheme.

A table that uses a range interval storage distribution scheme can have two types of fragments:
  • range fragments, which are defined by the user in the FRAGMENT BY or PARTITION BY clause of the CREATE TABLE or ALTER TABLE statement, and
  • interval fragments, which are generated automatically by the database server during INSERT and UPDATE operations, if a row has a fragment key values above the upper limit of the transition fragment (the last range fragment).
Only an interval fragment can be detached in an ONLINE DETACH operation.

If the detached interval fragment that is not the last fragment, the database server modifies the fragment names for any system-generated interval fragments that follow the detached fragment in the fragment list to match their new sysfragments.evalpos values in the surviving table. During this fragment renaming operation, an exclusive lock is placed on the fragments while the sysfragments system catalog is being updated with the new partition names (and with new evalpos values for any fragments whose ordinal positions within the fragment list changed during the ALTER FRAGMENT DETACH operation).

All indexes on the surviving table must have the same fragmentation scheme as the table. (That is, any indexes must be attached.) For this reason, if there is a primary key constraint or other referential constraints on the table, it is recommended that you first create an attached index for the constraint, and then use the ALTER TABLE statement to add the constraint. (By default, system-created indexes for primary key constraint and for other referential constraints are detached.)

If there are sessions accessing the same partition that is being detached, it is recommended that you issue the SET LOCK MODE TO WAIT statement for enough seconds to prevent nonexclusive access errors.

All other restrictions that apply to the DETACH option also apply to ONLINE DETACH operations. For those restrictions, see Restrictions on the ALTER FRAGMENT Statement and DROP Clause.

Example of ALTER FRAGMENT ONLINE ... DETACH

The following SQL statements define a fragmented employee table that uses a range-interval storage distribution scheme, with a unique index employee_id_idx on the column emp_id (that is also the fragmentation key) and another index employee_dept_idx on the column dept_id. 

CREATE TABLE employee (emp_id INTEGER, name CHAR(32), dept_id CHAR(2), 
             mgr_id INTEGER, ssn CHAR(12))
  FRAGMENT BY RANGE (emp_id) 
    INTERVAL (100) STORE IN (dbs1, dbs2, dbs3, dbs4)
      PARTITION p0 VALUES < 200 IN dbs1,
      PARTITION p1 VALUES < 400 IN dbs2;
CREATE UNIQUE INDEX employee_id_idx ON employee(emp_id);
CREATE INDEX employee_dept_idx ON employee(dept_id);

INSERT INTO employee VALUES (401, "Susan", "DV", 101, "123-45-6789");
INSERT INTO employee VALUES (601, "David", "QA", 104, "987-65-4321");
The last two statements insert rows with fragment key values above the upper limit of the transition fragment, causing the database server to generate two new interval fragments, so that the resulting fragment list consists of four fragments:
Fragments in surviving table before ALTER FRAGMENT ONLINE:
p0     VALUES < 200                   - range fragment
p1     VALUES < 400                   - range fragment (transition fragment)
sys_p2 VALUES >= 400 AND VALUES < 500 - interval fragment
sys_p4 VALUES >= 600 AND VALUES < 700 - interval fragment

The following statement returns an error, because the specified fragment to be detached is a range fragment (a fragment that stores rows with fragmentation key values below the transition value of 400). Only interval fragments can be detached online. 

ALTER FRAGMENT ONLINE ON TABLE employee 
   DETACH PARTITION p0 employee3;

The following statement runs successfully, and creates a new employee3 table to store the data in the detached fragment.

ALTER FRAGMENT ONLINE ON TABLE employee 
   DETACH PARTITION sys_p2 employee3;

If there are concurrent sessions accessing sys_p2, set the lock mode to WAIT (for a number of seconds sufficient for the ONLINE DETACH operation to be committed) to prevent nonexclusive access errors:

SET LOCK MODE TO WAIT 300;
ALTER FRAGMENT ONLINE ON TABLE employee DETACH PARTITION sys_p2 employee3;

Fragments in surviving table after ALTER FRAGMENT ONLINE:
p0     VALUES < 200                   - range fragment
p1     VALUES < 400                   - range fragment
sys_p4 VALUES >= 600 AND VALUES < 700 - interval fragment.