Dropping an index without blocking locks

Patent metadata

Field	Value
Publication number	US-11360802-B2
Application number	US-202016887980-A
Country	US
Kind code	B2
Filing date	May 29, 2020
Priority date	Mar 31, 2014
Publication date	Jun 14, 2022
Grant date	Jun 14, 2022

Abstract

Techniques for processing “destructive” database statements are provided. Destructive database statements, when processed, cause metadata of a database object to be changed. Examples of such database statements include ones that delete an index, that set a column as unused, and that drop a constraint on a column. When such a statement is received, a change is made to metadata of a database object. Such a metadata change may involve setting an index as unusable, disabling a constraint, or invalidating a cursor. After the metadata change, a first time is determined. Then, it is determined when one or more database transactions that were pending at the first time have committed. After those database transaction(s) have committed, one or more operations are performed, such as dropping an index or dropping a constraint.

First claim

What is claimed is:

1. A method comprising:
while storing metadata that indicates that an index is usable, receiving an instruction to drop the index that is based on a table;
in response to receiving the instruction and without using a lock, to process the instruction, that would block transactions involving the table:
updating the metadata to indicate that the index is unusable;
after updating the metadata, determining a particular timestamp;
waiting for one or more transactions that are pending at a time associated with the particular timestamp to commit;
in response to determining that the one or more transactions have committed, dropping the index;
wherein the method is performed by one or more computing devices.

2. The method of claim 1 , further comprising:
in response to receiving the instruction and prior to determining the particular timestamp, invalidating one or more cursors that are associated with the index.

3. The method of claim 1 , further comprising:
in response to receiving the instruction, acquiring a lock on the table upon which the index is based, wherein the lock does not prevent other database transactions from modifying the table.

4. The method of claim 1 , wherein updating, determining, waiting, and dropping are performed within a database transaction.

5. The method of claim 4 , wherein:
the database transaction is a first database transaction;
updating is performed within a second database transaction that is within the first database transaction.

6. The method of claim 1 , wherein waiting comprises calling a function that takes, as input, the particular timestamp and an object identifier that identifies the index or the table.

7. The method of claim 1 , wherein the one or more transactions involve accessing the index.

8. The method of claim 7 , further comprising:
after updating the metadata and prior to the one or more transactions committing, processing the one or more transactions.

9. A method comprising:
while storing metadata that indicates that a constraint on a column is usable, receiving an instruction to modify the metadata that is associated with the column of a table;
in response to receiving the instruction:
updating the metadata to indicate that the constraint on the column of the table is disabled;
determining a particular timestamp;
waiting for one or more transactions that are pending at a time associated with the particular timestamp to commit;
in response to determining that the one or more transactions have committed, dropping the constraint;
wherein the method is performed by one or more computing devices.

10. The method of claim 9 , wherein the instruction is to drop the constraint of the column of the table.

11. The method of claim 9 , wherein:
the instruction is to set the column unused;
the method further comprising, prior to determining the particular timestamp, storing second data that indicates that the column is unusable.

12. The method of claim 11 , further comprising:
prior to determining the particular timestamp, determining whether the constraint on the column exists;
wherein updating the metadata comprises updating the metadata that indicates that the constraint is disabled in response to determining that the constraint on the column exists.

13. The method of claim 9 , further comprising:
in response to receiving the instruction and prior to determining the particular timestamp:
determining whether an index exists on the column;
if it is determined that an index exists on the column, then storing data that indicates that the index is unusable;

in response to determining that the one or more transactions have committed, dropping the index if it is determined that the index exists.

14. The method of claim 9 , further comprising:
in response to receiving the instruction and prior to determining the particular timestamp, invalidating one or more cursors.

15. The method of claim 9 , wherein waiting comprises calling a function that takes, as input, the particular timestamp and an object identifier that identifies the column or the table.

16. One or more non-transitory computer-readable media storing instructions which, when executed by one or more processors, cause:
while storing metadata that indicates that an index is usable, receiving an instruction to drop the index that is based on a table;
in response to receiving the instruction and without using a lock, to process the instruction, that would block transactions involving the table:
updating the metadata to indicate that the index is unusable;
after updating the metadata, determining a particular timestamp;
waiting for one or more transactions that are pending at a time associated with the particular timestamp to commit;
in response to determining that the one or more transactions have committed, dropping the index.

17. The one or more non-transitory computer-readable media of claim 16 , wherein the instructions, when executed by the one or more processors, further cause:
in response to receiving the instruction and prior to determining the particular timestamp, invalidating one or more cursors that are associated with the index or with the table upon which the index is based.

18. The one or more non-transitory computer-readable media of claim 16 , wherein the instructions, when executed by the one or more processors, further cause:
in response to receiving the instruction, acquiring a lock on the table upon which the index is based, wherein the lock does not prevent other database transactions from modifying the table.

19. The one or more non-transitory computer-readable media of claim 16 , wherein updating, determining, waiting, and dropping are performed within a database transaction.

20. The one or more non-transitory computer-readable media of claim 19 , wherein:
the database transaction is a first database transaction;
updating is performed within a second database transaction that is within the first database transaction.

Assignees

• Oracle Int Corp

Inventors

• Kumar Ramesh
• Raghavan Ananth
• Eadon George

Classifications

• G06F16/2343

  Locking methods, e.g. distributed locking or locking implementation details · CPC title

• G06F16/2308

  Concurrency control (transaction processing G06F9/466) · CPC title

• G06F16/22 Primary

  Indexing; Data structures therefor; Storage structures · CPC title

• G06F9/466 Primary

  Transaction processing · CPC title