Systems and methods for dynamic and self-optimization for managing database transformations

A computer program product for, wherein the computer program product comprises at least one non-transitory computer-readable medium having computer-readable program code portions embodied therein, the computer-readable program code portions which when executed by a processing device are configured to cause the processor to perform the following operations:

• identify, by the processing device, data;
• apply, by the processing device, the data to a split database table, wherein the split database table comprises a minor portion and a major portion, and wherein the minor portion is a pre-determined small portion of the split database table and the major portion is a leftover portion of the split database table exclusive from the minor portion;
• partition, by the processing device, the data and assign the data to the major portion or the minor portion based on a partitioning logic, wherein the partitioning logic partitions the data without duplicate data between the major portion and the minor portion;
• generate, by the processing device, a plurality of option tables comprising the data of the minor portion, wherein each option table of the plurality of option tables comprises an option data structure associated with a performance-based structural option for how to organize the split database table;
• generate, by the processing device, an optimization score for each option table simultaneously;
• identify, by the processing device, an optimized data structure from the plurality of option tables based on the optimization score for each option table, wherein the optimized data structure is identified based on at least one of a query occurrence count or a predefined query weight;
• apply, by the at least one processing device, the optimized data structure to the major portion of the split database table, wherein the application of the optimized data structure to the major portion comprises an automatic re-organization of data within the major portion of the split database table to match the performance-based structural option of the optimized data structure;
• and
• iteratively update, in response to the application of the optimized data structure to the major portion, the plurality of option tables with a plurality of new option data structures, wherein the plurality of new option data structures comprises new option data structures different from the option data structure previously applied to the major portion, wherein with each iteration:
• identify, by the at least one processing device, at least one new optimized data structure each time the plurality of option tables are updated, wherein the at least one new option data structure is identified based on the query occurrence count or the predefined query weight;
• and
• apply, each time at least one new option data structure is identified, the at least one new option data structure to the major portion of the split database table.

A computer implemented method for, the computer implemented method comprising:

• identifying, by at least one processing device, data;
• applying, by the at least one processing device, the data to a split database table, wherein the split database table comprises a minor portion and a major portion, and wherein the minor portion is a pre-determined small portion of the split database table and the major portion is a leftover portion of the split database table exclusive from the minor portion;
• partitioning, by the at least one processing device, the data and assign the data to the major portion or the minor portion based on a partitioning logic, wherein the partitioning logic partitions the data without duplicate data between the major portion and the minor portion;
• generating, by the at least one processing device, a plurality of option tables comprising the data of the minor portion, wherein each option table of the plurality of option tables comprises an option data structure associated with a performance-based structural option for how to organize the split database table;
• generating, by the at least one processing device, an optimization score for each option table simultaneously;
• identifying, by the at least one processing device, an optimized data structure from the plurality of option tables based on the optimization score for each option table, wherein the optimized data structure is identified based on at least one of a query occurrence count or a predefined query weight;
• applying, by the at least one processing device, the optimized data structure to the major portion of the split database table, wherein the application of the optimized data structure to the major portion comprises an automatic re-organization of data within the major portion of the split database table to match the performance-based structural option of the optimized data structure;
• and
• iteratively updating, in response to the application of the optimized data structure to the major portion, the plurality of option tables with a plurality of new option data structures, wherein the plurality of new option data structures comprises new option data structures different from the option data structure previously applied to the major portion, wherein with each iteration:
• identify, by the at least one processing device, at least one new optimized data structure each time the plurality of option tables are updated, wherein the at least one new option data structure is identified based on the query occurrence count or the predefined query weight;
• and
• apply, each time at least one new option data structure is identified, at least one new option data structure to the major portion of the split database table.