Free space management in databases
View Patent ↗Managing free space in a relational database. A data statistics history for the relational database is received, including a distribution of index key columns for a clustering index. Based on the data statistics history, increasing index key columns are identified. A rate of increase of the increasing index key columns is measured. Based on the measured rate of increase and on an estimate of database reorganization frequency, free space for the increasing index key columns is reserved.
1. A computer-implemented method for free space management in a relational database, the method comprising:
receiving, by a computer, a data statistics history for the relational database, including a distribution of index key columns for a clustering index;
identifying, by the computer, increasing index key columns, based on the data statistics history;
measuring, by the computer, a rate of increase of the increasing index key columns, wherein the measuring comprises reading and analyzing exception records from a database log to determine how often an insert operation could not locate a free page; and
reserving, by the computer, free space for the increasing index key columns, based on the measured rate of increase and based on an estimate of database reorganization frequency.
2. A method in accordance with claim 1 , further comprising:
comparing the amount of free space reserved with an actual amount of free space used; and
adapting the measuring and/or reserving, based on the actual amount of free space used.
3. A method in accordance with claim 1 , wherein receiving, by the computer, a data statistics history for the relational database comprises:
using a database utility program and/or a real-time statistics process to collect data distribution information; and
aggregating the data distribution information into a statistics history table.
4. A method in accordance with claim 1 , wherein identifying, by the computer, increasing index key columns, based on the data statistics history, comprises:
categorizing index key columns as stable, slowly growing, or growing, based on an expected growth rate in number of column values; and
identifying the index key columns that are categorized as growing but not as stable or slowly growing.
5. A method in accordance with claim 1 , wherein measuring, by the computer, a rate of increase of the increasing index key columns further comprises:
comparing highest and/or lowest index key column values over a given time frame.
6. A method in accordance with claim 1 , wherein the estimate of database reorganization frequency is based on at least one of a user-supplied parameter and a log of database activity.
7. A computer system for free space management in a relational database, the computer system comprising:
one or more computer processors, one or more non-transitory computer-readable storage media, and program instructions stored on one or more of the computer-readable storage media for execution by at least one of the one or more processors, the program instructions comprising:
program instructions to receive a data statistics history for the relational database, including a distribution of index key columns for a clustering index;
program instructions to identify increasing index key columns, based on the data statistics history;
program instructions to measure a rate of increase of the increasing index key columns wherein the program instructions to measure comprise program instructions to read and analyze exception records from a database log to determine how often an insert operation could not locate a free page; and
program instructions to reserve free space for the increasing index key columns, based on the measured rate of increase and based on an estimate of database reorganization frequency.
8. A computer system in accordance with claim 7 , further comprising:
program instructions to compare the amount of free space reserved with an actual amount of free space used; and
program instructions to adapt the measuring and/or reserving, based on the actual amount of free space used.
9. A computer system in accordance with claim 7 , wherein program instructions to receive a data statistics history for the relational database comprise:
program instructions to use a database utility program and/or a real-time statistics process to collect data distribution information; and
program instructions to aggregate the data distribution information into a statistics history table.
10. A computer system in accordance with claim 7 , wherein program instructions to identify increasing index key columns, based on the data statistics history, comprise:
program instructions to categorize index key columns as stable, slowly growing, or growing, according to an expected growth rate; and
program instructions to identify the index key columns that are categorized as growing but not as stable or slowly growing.
11. A computer system in accordance with claim 7 , wherein program instructions to measure a rate of increase of the increasing index key columns further comprise:
program instructions to compare highest and/or lowest index key column values over a given time frame.
12. A computer system in accordance with claim 7 , wherein the estimate of database reorganization frequency is based on at least one of a user-supplied parameter and a log of database activity.
13. A computer program product for free space management in a relational database, the computer program product comprising:
one or more non-transitory computer-readable storage media and program instructions stored on the one or more computer-readable storage media, the program instructions comprising:
program instructions to receive a data statistics history for the relational database, including a distribution of index key columns for a clustering index;
program instructions to identify increasing index key columns, based on the data statistics history;
program instructions to measure a rate of increase of the increasing index key columns wherein the program instructions to measure comprise program instructions to read and analyze exception records from a database log to determine how often an insert operation could not locate a free page; and
program instructions to reserve free space for the increasing index key columns, based on the measured rate of increase and based on an estimate of database reorganization frequency.
14. A computer program product in accordance with claim 13 , further comprising:
program instructions to compare the amount of free space reserved with an actual amount of free space used; and
program instructions to adapt the measuring and/or reserving, based on the actual amount of free space used.
15. A computer program product in accordance with claim 13 , wherein
program instructions to receive a data statistics history for the relational database comprise:
program instructions to use a database utility program and/or a real-time statistics process to collect data distribution information; and
program instructions to aggregate the data distribution information into a statistics history table.
16. A computer program product in accordance with claim 13 , wherein program instructions to identify increasing index key columns, based on the data statistics history, comprise:
program instructions to categorize index key columns as stable, slowly growing, or growing, according to an expected growth rate; and
program instructions to identify the index key columns that are categorized as growing but not as stable or slowly growing.
17. A computer program product in accordance with claim 13 , wherein program instructions to measure a rate of increase of the increasing index key columns further comprise:
program instructions to compare highest and/or lowest index key column values over a given time frame.
18. A computer program product in accordance with claim 13 , wherein the estimate of database reorganization frequency is based on at least one of a user-supplied parameter and a log of database activity.