I am looking to join for tables together using create table as and I had a question about Hybrid Columnar Compression. For testing, I have found that the uncompressed daata will be around 10 TB and the compressed data will be around 1 TB. I am planning to compress the table during the create table as statement and wanted to know in what order Oracle whould do the compression, i.e. will the table be created and then Oracle will compress the data or will the table be compressed as the table will be created. The motivation behind the question is to see how much storage I will need to complete the operation.
Something else to be aware of: Although the final table will be written directly as HCC-compressed data, temporary sort data goes to your temporary tablespace uncompressed. So if your join requires temp usage, make sure your temp tablespace is large enough. The same applies to sorting (which you might want to consider anyway, since it can improve your compression ratio as well as making storage indexes more usable).