Exadata General (MOSC)

MOSC Banner

HCC conventional (array) insert performance issue

edited Jan 13, 2022 2:59PM in Exadata General (MOSC) 8 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center