Method, system, and program for managing database operations with respect to a database table

1. A method for managing a database, comprising:

processing a request to create a partitioned index on at least one non-partitioning key column of a table in a computer readable medium;

determining table partitions in which rows of the table are stored;

generating one index partition for each determined table partition, wherein each index partition includes nodes, wherein each node in each index partition includes a key value from at least one column in a corresponding table row in the table partition associated with the index partition and a location identifier identifying the corresponding table row in the corresponding table partition;

receiving a request to add a row to the table;

determining one of a plurality of table partitions on which to store the added row;

determining one index partition associated with the determined table partition; and

adding a node to the determined index partition including a key value from the row added to the table and the location identifier for the added row.

2. The method of claim 1 , wherein determining one of the plurality of table partitions comprises:

processing a partition map having a partition key column and criteria;

determining one table partition on which to store the added row based on a partition key column value in the added row and the criteria in the partition map for the partition key column; and

storing the added row in the determined table partition.

3. The method of claim 1 , further comprising:

processing a query specifying search predicates on at least one column of the table;

if the query specifies search predicates on at least one key column of the index partitions, then querying at least one index partition for index nodes having key values that satisfy the search predicates; and

returning rows from the table corresponding to the located index nodes whose key values satisfy the search predicates.

4. The method of claim 3 , wherein querying the at least one index partition and returning rows from the table further comprises:

if rows that satisfy the query search predicates are located in multiple index partitions, then scanning each index partition to determine index nodes having qualifying key values, wherein a same range of key values can exist in multiple index partitions;

selecting one qualifying key value from the determined qualifying key values according to a criteria;

returning data from the table row addressed by the location identifier in the index node including the selected qualifying key value; and

scanning the index partition comprising the selected qualifying key value to determine one node having a qualifying key value to consider with any previously determined qualifying key values not selected.

5. The method of claim 4 , wherein the steps of: (i) selecting one qualifying key value, (ii) returning the corresponding table row, and (iii) scanning the index partition including the node having the selected key value to determine a next node having a qualifying key value to consider with any determined qualifying key values that were not selected in previous iterations are repeatedly performed until all nodes are considered in the multiple index partitions.

6. The method of claim 1 , further comprising:

providing a partition map having at least one partition key column on the table;

processing a query specifying search predicates on at least one column of the table;

if the query specifies search predicates on at least one key column of the index partitions and at least one partition key column indicated in the partition map, then determining from the criteria indicated in the partition map at least one table partition that would have partition map key columns that satisfy the query search predicates;

determining one index partition for each determined table partition;

querying the at least one determined index partition for nodes whose key value satisfies the search predicates; and

returning rows from the table corresponding to the index nodes that satisfy the search predicates.

7. The method of claim 6 , wherein the partition map associates different key column values with different table partitions, wherein determining the at least one table partition comprises determining at least one table partition associated with key column values in the criteria that are capable of satisfying the query search predicates.

8. The method of claim 1 , further comprising:

processing a query specifying search predicates on at least one column of the table;

if the query specifies search predicates on at least one key column of the index partitions, then determining at least one table partition capable of having rows that satisfy the search predicates;

determining one index partition for each determined table partition;

querying the at least one determined index partition for nodes whose key values satisfy the search predicates; and

returning data from table rows addressed by the location identifiers in the index nodes having key values that satisfy the search predicates.

9. The method of claim 1 , further comprising:

processing an operation with respect to at least one row in the table; and

processing one index partition associated with each table partition including the at least one table row subject to the operation when processing the operation.

10. A method for managing a database, comprising:

processing a query on an index to access rows from a table that satisfy search predicates on at least one index key column corresponding to one column in the table;

determining at least one table partition capable of including table rows that satisfy the search predicates;

determining one index partition corresponding to each determined table partition, wherein each index partition provides an index on a subset of table rows from one corresponding table partition, wherein each node in the index partition includes a key value and location identifier for a corresponding table row in the corresponding table partition;

querying each determined index partition for index nodes whose key values satisfy the search predicates; and

returning data from the table rows addressed by the location identifiers in the index nodes whose key values satisfy the search predicates.

11. The method of claim 10 , wherein the table rows are returned according to a sort criteria applied to all index nodes in the determined index partitions that satisfy the search predicates.

12. The method of claim 11 , wherein the sort criteria applied to the determined index partitions, comprises:

scanning each determined index partition to determine one index node having a qualifying key value;

selecting one qualifying key value from the qualifying key values selected from each of the multiple index partitions according to a criteria and sorted;

returning the table row addressed by the location identifier in the index node including the selected qualifying key value; and

scanning the index partition including the selected qualifying key value to determine one further qualifying key value to consider with any previously determined qualifying key values not selected.

13. A system for managing a database, comprising:

a computer readable medium;

table partitions in the computer readable medium in which rows of a table are stored;

means for processing a request to create a partitioned index on at least one non-partitioning key column of the table;

means for determining the table partitions in which rows of the table are stored;

means for generating one index partition for each determined table partition, wherein each index partition includes nodes, wherein each node in each index partition includes a key value from at least one column in a corresponding table row in the table partition associated with the index partition and a location identifier identifying the corresponding table row in the corresponding table partition;

means for receiving a request to add a row to the table;

means for determining one of the table partitions on which to store the added row;

means for determining one index partition associated with the determined table partition; and

means for adding a node to the determined index partition including a key value from the row added to the table and the location identifier for the added row.

14. The system of claim 13 , further comprising:

means for processing a query specifying search predicates on at least one column of the table;

means for querying at least one index partition for index nodes having key values that satisfy the search predicates if the query specifies search predicates on at least one key column of the index partitions; and

means for returning rows from the table corresponding to the located index nodes whose key values satisfy the search predicates.

15. The system of claim 13 , further comprising:

a partition map in the computer readable medium having a partition key column on a table;

means for processing a query specifying search predicates on at least one column of the table;

means for determining from the criteria indicated in the partition map at least one table partition that would have partition map key columns that satisfy the query search predicates if the query specifies search predicates on at least one key column of the index partitions and at least one partition key column indicated in the partition map;

means for determining one index partition for each determined table partition;

means for querying the at least one determined index partition for nodes whose key value satisfies the search predicates;

means for selecting one qualifying key value from the qualifying key values selected from each of the multiple index partitions according to a criteria and sorted;

means for returning a row from the table corresponding to the index node having the selected qualifying key value.

16. A system for managing a database, comprising:

a computer readable medium;

table partitions in the computer readable medium in which rows of a table are stored;

means for processing a query on an index to access rows from the table that satisfy search predicates on at least one index key column corresponding to one column in the table;

means for determining at least one table partition capable of including table rows that satisfy the search predicates;

means for determining one index partition corresponding to each determined table partition, wherein each index partition provides an index on a subset of table rows from one corresponding table partition, wherein each node in the index partition includes a key value and location identifier for a corresponding table row in the corresponding table partition;

means for querying each determined index partition for index nodes whose key values satisfy the search predicates; and

means for returning data from the table rows addressed by the location identifiers in the index nodes whose key values satisfy the search predicates.

17. The system of claim 16 , wherein the table rows are returned according to a sort criteria applied to all index nodes in the determined index partitions that satisfy the search predicates.

18. The method of claim 16 , further comprising:

selecting one qualifying key value from the qualifying key values selected from each of the multiple index partitions according to a criteria and sorted, wherein the returned data comprises the table row addressed by the location identifier in the index node having the selected key value.

19. An article of manufacture for managing a database, wherein the article of manufacture causes operations to be performed, the operations comprising:

processing a request to create a partitioned index on at least one non-partitioning key column of a table in a computer readable medium;

determining table partitions in which rows of the table are stored; and

generating one index partition for each determined table partition, wherein each index partition includes nodes, wherein each node in each index partition includes a key value from at least one column in a corresponding table row in the table partition associated with the index partition and a location identifier identifying the corresponding table row in the corresponding table partition;

receiving a request to add a row to the table;

determining one of a plurality of table partitions on which to store the added row;

determining one index partition associated with the determined table partition; and

adding a node to the determined index partition including a key value from the row added to the table and the location identifier for the added row.

20. The article of manufacture of claim 19 , wherein determining one of the plurality of table partitions comprises:

processing a partition map having a partition key column and criteria;

determining one table partition on which to store the added row based on a partition key column value in the added row and the criteria in the partition map for the partition key column; and

storing the added row in the determined table partition.

21. The article of manufacture of claim 19 , wherein the operations further comprise:

processing a query specifying search predicates on at least one column of the table;

if the query specifies search predicates on at least one key column of the index partitions, then querying at least one index partition for index nodes having key values that satisfy the search predicates;

selecting one qualifying key value from the qualifying key values selected from each of the multiple index partitions according to a criteria and sorted; and

returning rows from the table corresponding to the located index node having the selected qualifying key value.

22. The article of manufacture of claim 21 , wherein querying the at least one index partition and returning rows from the table further comprises:

if rows that satisfy the query search predicates are located in multiple index partitions, then scanning each index partition to determine index nodes having qualifying key values, wherein a same range of key values can exist in multiple index partitions;

selecting one qualifying key value from the qualifying key values selected from each of the multiple index partitions according to a criteria and sorted;

returning data from the table row addressed by the location identifier in the index node including the selected qualifying key value; and

scanning the index partition comprising the selected qualifying key value to determine one node having a qualifying key value to consider with any previously determined qualifying key values not selected.

23. The article of manufacture of claim 22 , wherein the steps of: (i) selecting one qualifying key value, (ii) returning the corresponding table row, and (iii) scanning the index partition including the node having the selected key value to determine a next node having a qualifying key value to consider with any determined qualifying key values that were not selected in previous iterations are repeatedly performed until all nodes are considered in the multiple index partitions.

24. The article of manufacture of claim 19 , wherein the operations further comprise:

providing a partition map having at least one partition key column on the table;

processing a query specifying search predicates on at least one column of the table;

if the query specifies search predicates on at least one key column of the index partitions and at least one partition key column indicated in the partition map, then determining from the criteria indicated in the partition map at least one table partition that would have partition map key columns that satisfy the query search predicates;

determining one index partition for each determined table partition;

querying the at least one determined index partition for nodes whose key value satisfies the search predicates;

selecting one qualifying key value from the qualifying key values selected from each of the multiple index partitions according to a criteria and sorted; and

returning the row from the table corresponding to the index node having the selected qualifying key value.

25. The article of manufacture of claim 24 , wherein the partition map associates different key column values with different table partitions, wherein determining from the criteria the at least one table partition comprises determining at least one table partition associated with key column values that are capable of satisfying the query search predicates.

26. The article of manufacture of claim 19 , wherein the operations further comprise:

processing a query specifying search predicates on at least one column of the table;

if the query specifies search predicates on at least one key column of the index partitions, then determining at least one table partition capable of having rows that satisfy the search predicates;

determining one index partition for each determined table partition;

querying the at least one determined index partition for nodes whose key values satisfy the search predicates;

selecting one qualifying key value from the qualifying key values selected from each of the multiple index partitions according to a criteria and sorted; and

returning data from table row addressed by the location identifier in the index node having the selected qualifying key value.

27. The article of manufacture of claim 19 , wherein the operations further comprise:

processing an operation with respect to at least one row in the table; and

processing one index partition associated with each table partition including the at least one table row subject to the operation when processing the operation.

28. An article of manufacture for managing a database, wherein the article of manufacture causes operations to be performed, the operations comprising:

processing a query on an index to access rows from a table that satisfy search predicates on at least one index key column corresponding to one column in the table;

determining at least one table partition capable of including table rows that satisfy the search predicates;

determining one index partition corresponding to each determined table partition, wherein each index partition provides an index on a subset of table rows from one corresponding table partition, wherein each node in the index partition includes a key value and location identifier for a corresponding table row in the corresponding table partition;

querying each determined index partition for index nodes whose key values satisfy the search predicates; and

returning data from the table rows addressed by the location identifiers in the index nodes whose key values satisfy the search predicates.

29. The article of manufacture of claim 28 , wherein the table rows are returned according to a sort criteria applied to all index nodes in the determined index partitions that satisfy the search predicates.

30. The article of manufacture of claim 29 , wherein the sort criteria applied to the determined index partitions, comprises:

scanning each determined index partition to determine one index node having a qualifying key value;

selecting one qualifying key value from the determined qualifying key values according to a criteria;

returning the table row addressed by the location identifier in the index node including the selected qualifying key value; and

scanning the index partition including the selected qualifying key value to determine one further qualifying key value to consider with any previously determined qualifying key values not selected.

31. The article of manufacture of claim 28 , further comprising:

selecting one qualifying key value from the qualifying key values selected from each of the multiple index partitions according to a criteria and sorted, wherein the returned data comprises the table row addressed by the location identifier in the index node having the selected key value.