System and method for selective database indexing

• US 20020083048A1
• Filed: 12/22/2000
• Published: 06/27/2002
• Est. Priority Date: 09/26/2000
• Status: Active Grant

• Current Assignee: Blue Yonder Group, Inc. (Panasonic Holdings Corporation)
• Original Assignee: i2 Technologies Incorporated (Panasonic Holdings Corporation)
• Inventors: Tenorio, Manoel, Chinnappan, Mohanasundaram
• Granted Patent: US 6,708,161 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/954 Navigation, e.g. using categorised browsing
• CPC Class Codes: G06Q 30/06 Buying, selling or leasing transactions
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

A method for selectively indexing a database (32) includes selecting a set of fields (156) in the database (32). The selected fields (156) are located in one column (152) of a table (150) in the database (32). The method also includes determining the total time required for reading data from the fields (156) during a selected time period if the fields (156) are indexed and determining the total time required for reading data from the fields (156) during the selected time period if the fields (156) are not indexed. The method further includes determining the total time required for writing data to the fields (156) during the selected time period if the fields (156) are indexed and determining the total time required for writing data to the fields (156) during the selected time period if the fields (156) are not indexed. In addition, the method includes evaluating the total times required for reading and writing data to the fields to determine whether the fields (156) should be indexed.

Claims

1. A method for selectively indexing a database, comprising:

selecting a set of fields in the database, the fields located in one column of a table in the database;

determining the total time required for reading data from the fields during a selected time period if the fields are indexed;

determining the total time required for reading data from the fields during the selected time period if the fields are not indexed;

determining the total time required for writing data to the fields during the selected time period if the fields are indexed;

determining the total time required for writing data to the fields during the selected time period if the fields are not indexed; and

evaluating the total times required for reading and writing data to the fields to determine whether the fields should be indexed.

2. The method of claim 1, wherein the method is repeated for the set of fields associated with each column in the database.

3. The method of claim 1, wherein the method is repeated for the selected set of fields on a periodic basis to tune the performance of the database.

4. The method of claim 1, wherein determining the total time required for reading data from the fields comprises:

determining the amount of time required to read data from a selected one of the fields;

determining the number of reads of the fields during the selected time period; and

multiplying the amount of time required to read data from the selected field by the number of reads to generate the total time for reading data.

5. The method of claim 1, wherein determining the total time required for writing data to the fields comprises:

determining the amount of time required to write data to a selected one of the fields;

determining the number of writes to the fields during the selected time period; and

multiplying the amount of time required to write data from the selected field by the number of writes to generate the total time for writing data.

6. The method of claim 1, wherein determining the total time required for reading data from the fields comprises:

measuring the amount of time taken for each read occurring during the selected time period;

summing the amount of time taken for all of the reads occurring during the selected time period to generate the total time for reading data.

7. The method of claim 1, wherein determining the total time required for writing data to the fields comprises:

measuring the amount of time taken for each write occurring during the selected time period;

summing the amount of time taken for all of the writes occurring during the selected time period to generate the total time for writing data.

8. The method of claim 1, wherein:

the method further comprises;

multiplying the total times required for reading data with and without indexing by a first weighting factor; and

multiplying the total times required for writing data with and without indexing by a second weighting factor; and

evaluating the total times comprises evaluating the weighted total times for reading and writing to determine whether the fields should be indexed.

9. The method of claim 1, wherein evaluating the total times comprises:

determining the absolute time difference between the total time required for reading data if the fields are indexed and the total time required for writing data if the fields are indexed;

determining the absolute time difference between the total time required for reading data if the fields are not indexed and the total time required for writing data if the fields are not indexed; and

indexing the selected set of fields if the absolute time difference with indexing is less than the absolute time difference without indexing.

10. The method of claim 1, wherein evaluating the total times comprises:

determining the sum of the total time required for reading data if the fields are indexed and the total time required for writing data if the fields are indexed;

determining the sum of the total time required for reading data if the fields are not indexed and the total time required for writing data if the fields are not indexed; and

indexing the selected set of fields if the sum with indexing is less than the sum without indexing.

11. The method of claim 1, wherein the database comprises product data and the set of fields are associated with a feature of a product for which product data is included in the database.

13. The software of claim 12, wherein determining the total time required for reading data from the fields comprises:

determining the amount of time required to read data from a selected one of the fields;

determining the number of reads of the fields during the selected time period; and

multiplying the amount of time required to read data from the selected field by the number of reads to generate the total time for reading data.

14. The software of claim 12, wherein determining the total time required for writing data to the fields comprises:

determining the amount of time required to write data to a selected one of the fields;

determining the number of writes to the fields during the selected time period; and

multiplying the amount of time required to write data from the selected field by the number of writes to generate to the total time for writing data.

15. The software of claim 12, wherein determining the total time required for reading data from the fields comprises:

measuring the amount of time taken for each read occurring during the selected time period;

summing the amount of time taken for all of the reads occurring during the selected time period to generate the total time for reading data.

16. The software of claim 12, wherein determining the total time required for writing data to the fields comprises:

measuring the amount of time taken for each write occurring during the selected time period;

summing the amount of time taken for all of the writes occurring during the selected time period to generate the total time for writing data.

17. The software of claim 12, wherein:

the software is further operable to;

multiply the total times required for reading data with and without indexing by a first weighting factor; and

multiply the total times required for writing data with and without indexing by a second weighting factor; and

evaluating the total times comprises evaluating the weighted total times for reading and writing to determine whether the fields should be indexed.

18. The software of claim 12, wherein evaluating the total times comprises:

determining the absolute time difference between the total time required for reading data if the fields are indexed and the total time required for writing data if the fields are indexed;

determining the absolute time difference between the total time required for reading data if the fields are not indexed and the total time required for writing data if the fields are not indexed; and

indexing the selected set of fields if the absolute time difference with indexing is less than the absolute time difference without indexing.

19. The software of claim 12, wherein evaluating the total times comprises:

determining the sum of the total time required for reading data if the fields are indexed and the total time required for writing data if the fields are indexed;

determining the sum of the total time required for reading data if the fields are not indexed and the total time required for writing data if the fields are not indexed; and

indexing the selected set of fields if the sum with indexing is less than the sum without indexing.

20. The software of claim 12, wherein the database comprises product data and the set of fields are associated with a feature of a product for which product data is included in the database.

12. Software for selectively indexing a database, the software embodied in a computer-readable medium and operable to:

select a set of fields in the database, the fields located in one column of a table in the database;

determine the total time required for reading data from the fields during a selected time period if the fields are indexed;

determine the total time required for reading data from the fields during the selected time period if the fields are not indexed;

determine the total time required for writing data to the fields during the selected time period if the fields are indexed;

determine the total time required for writing data to the fields during the selected time period if the fields are not indexed; and

evaluate the total times required for reading and writing data to the fields to determine whether the fields should be indexed.

21. A method for selectively indexing a database, comprising:

selecting a set of fields in the database, the selected fields located in one column of a table in the database;

determining the number of reads of the selected fields during a specified time period;

determining the amount of read time required to read data from a particular one of the selected fields if the field is indexed;

multiplying the read time for the particular field by the number of reads of the selected fields during the time period to generate the total time for reading data if all the selected fields are indexed;

determining the amount of read time required to read data from a particular one of the selected fields if the field is not indexed;

multiplying the read time for the particular field by the number of reads of the selected fields during the time period to generate the total time for reading data if all the selected fields are not indexed;

determining the number of writes to the selected fields during a specified time period;

determining the amount of write time required to write data to a particular one of the selected fields if the field is indexed;

multiplying the write time for the particular field by the number of writes to the selected fields during the time period to generate the total time for writing data if all the selected fields are indexed;

determining the amount of write time required to write data to a particular one of the selected fields if the field is not indexed;

multiplying the write time for the particular field by the number of writes to the selected fields during the time period to generate the total time for writing data if all the selected fields are not indexed;

determining the absolute time difference between the total read time if the selected fields are indexed and the total write time if the selected fields are indexed;

determining the absolute time difference between the total read time if the selected fields are not indexed and the total write time if the selected fields are not indexed; and

indexing the selected fields if the absolute time difference with indexing is less than the absolute time difference without indexing.

22. Software for selectively indexing a database, the software embodied in a computer-readable medium and operable to:

select a set of fields in the database, the selected fields located in one column of a table in the database;

determine the number of reads of the selected fields during a specified time period;

determine the amount of read time required to read data from a particular one of the selected fields if the field is indexed;

multiply the read time for the particular field by the number of reads of the selected fields during the time period to generate the total time for reading data if all the selected fields are indexed;

determine the amount of read time required to read data from a particular one of the selected fields if the field is not indexed;

multiply the read time for the particular field by the number of reads of the selected fields during the time period to generate the total time for reading data if all the selected fields are not indexed;

determine the number of writes to the selected fields during a specified time period;

determine the amount of write time required to write data to a particular one of the selected fields if the field is indexed;

multiply the write time for the particular field by the number of writes to the selected fields during the time period to generate the total time for writing data if all the selected fields are indexed;

determine the amount of write time required to write data to a particular one of the selected fields if the field is not indexed;

multiply the write time for the particular field by the number of writes to the selected fields during the time period to generate the total time for writing data if all the selected fields are not indexed;

determine the absolute time difference between the total read time if the selected fields are indexed and the total write time if the selected fields are indexed;

determine the absolute time difference between the total read time if the selected fields are not indexed and the total write time if the selected fields are not indexed; and

index the selected fields if the absolute time difference with indexing is less than the absolute time difference without indexing.