Online index builds and rebuilds without blocking locks

US8195702B2 · kind B2 · utility

Assignee

• Oracle International Corporation · US

Inventors

• Shrikanth Shankar · Mountain View, US
• Ramesh Kumar · San Francisco, US
• Krishna Kunchithapadam · Portland, US
• Ananth Raghavan · San Francisco, US
• Dheeraj Pandey · San Jose, US
• Amit Ganesh · San Jose, US
• Joy Forsythe · Cambridge, US

Key dates

• Filing date: Jul 30, 2007
• Grant date: Jun 5, 2012
• Expiry date: Jun 15, 2029

Classification

• Technology area (CPC G): Physics
• CPC primary: G06F16/2272
• WIPO field: Computer technology
• WIPO sector: Electrical engineering

Abstract

Techniques are provided for processing a CREATE INDEX statement that avoids one or more locks on the underlying table. The processing of such a statement may be divided into a build phase and a cooperation phase. During the build phase, a journal table is created and published. A snapshot of the table is obtained after the last DML transaction (that was pending at the time the journal table was published) commits. The index is built based on the state of the table as of the snapshot. Any changes to the table while the index is built are recorded in the journal table. During the cooperation phase, the index is partially published. A subsequent DML statement determines whether any changes in the journal table affect the same data as the subsequent DML statement. If so, then those changes are applied to the index followed by the subsequent DML statement.