Partial indexes for partitioned tables

Patent metadata

Field	Value
Publication number	US-10282437-B2
Application number	US-201414255425-A
Country	US
Kind code	B2
Filing date	Apr 17, 2014
Priority date	Apr 17, 2014
Publication date	May 7, 2019
Grant date	May 7, 2019

Abstract

Techniques for creating and using partial indexes are provided. A partial index is an index that indexes one or more partitions of a partitioned table and does not index one or more other partitions of the partitioned table. Thus, if a partition of a partitioned table is rarely used, then an index may index all other partitions of the partitioned table or at least only those partitions that are written to or read from relatively frequently. Also, in preparation for loading data into a partition, indexing for the partition may be “turned off”, effectively making a full index a partial index. While the data is loaded into the partition, the partial index is still available for query processing. After the data is loaded into the partition, indexing for the partition is “turned on”, which causes the data in the partition to be indexed by the partial index.

First claim

What is claimed is:

1. A method for reducing an amount of storage required for storing an index, the method comprising:
receiving an instruction to create a table;
wherein the instruction indicates how the table is to be partitioned;
storing a plurality of partitions of the table;
storing the index that indexes data from multiple first partitions of the plurality of partitions and not from one or more second partitions of the plurality of partitions;
wherein the instruction includes index partitioning metadata;
storing the index partitioning metadata that identifies:
the multiple first partitions as being indexed by the index, and
the one or more second partitions as not being indexed by the index;

wherein storing the index partitioning metadata is based on the instruction;
receiving a query that involves the table;
determining to use the index to process the query;
wherein the method is performed by one or more computing devices.

2. The method of claim 1 , further comprising:
in response to receiving the query, for each entry of multiple entries of the index, determining whether said each entry corresponds to one of the one or more second partitions of the plurality of partitions.

3. The method of claim 2 , further comprising:
determining that a particular entry of said multiple entries corresponds to a partition of the one or more second partitions;
in response to determining that the particular entry corresponds to a partition of the one or more second partitions, filtering out said particular entry.

4. The method of claim 1 , further comprising:
generating, for the query, an execution plan that involves using the index to access one or more entries in the multiple first partitions and scanning the one or more second partitions of the plurality of partitions.

5. The method of claim 1 , further comprising:
receiving input that identifies a particular partition of the one or more second partitions of the plurality of partitions;
in response to receiving the input:
updating the index partitioning metadata to indicate that the particular partition is to be indexed by the index;
updating the index to index the particular partition along with the multiple first partitions of the plurality of partitions.

6. The method of claim 1 , wherein the index is a first index and the index partitioning metadata is first index partitioning metadata, the method further comprising:
while storing the first index partitioning metadata, storing second index partitioning metadata that is different than the second index partitioning metadata and that identifies at least one of the one or more second partitions as being indexed by a second index that is different than the first index.

7. A method for reducing an amount of storage required for storing an index, the method comprising:
storing a plurality of partitions of a table;
storing the index that indexes data from one or more first partitions of the plurality of partitions and not from one or more second partitions of the plurality of partitions;
storing index partitioning metadata that identifies:
the one or more first partitions as being indexed by the index, and
the one or more second partitions as not being indexed by the index;

receiving a query that involves the table;
determining to use the index to process the query;
wherein a prior version of the index indexed all partitions in the plurality of partitions;
wherein a prior version of the index partitioning metadata identified all partitions in the plurality of partitions as being indexed by the index;
wherein a current version of the index indexes only a strict subset of the plurality of partitions of the table.

8. The method of claim 7 , further comprising:
while the prior version of the index, is stored, receiving input that identifies the one or more second partitions of the plurality of partitions;
in response to receiving the input, modifying the index partitioning metadata to indicate the one or more second partitions as not being indexed by the index.

9. The method of claim 8 , wherein the input indicates a load instruction, further comprising:
in response to receiving the input, while the index partitioning metadata identifies the one or more second partitions as not being indexed by the index, causing loadable data that is indicated by the load instruction to be loaded into the one or more second partitions of the plurality of partitions;
after the loadable data is loaded into the one or more second partitions of the plurality of partitions, updating the index partitioning metadata to indicate that the one or more second partitions are to be indexed by the index.

10. The method of claim 9 , further comprising:
while the loadable data is being loaded into the one or more second partitions of the plurality of partitions, determining to use the index to process a second query;
while processing the query, using the index to identify first data that satisfies one or more search criteria indicated in the query and scanning the one or more second partitions to identify second data that satisfies the one or more search criteria.

11. One or more non-transitory computer-readable media storing instructions for reducing an amount of storage required for storing an index, wherein the instructions, when executed by one or more processors, cause:
receiving an instruction to create a table;
wherein the instruction indicates how the table is to be partitioned;
storing a plurality of partitions of the table;
storing the index that indexes data from one or more first partitions of the plurality of partitions and not from one or more second partitions of the plurality of partitions;
wherein the instruction includes index partitioning metadata;
storing the index partitioning metadata that identifies:
the one or more first partitions as being indexed by the index, and
the one or more second partitions as not being indexed by the index;

wherein storing the index partitioning metadata is based on the instruction;
receiving a query that involves the table;
determining to use the index to process the query.

12. The one or more non-transitory computer-readable media of claim 11 , wherein the instructions, when executed by the one or more processors, further cause:
in response to receiving the query, for each entry of multiple entries of the index, determining whether said each entry corresponds to one of the one or more second partitions of the plurality of partitions.

13. The one or more non-transitory computer-readable media of claim 12 , wherein the instructions, when executed by the one or more processors, further cause:
determining that a particular entry of said multiple entries corresponds to a partition of the one or more second partitions;
in response to determining that the particular entry corresponds to a partition of the one or more second partitions, filtering out said particular entry.

14. The one or more non-transitory computer-readable media of claim 11 , wherein the instructions, when executed by the one or more processors, further cause:
generating, for the query, an execution plan that involves using the index to access one or more entries in the one or more first partitions and scanning the one or more second partitions of the plurality of partitions.

15. The one or more non-transitory computer-readable media of claim 11 , wherein the instructions, when executed by the one or more processors, further cause:
receiving input that identifies a particular partition of the one or more second partitions of the plurality of partitions;
in re

Assignees

• Oracle Int Corp

Inventors

• Raghavan Ananth
• Eadon George
• Kumar Ramesh
• Baer Hermann
• Sridharan Suresh

Classifications

• G06F16/278

  Data partitioning, e.g. horizontal or vertical partitioning · CPC title

• G06F16/2228 Primary

  Indexing structures · CPC title

• G06F16/2272

  Management thereof · CPC title

• G06F16/22

  Indexing; Data structures therefor; Storage structures · CPC title

• G06F16/31

  Indexing; Data structures therefor; Storage structures · CPC title