Method, system and program for scrolling index scans

Abstract

A method, system and program for accessing records in a database object, such as an index or table. A cursor is maintained that points to a start entry in the database object. The database object has rows and columns of values. A runtime component receives a command to return a kth entry in the object having column values that satisfy predicates in a query statement. The runtime component calls a manager component that accesses entries in the database object to return the kth entry in the object having column values that satisfy the predicates. The manager component moves the cursor through sequential entries in the database object, in either the forward or reverse direction, until a kth entry that satisfies the predicates from the start entry is reached. The manager component positions the cursor to an entry that is the kth entry that satisfies the predicates from the start entry and returns data from the kth entry pointed to by the cursor to the runtime component.

What is claimed is:  
     
       1. A method for accessing records in a database object having rows and columns of values, comprising: 
       maintaining a cursor pointing to a start entry in the database object;  
       receiving, with a runtime component, a command to return a kth entry in the database object having column values that satisfy predicates in a query statement;  
       calling, with the runtime component, a manager component that accesses entries in the database object to return the kth entry in the object having column values that satisfy the predicates;  
       moving the cursor, with the manager component, through sequential entries in the database object until the manager component determines a kth entry that satisfies the predicates from the start entry;  
       positioning the cursor, with the manager component, to an entry that is the kth entry that satisfies the predicates from the start entry; and  
       returning, with the manager component, data from the kth entry pointed to by the cursor to the runtime component.  
     
     
       2. The method of  claim 1 , wherein the manager component further performs: 
       positioning the cursor to point to a first qualifying entry in the object if the command comprises an ABSOLUTE fetch command.  
     
     
       3. The method of  claim 1 , wherein the database object comprises an index to a base table, wherein the index has a subset of columns from the base table. 
     
     
       4. The method of  claim 3 , wherein the manager component comprises a data manager that manages access to the base table and an index manager that manages access to the index, wherein the query statement includes a select list of columns to return from an entry whose columns also satisfy the predicates in the query, wherein moving the cursor through the sequential entries in the index further comprises, for each cursor movement: 
       determining, with the index manager, whether columns in the index entry pointed to by the cursor satisfy the predicates;  
       determining, with the index manager, whether the cursor points to the entry that is the kth entry to satisfy the predicates from the start entry if the columns in the index satisfy the predicates, wherein returning the data from the kth entry comprises returning data from the columns in the select list; and  
       moving, with the index manager, the cursor to point to the next entry in the index if the columns in the index entry pointed to by the cursor do not satisfy the predicates.  
     
     
       5. The method of  claim 4 , wherein the runtime component calls the data manager to fetch the kth entry satisfying the predicates and the data manager calls the index manager to fetch the kth entry satisfying the predicates. 
     
     
       6. The method of  claim 4 , further performing, for each cursor movement: 
       determining, with the index manager, whether predicates must be applied to non-index columns in the base table to satisfy the predicates;  
       determining, with the data manager, whether non-index columns in the entry in the base table corresponding to the index entry pointed to by the cursor satisfy the predicates on the non-index columns if non-index columns in the base table must be considered in order to determine whether the predicates in the query statement are satisfied or not satisfied.  
     
     
       7. The method of  claim 6 , wherein moving the cursor through sequential entries until the kth entry satisfying the predicates is determined, further comprises: 
       modifying a counter with the index manager if the index manager determines that the entry satisfies the predicates by having the index columns satisfy the predicates, wherein the counter indicates when the kth entry satisfying the predicates is located; and  
       modifying the counter with the data manager if the data manager determines that the entry satisfies the predicates by having the non-index columns satisfy the predicates.  
     
     
       8. The method of  claim 7 , wherein returning the data from the kth entry pointed to by the cursor further comprises: 
       returning, with the index manager, data from the index columns to the data manager if the index manager determines that the index entry is the kth index entry to satisfy the predicates;  
       returning, with the data manager, the data for the index entry received from the index manager to the runtime component; and  
       returning, with the data manager data from the base table columns to the runtime component if the data manager determines that the index entry is the kth index entry to satisfy the predicates.  
     
     
       9. The method of  claim 1 , wherein moving the cursor, with the management component, through the sequential entries in the database object for each cursor movement further comprises: 
       moving the cursor to point to the next entry in the object and incrementing a counter if the columns in the object entry pointed to by the cursor do not satisfy the predicates; and  
       determining whether the counter equals k if the columns in the index satisfy the predicates, wherein the entry pointed to by the cursor is the kth entry if the counter equals k.  
     
     
       10. A system for accessing records in a database object having rows and columns of values, comprising: 
       a storage device;  
       a database object stored in the storage device;  
       a processor capable of accessing the storage device including the database object;  
       a runtime component and manager component programs embedded in a computer readable medium, wherein the processor executes the runtime and manager component programs to perform:  
       (i) maintaining a cursor pointing to a start entry in the database object;  
       (ii) receiving, with the runtime component, a command to return a kth entry in the object having column values that satisfy predicates in a query statement;  
       (iii) calling, with the runtime component, the manager component that accesses entries in the database object to return the kth entry in the object having column values that satisfy the predicates;  
       (iv) moving the cursor, with the manager component, through sequential entries in the database object until the manager component determines a kth entry that satisfies the predicates from the start entry;  
       (v) positioning the cursor, with the manager component, to an entry that is the kth entry that satisfies the predicates from the start entry; and  
       (vi) returning, with the manager component, data from the kth entry pointed to by the cursor to the runtime component.  
     
     
       11. The system of  claim 10 , wherein the processor further executes the manager component to perform: 
       positioning the cursor to point to a first qualifying entry in the object if the command comprises an ABSOLUTE fetch command.  
     
     
       12. The system of  claim 10 , wherein the database object comprises an index to a base table, wherein the index has a subset of columns from the base table. 
     
     
       13. The system of  claim 12 , wherein the manager component comprises a data manager that manages access to the base table and an index manager that manages access to the index, wherein the query statement includes a select list of columns to return from an entry whose columns also satisfy the predicates in the query, wherein moving the cursor through the sequential entries in the index further comprises the processor executing the index manager to perform for each cursor movement: 
       determining whether columns in the index entry pointed to by the cursor satisfy the predicates;  
       determining whether the cursor points to the entry that is the kth entry to satisfy the predicates from the start entry if the columns in the index satisfy the predicates, wherein returning the data from the kth entry comprises returning data from the columns in the select list; and  
       moving the cursor to point to the next entry in the index if the columns in the index entry pointed to by the cursor do not satisfy the predicates.  
     
     
       14. The system of  claim 13 , wherein the runtime component calls the data manager to fetch the kth entry satisfying the predicates and the data manager calls the index manager to fetch the kth entry satisfying the predicates. 
     
     
       15. The system of  claim 14 , wherein the processor executes the index manager and data manager to perform, for each cursor movement: 
       determining, with the index manager, whether predicates must be applied to non-index columns in the base table to satisfy the predicates;  
       determining, with the data manager, whether non-index columns in the entry in the base table corresponding to the index entry pointed to by the cursor satisfy the predicates on the non-index columns if non-index columns in the base table must be considered in order to determine whether the predicates in the query statement are satisfied or not satisfied.  
     
     
       16. The system of  claim 15 , wherein moving the cursor through sequential entries until the kth entry satisfying the predicates is determined, further comprises: 
       modifying a counter with the index manager if the index manager determines that the entry satisfies the predicates by having the index columns satisfy the predicates, wherein the counter indicates when the kth entry satisfying the predicates is located; and  
       modifying the counter with the data manager if the data manager determines that the entry satisfies the predicates by having the non-index columns satisfy the predicates.  
     
     
       17. The system of  claim 16 , wherein returning the data from the kth entry pointed to by the cursor further comprises: 
       returning, with the index manager, data from the index columns to the data manager if the index manager determines that the index entry is the kth index entry to satisfy the predicates;  
       returning, with the data manager, the data for the index entry received from the index manager to the runtime component; and  
       returning, with the data manager data from the base table columns to the runtime component if the data manager determines that the index entry is the kth index entry to satisfy the predicates.  
     
     
       18. The system of  claim 10 , wherein moving the cursor, with the management component, through the sequential entries in the database object for each cursor movement further comprises: 
       moving the cursor to point to the next entry in the object and incrementing a counter if the columns in the object entry pointed to by the cursor do not satisfy the predicates; and  
       determining whether the counter equals k if the columns in the index satisfy the predicates, wherein the entry pointed to by the cursor is the kth entry if the counter equals k.  
     
     
       19. A program for accessing records in a database object having rows and columns of values, wherein the program comprises a runtime component and manager component code embedded in a computer readable medium that is capable of causing a processor to perform: 
       maintaining a cursor pointing to a start entry in the database object;  
       receiving, with the runtime component, a command to return a kth entry in the object having column values that satisfy predicates in a query statement;  
       calling, with the runtime component, the manager component that manages accesses to the database object to return the kth entry in the object having column values that satisfy the predicates;  
       moving the cursor, with the manager component, through sequential entries in the database object until the manager component determines a kth entry that satisfies the predicates from the start entry;  
       positioning the cursor, with the manager component, to an entry that is the kth entry that satisfies the predicates from the start entry; and  
       returning, with the manager component, data from the kth entry pointed to by the cursor to the runtime component.  
     
     
       20. The program of  claim 19 , wherein the manager component is further capable of causing the processor to perform: 
       positioning the cursor to point to a first qualifying entry in the object if the command comprises an ABSOLUTE fetch command.  
     
     
       21. The program of  claim 19 , wherein the database object comprises an index to a base table, wherein the index has a subset of columns from the base table. 
     
     
       22. The program of  claim 21 , wherein the manager component comprises a data manager that manages access to the base table and an index manager that manages access to the index, wherein the query statement includes a select list of columns to return from an entry whose columns also satisfy the predicates in the query, wherein the index manager causes the processor to move the cursor through the sequential entries in the index, for each cursor movement, by: 
       determining whether columns in the index entry pointed to by the cursor satisfy the predicates;  
       determining whether the cursor points to the entry that is the kth entry to satisfy the predicates from the start entry if the columns in the index satisfy the predicates, wherein returning the data from the kth entry comprises returning data from the columns in the select list; and  
       moving the cursor to point to the next entry in the index if the columns in the index entry pointed to by the cursor do not satisfy the predicates.  
     
     
       23. The system of  claim 22 , wherein the runtime component calls the data manager to fetch the kth entry satisfying the predicates and the data manager calls the index manager to fetch the kth entry satisfying the predicates. 
     
     
       24. The program of  claim 22 , wherein the index manager and data manager are further capable of causing the processor to perform, for each cursor movement: 
       determining, with the index manager, whether predicates must be applied to non-index columns in the base table to satisfy the predicates;  
       determining, with the data manager, whether non-index columns in the entry in the base table corresponding to the index entry pointed to by the cursor satisfy the predicates on the non-index columns if non-index columns in the base table must be considered in order to determine whether the predicates in the query statement are satisfied or not satisfied.  
     
     
       25. The program of  claim 24 , wherein moving the cursor through sequential entries until the kth entry satisfying the predicates is determined, further comprises: 
       modifying a counter with the index manager if the index manager determines that the entry satisfies the predicates by having the index columns satisfy the predicates, wherein the counter indicates when the kth entry satisfying the predicates is located; and  
       modifying the counter with the data manager if the data manager determines that the entry satisfies the predicates by having the non-index columns satisfy the predicates.  
     
     
       26. The program of  claim 25 , wherein returning the data from the kth entry pointed to by the cursor further comprises: 
       returning, with the index manager, data from the index columns to the data manager if the index manager determines that the index entry is the kth index entry to satisfy the predicates;  
       returning, with the data manager, the data for the index entry received from the index manager to the runtime component; and  
       returning, with the data manager data from the base table columns to the runtime component if the data manager determines that the index entry is the kth index entry to satisfy the predicates.  
     
     
       27. The program of  claim 19 , wherein moving the cursor, with the management component, through the sequential entries in the database object for each cursor movement further comprises: 
       moving the cursor to point to the next entry in the object and incrementing a counter if the columns in the object entry pointed to by the cursor do not satisfy the predicates; and  
       determining whether the counter equals k if the columns in the index satisfy the predicates, wherein the entry pointed to by the cursor is the kth entry if the counter equals k.