Accessing data from asynchronously maintained index

• US 20080249990A1
• Filed: 04/05/2007
• Published: 10/09/2008
• Est. Priority Date: 04/05/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Baby, Thomas, Chandrasekar, Sivasankaran, Agarwal, Nipun
• Granted Patent: US 7,814,117 B2
• Time in Patent Office: Days
• US Class Current: 707/3
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• CPC Class Codes: G06F 16/81 Indexing, e.g. XML tags; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/8373 Query execution

Abstract

A method and apparatus for computing queries in a way that is consistent with an index and the underlying base table is provided. A query is computed such that it is consistent with the state of an index and base table as of the last point at which the index and table were synchronized. For example, if an index is maintained asynchronously then it is not necessarily consistent with its base table when a query is to be run against the table and the index. Therefore, the query is computed such that it will be consistent with the last point at which the index and the table were synchronized. In one embodiment, a System Change Number (SCN) of the last successful synchronization operation is used to perform a flashback query on the table.

Claims

1. A method comprising:

tracking a logical point at which an index was synchronized with an indexed table indexed by the index; and

providing results for a query based on a state of the index and the indexed table as of the logical point.

2. The method of claim 1, wherein tracking the logical point comprises storing a system change number (SCN) in association with the index.

3. The method of claim 1, wherein tracking the logical point comprises storing a timestamp.

4. The method of claim 1, wherein providing results for the query comprises providing data that reflects the state of the indexed table as of the logical point.

5. The method of claim 1, wherein the query is a first query and providing results for the first query comprises executing a flashback query that is based on the first query and the logical point.

6. A method comprising:

computing a query in a way that is consistent with a previous point at which a table and an index were synchronized.

7. The method of claim 6, wherein computing the query comprises adding a clause to the query such that the query, when executed, causes data to be retrieved from the table and modified using log records so that the data reflects the state of the table as of the previous point.

8. The method of claim 6, wherein computing the query comprises computing a flashback query that references the previous point.

9. The method of claim 6, further comprising providing data that reflects the state of the table as of the previous point.

10. The method of claim 9, wherein providing data comprises accessing the index.

11. A method comprising:

tracking a logical point at which an index was synchronized with an indexed table;

receiving a query to be run against the indexed table; and

re-writing the query based on the logical point.

12. The method of claim 11, wherein rewriting the query includes rewriting the query to reference one or more base structures comprising the index.

13. The method of claim 11, wherein re-writing the query comprises adding to the query a clause that specifies that data is to be provided that reflects the state of the indexed table as of the logical point.

14. The method of claim 11, further comprising storing the query as re-written.

15. The method of claim 13, further comprising invalidating the stored query in response to the index and the indexed table being synchronized.