Use select into ... compression to select regular and LOB data directly into a compressed table.
The destination table does not inherit anything from the original table. That is, if the table from which you are selecting data is page-level compressed, the table into which you select the data can be row-level compressed, or not compressed.
You must indicate compression levels if you are selecting large object data into a table.
Compression Type | Behavior of select into on Target Tables | Source Table or Column | Database-wide Setting for compression | Target Table or Column |
---|---|---|---|---|
Data compression |
Target table or columns do not inherit any properties from the source table. If you do not specify compression, tables other than temporary tables inherit the database-wide setting for compression. Temporary tables do not inherit any compression settings from the source table, source column, or from the target databases’s attributes. | Table can be compressed or uncompressed, and may include one or more compressed columns. | none | Target table and all columns are uncompressed. |
row or page | Target table is created with either row or page compression, according to
database-wide attribute. All eligible columns are
compressed. Note: Index compression compression
does not support row
compression.
|
|||
LOB compression |
LOB columns in the target table do not inherit any properties from the source columns. If you do not specify compression, LOB columns in target tables other than temporary tables inherit the database-wide setting for the lob_compression attribute. LOB columns in temporary tables inherit nothing from the source table, source column, or from the target database's attributes. | Source LOB columns may be compressed. | lob_compression = 0, unset for the database | All LOB columns in the target table are uncompressed. |
lob_compression = compression_level | All LOB columns in the target table are created using the database-wide setting for lob_compression = compression_level. |
select * into titles2 with compression = row from titles
See the Reference Manual: Commands.