Data partitioning for high-efficiency data retrieval
View Patent ↗Systematically enforcing generation of partition key values for records in a data management system such that for any given partitioned table, all records relating to a common business object share a common partitioning key. Partitioning and/or clustering of these records can then be done based on these specially generated partitioning key values, with entities dependent on the same first-class entity being placed in the same partition and/or cluster. This may provide considerable performance gains in at least some circumstances.
1. A method for use with a database including a table including a set of supported partitions, the method comprising:
creating a primary key definition having a predefined format for use as a range partitioning key for a table, with the predefined format including the following identifier fields: service-level entity context, random number, timestamp and instance, with instance being a unique number for each node in a clustered environment;
assigning a default key generation format for the primary key definition, such that the default key generation format causes: (i) all primary keys for each instance of a first class entity to be generated with a common code, (ii) all primary keys of a set of correlated records for a particular organization to be generated randomly at the time they are initially stored in a first range of the table, and (iii) future data augmentations to the set of correlated records for the particular organization to be stored in the first range, thereby ensuring the first class entity is randomly distributed among the set of supported partitions of the table;
generating a primary key for the first class entity for a first time, by generating a random number with a defined length, so that records relating to the first class entity will be randomly distributed among the set of supported partitions;
generating subsequent augmentations when new correlated records are added, using the primary key of the first class entity to retrieve a value of service-level entity context field from the first class entity, thereby ensuring all other new primary keys are generated with the common code;
creating a set of database tables with range partitioning on the primary keys generated; and
saving all correlated records in a common database partition.
2. The method of claim 1 wherein the common code is located at the two most significant digits of the primary keys for each instance of the first class entity.
3. The method of claim 1 where the first class entities include at least the following types of first class entities: people, organizations and contracts.
4. A computer program product (CPP) for use with a database including a table including a set of supported partitions, the method comprising:
a machine readable storage device; and
computer code stored on the machine readable storage device, with the computer code including instructions for causing a processor(s) set to perform operations including the following:
creating a primary key definition having a predefined format for use as a range partitioning key for a table, with the predefined format including the following identifier fields: service-level entity context, random number, timestamp and instance which is a unique number for each node in a clustered environment,
assigning a default key generation format for the primary key definition, such that the default key generation format causes: (i) all primary keys for each instance of a first class entity to be generated with a common code, (ii) all primary keys of a set of correlated records for a particular organization are generated randomly at the time they are initially stored in a first range of the table, and (iii) future data augmentations to the set of correlated records for the particular organization are stored in the first range, thereby ensuring the first class entity is randomly distributed among the set of supported partitions of the table,
generating a primary key for the first class entity for a first time, by generating a random number with a defined length, so that records relating to the first class entity will randomly distributed among the set of supported partitions,
generating subsequent augmentations when new correlated records are added, using the primary key of the first class entity to retrieve a value of service-level entity context field from the first class entity, thereby ensuring all other new primary keys are generated with the common code,
creating a set of database tables with range partitioning on the primary keys generated, and
saving all correlated records in a common database partition.
5. The CPP of claim 4 wherein the common code is located at the two most significant digits of the primary keys for each instance of the first class entity.
6. The CPP of claim 4 where the first class entities include at least the following types of first class entities: people, organizations and contracts.
7. A computer system comprising:
a processor(s) set;
a machine readable storage device; and
computer code stored on the machine readable storage device, with the computer code including instructions for causing the processor(s) set to perform operations including the following:
creating a primary key definition having a predefined format for use as a range partitioning key for a table, with the predefined format including the following identifier fields: service-level entity context, random number, timestamp and instance which is a unique number for each node in a clustered environment,
assigning a default key generation format for the primary key definition, such that the default key generation format causes: (i) all primary keys for each instance of a first class entity to be generated with a common code, (ii) all primary keys of a set of correlated records for a particular organization are generated randomly at the time they are initially stored in a first range of the table, and (iii) future data augmentations to the set of correlated records for the particular organization are stored in the first range, thereby ensuring the first class entity is randomly distributed among the set of supported partitions of the table,
generating a primary key for the first class entity for a first time, by generating a random number with a defined length, so that records relating to the first class entity will randomly distributed among the set of supported partitions,
generating subsequent augmentations when new correlated records are added, using the primary key of the first class entity to retrieve a value of service-level entity context field from the first class entity, thereby ensuring all other new primary keys are generated with the common code,
creating a set of database tables with range partitioning on the primary keys generated, and
saving all correlated records in a common database partition.
8. The computer system of claim 7 wherein the common code is located at the two most significant digits of the primary keys for each instance of the first class entity.
9. The computer system of claim 7 where the first class entities include at least the following types of first class entities: people, organizations and contracts.