Compression refers to a logical row compression in which multiple sets of nonrepeating column values are appended to a single set of repeating column values. This allows the system to store the repeating value set only once, while any nonrepeating column values are stored as logical segmental extensions of the base repeating set.
- One set of values for the columns in the column_1 list. When a column_2 list is specified, the columns specified in the column_1 list are the data that is compressed for rows that have the same values for the columns in the column_1 list.
- Multiple sets of values of the columns in the column_2 list for rows that have the same column_1 list values.
For example, if a logical join result has n rows with the same fixed part value, then there is one corresponding physical join index row that includes n repeated parts in the physical join index. A physical join index row represents one or more logical join index rows. The number of logical join index rows represented in the physical row depends the number of repeated values.
Compression is only done on rows inserted by the same INSERT statement. Newly inserted rows are not added as logical rows to existing compressed rows.
When the number of repeated values associated with a given fixed value exceeds the system row size limit, the join index row is automatically split into multiple physical rows, each having the same fixed value but different lists of repeated values. A given logical join index result row cannot exceed the system 1 MB row size limit.