15.00 - Expanded Multi-Value Compression Examples - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Expanded Multi-Value Compression Examples

The following set of examples studies the relative benefits of multi-value compression for different table and system sizes. The calculations evaluate compression as if presence bits were added individually. You can also use this methodology to evaluate the outcome of compressing multiple columns within a table.

Example  

This example examines a case that multi-value compresses 100 values.

Fallback is enabled for this table.

The following table provides the specifications for the study.

 

 

 

    Bytes Recovered by Compression Expressed in Different Units of Magnitude

          Bytes

      Megabytes

      Gigabytes

      Terabytes

Additional Table Header Space

              10,010

               0

             0.00

             0.00

Additional Row Header Space

 1,750,000,000

        1,750

             1.8

             0.00

Savings from Row Compression

 2,000,000,000

        2,000

             2.0

             0.00

 

=================================================

Net Savings from Compression

     249,989,990

           250

             0.2

             0.00

 

 

 

 

 

Capacity utilization if column not compressed

20,250,000,000

 

 

 

Capacity utilization if column is compressed

20,000,010,010

 

 

 

Percent compression

           1

 

 

 

Compression ratio

           1.01

 

 

 

Break-Even Percentage of Compressible Rows

           8.75

 

 

 

To Break Even, 1 in n Rows Must Be Compressible, Where n Is This Value

         11.4

 

 

 

The following table indicates the multi-value net space savings recovered through compression. Notice the relative multi-value net savings loss for this case when 100 values are compressed rather than 1 value.

 

                                                           Variable

          Condition

Number of AMPs in system

10

Column nullability

Nullable

Null compression

Compressed

Cardinality

1.0 x 109

Number of rows having compressible values

1.0 x 108

Capacity utilization if column is not compressed (bytes)

20,250,000,000

Number of values to compress

100

Column size (bytes)

10

Fallback

Yes

1 in n rows is compressible, where n is this value

10.0

Percentage of rows compressible in table

10

Presence bits for multi-value compression

7

Presence bits for null compression

1

Example  

This example examines a case that is identical to the one presented in “Example 1” on page 732 except that it multi-value compresses 200 values rather than 100 values.

Fallback is enabled for this table.

The following table provides the specifications for the study.

 

 

 

    Bytes Recovered by Compression Expressed in Different Units of Magnitude

          Bytes

      Megabytes

      Gigabytes

      Terabytes

Additional Table Header Space

                    120

               0

           0.0

           0.00

Additional Row Header Space

      250,000,000

           250

           0.3

           0.00

Savings from Row Compression

   2,000,000,000

        2,000

           2.0

           0.00

 

=================================================

Net Savings from Compression

   1,749,999,880

        1,750

           1.7

           0.00

 

 

 

 

 

Capacity utilization if column not compressed

20,250,000,000

 

 

 

Capacity utilization if column is compressed

18,500,000,120

 

 

 

Percent compression

           9.00

 

 

 

Compression ratio

           1.09

 

 

 

Break-Even Percentage of Compressible Rows

           1.25

 

 

 

To Break Even, 1 in n Rows Must Be Compressible, Where n Is This Value

         80.00

 

 

 

 

                                                           Variable

           Condition

Number of AMPs in system

10

Column nullability

Nullable

Null compression

Compressed

Number of values to compress

200

Cardinality

1.0 x 109

Number of rows having compressible values

1.0 x 108

Capacity utilization if column is not compressed (bytes)

20,250,000,000

Number of values to compress

200

Column size (bytes)

10

Fallback

Yes

1 in n rows is compressible, where n is this value

10.0

Percentage of rows compressible in table

10

Presence bits for multi-value compression

8

Presence bits for null compression

1

The following table indicates the net space savings recovered through multi-value compression. Notice the absolute net savings loss for this case when 200 values are multi-value compressed rather than 100 values. Multi-value compression of this column actually increases the space consumed rather than decreasing it.

 

 

 

    Bytes Recovered by Compression Expressed in Different Units of Magnitude

          Bytes

      Megabytes

      Gigabytes

      Terabytes

Additional Table Header Space

               20,010

               0

           0.0

           0.00

Additional Row Header Space

   2,000,000,000

           175

           0.2

           0.00

Savings from Row Compression

   2,000,000,000

        2,000

           2.0

           0.00

 

=================================================

Net Savings from Compression

             -20,010

      -2,000

         -2.0

           0.00

 

 

 

 

 

Capacity utilization if column not compressed

20,250,000,000

 

 

 

Capacity utilization if column is compressed

20,250,020,010

 

 

 

Percent compression

           0

 

 

 

Compression ratio

           1.00

 

 

 

Break-Even Percentage of Compressible Rows

         10.00

 

 

 

To Break Even, 1 in n Rows Must Be Compressible, Where n Is This Value

         10.00

 

 

 

Example  

This example examines a case that is identical to the one presented in “Example 2” on page 733 except that fallback is not enabled for this table.

The following table provides the specifications for the study.

 

                                                           Variable

         Condition

Number of AMPs in system

10

Column nullability

Nullable

Null compression

Compressed

Number of values to compress

200

Cardinality

1.0 x 109

Number of rows having compressible values

1.0 x 108

Capacity utilization if column is not compressed (bytes)

10,125,000,000

Column size (bytes)

10

Fallback

No

1 in n rows is compressible, where n is this value

10.0

Percentage of rows compressible in table

10

Presence bits for multi-value compression

8

Presence bits for null compression

1

The following table indicates the net space savings recovered through multi-value compression. Notice the absolute net savings loss for this case when 200 values are compressed rather than 100 values. Multi-value compression of this column actually increases the space consumed rather than decreasing it.

 

 

 

     Bytes Recovered by Compression Expressed in Different Units of Magnitude

          Bytes

      Megabytes

      Gigabytes

      Terabytes

Additional Table Header Space

              20,010

               0

           0.0

           0.00

Additional Row Header Space

 1,000,000,000

           175

           0.2

           0.00

Savings from Row Compression

 1,000,000,000

        2,000

           2.0

           0.00

 

=================================================

Net Savings from Compression

           -20,010

      -2,000

         -2.0

           0.00

 

 

 

 

 

Capacity utilization if column not compressed

10,125,000,000

 

 

 

Capacity utilization if column is compressed

10,120,020,010

 

 

 

Percent compression

           0

 

 

 

Compression ratio

           1.00

 

 

 

Break-Even Percentage of Compressible Rows

         10.00

 

 

 

To Break Even, 1 in n Rows Must Be Compressible, Where n Is This Value

         10.00

 

 

 

Example  

This example examines a case that is identical to the one presented in “Example 2” on page 733 except that the length of the multi-value compressed column is 40 bytes rather than 10 bytes.

Fallback is enabled for this table.

The following table provides the specifications for the study.

 

                                                           Variable

          Condition

Number of AMPs in system

10

Column nullability

Nullable

Null compression

Compressed

Cardinality

1.0 x 109

Number of rows having compressible values

1.0 x 108

Capacity utilization if column is not compressed (bytes)

80,250,000,000

Number of values to compress

200

Column size (bytes)

40

Fallback

Yes

1 in n rows is compressible, where n is this value

10.0

Percentage of rows compressible in table

10

Presence bits for multi-value compression

8

Presence bits for null compression

1

The following table indicates the net space savings recovered through multi-value compression. Notice the net savings gain for this case when the number of bytes in the multi-value compressed column is increased to 40 from 10. This not only demonstrates the obvious fact that greater space savings can be extracted by multi-value compressing wider column values, but that there is a crossover point between where the column width is such that a net savings cannot be realized by multi-value compression and where the column width is such that a net savings can be realized by multi-value compression.

 

 

 

   Bytes Recovered by Compression Expressed in Different Units of Magnitude

          Bytes

      Megabytes

      Gigabytes

      Terabytes

Additional Table Header Space

               80,010

               0

           0.0

           0.00

Additional Row Header Space

   2,000,000,000

        2,000

           2.0

           0.00

Savings from Row Compression

   8,000,000,000

        8,000

           8.0

           0.01

 

=================================================

Net Savings from Compression

   5,999,919,990

        6,000

           6.0

           0.01

 

 

 

 

 

Capacity utilization if column not compressed

80,250,000,000

 

 

 

Capacity utilization if column is compressed

74,250,080,010

 

 

 

Percent compression

           7

 

 

 

Compression ratio

           1.08

 

 

 

Break-Even Percentage of Compressible Rows

           2.50

 

 

 

To Break Even, 1 in n Rows Must Be Compressible, Where n Is This Value

         40.00

 

 

 

Example  

This example examines a case that is identical to the one presented in “Example 2” on page 733 except that 30% of the rows in the table are multi-value compressible rather than 10% of the rows being multi-value compressible.

Fallback is enabled for this table.

The following table provides the specifications for the study.

 

                                                           Variable

          Condition

Number of AMPs in system

10

Column nullability

Nullable

Null compression

Compressed

Cardinality

1.0 x 109

Number of rows having compressible values

1.0 x 108

Capacity utilization if column is not compressed (bytes)

20,250,000,000

Number of values to compress

200

Column size (bytes)

10

Fallback

Yes

1 in n rows is compressible, where n is this value

10.0

Percentage of rows compressible in table

30

Presence bits for multi-value compression

8

Presence bits for null compression

1

The following table indicates the net space savings recovered through multi-value compression. Notice the net savings gain for this case when the percentage of multi-value compressible rows in the compressed column is increased to 30 from 10.

 

 

 

    Bytes Recovered by Compression Expressed in Different Units of Magnitude

          Bytes

      Megabytes

      Gigabytes

      Terabytes

Additional Table Header Space

               20,010

               0

           0.0

           0.00

Additional Row Header Space

  2,000,000,000

        2,000

           2.0

           0.00

Savings from Row Compression

  6,000,000,000

        6,000

           6.0

           0.01

 

=================================================

Net Savings from Compression

  3,999,979,990

        4,000

           4.0

           0.00

 

 

 

 

 

Capacity utilization if column not compressed

20,250,000,000

 

 

 

Capacity utilization if column is compressed

16,250,020,010

 

 

 

Percent compression

         20

 

 

 

Compression ratio

           1.25

 

 

 

Break-Even Percentage of Compressible Rows

         10.00

 

 

 

To Break Even, 1 in n Rows Must Be Compressible, Where n Is This Value

         10.00