HCC conventional (array) insert performance issue
Hi,
While doing some tests with various HCC compression methods, I've come to the following scenario:
First a bigtab table having 20M rows (12 highly randomized columns) was created. Then two empty tables were created with ARCHIVE HIGH compression.
1. EXEC EXECUTE IMMEDIATE 'drop table t_hcc_archive_high'; EXCEPTION WHEN OTHERS THEN NULL; EXEC EXECUTE IMMEDIATE 'drop table t_hcc_archive_high2'; EXCEPTION WHEN OTHERS THEN NULL; 2. CREATE TABLE t_hcc_archive_high COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM bigtab WHERE 1 = 0; CREATE TABLE t_hcc_archive_high2 COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM bigtab WHERE 1 = 0; 3. INSERT INTO t_hcc_archive_high select * from bigtab; commit; --76 minutes to insert 20M rows in empty t_hcc_archive_high 4. INSERT /*+ append */ INTO t_hcc_archive_high2 select * from bigtab; commit; --20 minutes to insert 20M rows in empty t_hcc_archive_high2 5. INSERT INTO t_hcc_archive_high select * from bigtab; commit; --76 minutes to insert 20M rows on top