Recreate Lob table or big table in PARALLEL

Recreating LOB/Big Tables in Parallel

This section provides steps to recreate a large table, potentially containing LOBs, in parallel for improved performance. This is useful for reorganizing data, applying new storage attributes, or simply moving data efficiently.

Source Table: APP_USER.APP_TABLE1 Target Table: APP_USER.APP_TABLE2

Step 1: Create a Sequence (for Logging Timing)

Create a sequence to generate unique IDs for logging the start and end times of the operation.

CREATE SEQUENCE Myuser.T_SQ
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

-- Create a table to log the job times (if it doesn't exist)
CREATE TABLE Myuser.job_time (
    t_id    NUMBER,
    t_name  VARCHAR2(100),
    t_type  VARCHAR2(10),
    t_time  DATE
);

Step 2: Create a Procedure

Create a PL/SQL procedure that performs the parallel insert operation and logs its timing.

CREATE OR REPLACE PROCEDURE Myuser.my_proc AS
  v_seq NUMBER;
BEGIN
  -- Enable parallel processing for the current session
  EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_FORCE_LOCAL=TRUE';
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

  -- Get the next sequence value for logging
  SELECT Myuser.T_SQ.NEXTVAL INTO v_seq FROM dual;

  -- Log the start time of the operation
  INSERT INTO Myuser.job_time (t_id, t_name, t_type, t_time) VALUES (v_seq, 'APP_TABLE1', 'START', SYSDATE);
  COMMIT;

  -- Perform the parallel insert from source to target table
  -- APPEND hint for direct path insert, PARALLEL hint for parallel execution
  INSERT /*+ APPEND PARALLEL(A,60) */ INTO APP_USER.APP_TABLE2 A
  SELECT /*+ PARALLEL(B,60) */ * FROM APP_USER.APP_TABLE1 B;
  COMMIT;

  -- Log the end time of the operation
  INSERT INTO Myuser.job_time (t_id, t_name, t_type, t_time) VALUES (v_seq, 'APP_TABLE1', 'END', SYSDATE);
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    -- Log any errors
    INSERT INTO Myuser.job_time (t_id, t_name, t_type, t_time) VALUES (v_seq, 'APP_TABLE1', 'ERROR', SYSDATE);
    COMMIT;
    RAISE; -- Re-raise the exception after logging
END;
/

Note:

• PARALLEL(A,60) and PARALLEL(B,60) hints suggest using 60 parallel slaves. Adjust this number based on your system's CPU cores and I/O capabilities.

• APPEND hint performs a direct-path insert, which is faster for large data volumes as it bypasses the buffer cache.

• Ensure that APP_USER.APP_TABLE2 is already created with the desired structure (including LOB segments if APP_TABLE1 has them) and any necessary indexes or constraints are handled separately.

Step 3: Execute Procedure

Execute the procedure to start the parallel table recreation process.

EXEC Myuser.my_proc;

After execution, you can query Myuser.job_time to check the start and end times of the operation.

This comprehensive overview should provide a solid understanding of Oracle's Multitenant Architecture and its evolution across recent database releases.