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)andPARALLEL(B,60)hints suggest using 60 parallel slaves. Adjust this number based on your system's CPU cores and I/O capabilities.APPENDhint performs a direct-path insert, which is faster for large data volumes as it bypasses the buffer cache.Ensure that
APP_USER.APP_TABLE2is already created with the desired structure (including LOB segments ifAPP_TABLE1has 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.
