Reordering datasets in a table for increased compression ratio
Selecting tables for compression by threshold statistical values. Identified tables are reordered according to fields having the lowest cardinality to increase the size of character strings replaced by keys during compression. Field locations are mapped between the original table and the reordered table. Dictionary-based compression is performed on reordered tables.
1. A computer-implemented method for reordering data distribution in database tables, the method comprising:
identifying a threshold ratio for reordering field locations in a set of database tables, the threshold ratio comparing a number of identical values by field in a database table to a total data volume of the database table;
determining to reorder field locations in a first database table of the set of database tables based on the first database table meeting the identified threshold ratio;
determining field data cardinality for a set of fields in the first database table;
selecting a sub-set of fields of the first database table to be located among leading field locations of the first databased table, the sub-set of fields having field data cardinality values lower than remaining fields in the set of fields; and
reordering the first database table such that the selected sub-set of fields are leading fields in the reordered database table, thereby creating a reordered database table, a data field located first among the leading fields having a lowest field data cardinality value and an adjacent field having a second lowest field data cardinality value.
2. The method of claim 1 , wherein the reordering step further sorts records in the reordered database table such that records having identical adjacent field values are arranged adjacent each other.
3. The method of claim 1 , further comprising:
collecting statistical data for the database tables including the number of identical values in each field of the database tables, a data volume of each database table, and field data cardinality for each field in the database tables.
4. The method of claim 1 , further comprising:
mapping original field locations of the first database table prior to the reordering step; and
mapping target field locations of the reordered database.
5. The method of claim 1 , further comprising:
rebuilding a compression dictionary according to sets of leading record values of the reordered table, each set of leading record values including values in the selected sub-set of fields.
6. The method of claim 1 , further comprising:
performing a dictionary-based compression on the reordered database table.
7. The method of claim 1 , wherein:
the database table is a row store table; and
the set of fields are organized as columns of data.
8. A computer program product comprising a computer-readable storage medium having a set of instructions stored therein which, when executed by a processor, causes the processor to reorder data distribution in database tables by:
identifying a threshold ratio for reordering field locations in a set of database tables, the threshold ratio comparing a number of identical values by field in a database table to a total data volume of the database table;
determining to reorder field locations in a first database table of the set of database tables based on the first database table meeting the identified threshold ratio;
determining field data cardinality for a set of fields in the first database table;
selecting a sub-set of fields of the first database table to be located among leading field locations of the first databased table, the sub-set of fields having field data cardinality values lower than remaining fields in the set of fields; and
reordering the first database table such that the selected sub-set of fields are leading fields in the reordered database table, thereby creating a reordered database table, a data field located first among the leading fields having a lowest field data cardinality value and an adjacent field having a second lowest field data cardinality value.
9. The computer program product of claim 8 , wherein the reordering step further sorts records in the reordered database table such that records having identical adjacent field values are arranged adjacent each other.
10. The computer program product of claim 8 , further causing the processor to reorder data distribution in database tables by:
collecting statistical data for the database tables including the number of identical values in each field of the database tables, a data volume of each database table, and field data cardinality for each field in the database tables.
11. The computer program product of claim 8 , further causing the processor to reorder data distribution in database tables by:
performing a dictionary-based compression on the reordered database table.
12. The computer program product of claim 8 , wherein:
mapping original field locations of the first database table prior to the reordering step; and
mapping target field locations of the reordered database.
13. A computer system for reordering data distribution in database tables, the computer system comprising:
a processor set; and
a computer readable storage medium;
wherein:
the processor set is structured, located, connected, and/or programmed to run program instructions stored on the computer readable storage medium; and
the program instructions which, when executed by the processor set, cause the processor set to reorder data distribution in database tables by:
identifying a threshold ratio for reordering field locations in a set of database tables, the threshold ratio comparing a number of identical values by field in a database table to a total data volume of the database table;
determining to reorder field locations in a first database table of the set of database tables based on the first database table meeting the identified threshold ratio;
determining field data cardinality for a set of fields in the first database table;
selecting a sub-set of fields of the first database table to be located among leading field locations of the first databased table, the sub-set of fields having field data cardinality values lower than remaining fields in the set of fields; and
reordering the first database table such that the selected sub-set of fields are leading fields in the reordered database table, thereby creating a reordered database table, a data field located first among the leading fields having a lowest field data cardinality value is a leading field and an adjacent field having a second lowest field data cardinality value.
14. The computer system of claim 13 , wherein the reordering step further sorts records in the reordered database table such that records having identical adjacent field values are arranged adjacent each other.
15. The computer system of claim 13 , further causing the processor to reorder data distribution in database tables by:
collecting statistical data for the database tables including the number of identical values in each field of the database tables, a data volume of each database table, and field data cardinality for each field in the database tables.
16. The computer system of claim 13 , further causing the processor to reorder data distribution in database tables by:
performing a dictionary-based compression on the reordered database table.
17. The computer system of claim 13 , wherein:
mapping original field locations of the first database table prior to the reordering step; and
mapping target field locations of the reordered database.