Oracle International Corporation

Hermann Baer

Ananth Raghavan

Ramesh Kumar

George Eadon

Suresh Sridharan

Rafi Ahmed

Bhaskar Ghosh

Mohamed Ziauddin

Techniques are provided for moving data between partitions. Such a process may be performed without requiring any locks that block transactions that target a partition from being executed. Instead, such transactions may proceed while a move operation is being performed. The move operation involves copying data from the targeted partition to another partition that is hidden from (or 'invisible' to) those transactions that attempt to read from or write to the partition. During the move operation, changes that are made to the partition are also reflected in a journal. Eventually, the changes reflected in the journal are drained and applied to the hidden partition. Once the partition and the hidden partition are synchronized, the identities of the partitions are swapped so that future transactions will target the previously-hidden partition instead of the previously-viewable partition.

Moving data between partitions

A database engine performs row filtering operations concurrently with performing database object partition maintenance operations. The filtering operations do not incur the high processing costs of database delete or purge operations that would incur per row costs for logging, locking, etc. The partitioning and filtering operations are specified in multiple clauses of a single database language statement. The clauses of the statement are executed concurrently. A method implementation receives a database language statement that comprises a partitioning operation specification as well as a filtering operation specification that includes a logical predicate for determining whether or not to filter-out predicate-matching entries when forming the resulting partitioned target database object. While processing the partitioning operations, the method discards entries taken from the source database object whenever the entry corresponds to the logical predicate. The target database object contains some entries that derive from the source, but does not contain the discarded entries.

Filtered partition maintenance operations

Techniques are described herein for creating a new database table for an exchange operation with a table partition of another table. A database management system (DBMS) is configured to receive a query such as 'CREATE TABLE new_table FOR EXCHANGE FROM partition_P'. The DBMS then identifies properties of the source partition (e.g., partition P) and creates a new database table based on the properties identified. Some of these properties (e.g., hidden columns, data modeling frameworks, and table statistics) may not otherwise be directly accessible by the external entity that sent the query. The newly created table then has all of the necessary properties to be part of the other table if swapped with the source partition in an exchange operation.

Create table for exchange

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.

Partial indexes for partitioned tables

Techniques for partition pruning based on aggregated zone map information. In one embodiment, for example, a method for pruning partitions based on aggregated zone map information comprises: receiving a query statement comprising a filter predicate on a column of a database table; and pruning one or more partitions of the database table from access paths for processing the query statement based on determining, based on aggregated zone map information associated with the one or more partitions, that the filter predicate cannot be satisfied by data stored in the one or more partitions.

Techniques for partition pruning based on aggregated zone map information

Techniques for maintaining a global index in response to a partition being dropped are provided. In response to an instruction to drop a partition, partition identification data that identifies the partition is stored. Index entries, in the global index, that correspond to the dropped partition become 'orphaned' entries. Later, an execution plan for a query is processed, where the execution plan targets a global index. During execution of the execution plan, one or more index entries are accessed. For each accessed index entry, the partition identification data is analyzed to determine if the index entry is an orphaned entry. If so, then the index entry is ignored for purposes of the query. Later, the global index may be updated to delete each orphaned entry. Such deletion may occur much later, such as during a time when the database is not queried or updated frequently.

Asynchronous global index maintenance during partition maintenance

Various techniques are described for processing database commands that include MINUS and/or INTERSECT operators. The queries containing the MINUS and/or INTERSECT operators are transformed to create a plurality of transformed queries. Each of the transformed queries produces the same result as the original query, but does not include the MINUS and/or INTERSECT operator. To achieve the same result set as the original query, the transformed queries employ equijoins, antijoins, and/or semijoins, and duplicate elimination operations. Costs are estimated for each of the various transformed queries. Based on the cost estimates, one of the transformed queries is selected as the query that is to be executed to perform the operations specified in the original query.

Multi-tiered query processing techniques for minus and intersect operators

Various techniques are described for processing database commands that include MINUS and/or INTERSECT operators. According to one technique, the database server executes the MINUS/INTERSECT in a specialized rowsource in parallel. In one approach, the specialized rowsource implements a sort merge-join like solution, where: a DISTINCT SORT is performed on each input, a left and right pointer is maintained on the respective input streams of tuples, the left or right pointer is incremented based on whether there is a match between the tuples pointed-to by the pointers, and the tuple of the left side is returned (or not returned) based on whether there is a match. Techniques are described for generating multiple query plans for executing a query, where each of the query plans includes a plan portion for executing, in parallel, the operation associated with a MINUS/INTERSECT operator. Cost estimates are generated for the query plans. The database server selects from the query plans a particular query plan to execute based, at least in part, on the cost estimates.

Growing community of inventors

San Mateo, CA, United States of America

Hermann Baer