Method: CTAS
Copy of Oracle data using CTAS:
- Use NOLOGGING: This
turns off logging, making for a faster copy. However, if you must
recover, you will not be able to roll-forward through this operation.
- Use the parallel clause: Since a table copy does a full table scan of both
tables, parallel query will make the copying far faster, (up to
cpu_count-1 faster).
In this example, we fast copy data
from one table to another table on a 16 CPU server:
create table newtab
parallel 15 nologging
as
select /*+parallel (source 15) */ * from source;
parallel 15 nologging
as
select /*+parallel (source 15) */ * from source;
Method: Insert with
Append and parallel hint
Disable the indexes (mark them unusable)
INSERT /*+ APPEND
*/ INTO TARGET
SELECT /*+ PARALLEL (A 4) */ YOUR_COLS
FROM YOUR_TABLE A
WHERE ALL_CONDITIONS;
Rebuild
the indexes using NOLOGGING
Method: Bulk
collection
/* package header */
CREATE OR REPLACE PACKAGE perfrormance_test AS
PROCEDURE nested_table;
PROCEDURE associative_array;
END perfrormance_test;
/* package body */
CREATE OR REPLACE PACKAGE BODY perfrormance_test AS
PROCEDURE nested_table
AS
/* select all records from source table */
CURSOR big_table_cur IS
SELECT col1
, col2
, col3
FROM big_table;
/* create nested table type and variable that will hold BIG_TABLE's records */
TYPE big_table_ntt IS TABLE OF big_table_cur%ROWTYPE;
l_big_table big_table_ntt;
BEGIN
/* open pointer to SELECT statement */
OPEN big_table_cur;
/* collect data in the collection */
FETCH big_table_cur BULK COLLECT INTO l_big_table;
/* close the pointer */
CLOSE big_table_cur;
/* print size of the collection */
DBMS_OUTPUT.PUT_LINE('Nested table holds: ' || TO_CHAR(l_big_table.COUNT) || ' records.');
/* write data down to target table */
FORALL indx IN l_big_table.FIRST..l_big_table.LAST
INSERT INTO big_table_target(col1, col2, col3)
VALUES (l_big_table(indx).col1, l_big_table(indx).col2, l_big_table(indx).col3);
/*
** or you can use it this way:
**
** VALUES (l_big_table(indx));
*/
/* print number of rows inserted */
DBMS_OUTPUT.PUT_LINE('Number of rows inserted ' || SQL%ROWCOUNT || ' rows');
/* save changes */
COMMIT;
/* or if you want undo changes */
/* ROLLBACK; */
END nested_table;
PROCEDURE associative_array
AS
/* create record (row) type */
TYPE strings_rec IS RECORD
(
one VARCHAR2(4000)
, two VARCHAR2(4000)
, three VARCHAR2(4000)
);
/* create collection of records: type and variable */
TYPE strings_aat IS TABLE OF strings_rec INDEX BY VARCHAR2(4000);
l_strings strings_aat;
BEGIN
/* populate collection with 3 000 000 rows */
/* looping can take some time */
FOR indx IN 1..3000000 LOOP
l_strings('indx_' || TO_CHAR(indx)).one := 'column one indx ' || TO_CHAR(indx);
l_strings('indx_' || TO_CHAR(indx)).two := 'column two indx ' || TO_CHAR(indx);
l_strings('indx_' || TO_CHAR(indx)).three := 'column three indx ' || TO_CHAR(indx);
END LOOP;
/* print size of the collection */
DBMS_OUTPUT.PUT_LINE('Assoc table holds: ' || TO_CHAR(l_strings.COUNT) || ' records.');
/*
** CREATE TABLE aat_target
** (
** t_id VARCHAR(4000)
** , one VARCHAR(4000)
** , two VARCHAR(4000)
** , three VARCHAR(4000)
** );
*/
/* insert rows */
FORALL indx IN l_strings.FIRST..l_strings.LAST
INSERT INTO aat_target(t_id, one, two, three)
VALUES (l_strings(indx), l_strings(indx).one, l_strings(indx).two, l_strings(indx).three);
/* print number of rows inserted */
DBMS_OUTPUT.PUT_LINE('Number of rows inserted ' || SQL%ROWCOUNT || ' rows');
COMMIT;
END associative_array;
END perfrormance_test;
