Filtered partition maintenance operations
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.
1. A computer-implemented method for performing row filtering operations when performing a database object partition maintenance operation that generates a target database object from a source database object, comprising:
receiving a database language statement to discard records from further processing with the source database object, the database language statement comprising both a partitioning operation specification and a filtering operation specification that includes a logical predicate that is used to determine whether or not to include or exclude a predicate-matching record in the target database object; and
creating a new partition from the source database object at least by processing a partitioning operation of the partitioning operation specification while processing the filtering operation to discard the records in the source database object from being populated into the partition when the records corresponds to the logical predicate, wherein
the partitioning operation defines one or more characteristics pertaining to a structure for the partition and creates partition by storing, together with the filtering operation, a subset of records in the source database object into the partition, and
the subset of records does not contain any of the records to be discarded from the further processing according to the database language statement.
2. The method of claim 1 , wherein the partitioning operation specification includes information at least one of a database table or a named partition.
3. The method of claim 1 , wherein processing the filtering operation does not generate a redo stream entry, and does not generate an undo stream entry.
4. The method of claim 1 , wherein processing the filtering operation does not perform any locking or unlocking operations pertaining to the records to be deleted.
5. The method of claim 1 , wherein the database language statement is a structured query language (SQL) statement, processing the partition of the partitioning operation specification creates the partition and a separate partition, and the separate partition includes the records that are discarded from being populated into the partition.
6. The method of claim 5 , wherein at least a portion of the SQL statement comprises an ALTER keyword.
7. The method of claim 5 , wherein the SQL statement includes at least one of, a MOVE keyword, a SPLIT keyword, or a MERGE keyword.
8. The method of claim 1 , wherein the logical predicate is a structured query language (SQL) predicate expression.
9. The method of claim 8 , wherein the SQL predicate expression comprises at least one of, a Boolean expression, or an inequality operator, or a join expression, or any combination thereof.
10. The method of claim 1 , wherein the source database object is an index.
11. A non-transitory computer readable medium having stored thereon a sequence of instructions which, when stored in memory and executed by one or more processors, causes the one or more processors to perform a set of acts for performing row filtering operations when performing a database object partition maintenance operation that generates a target database object from a source database object, the set of acts comprising:
receiving a database language statement to discard records from further processing with the source database object, the database language statement comprising both a partitioning operation specification and a filtering operation specification that includes a logical predicate that is used to determine whether or not to include or exclude a predicate-matching record in the target database object; and
creating a new partition from the source database object at least by processing a partitioning operation of the partitioning operation specification while processing the filtering operation to discard the records in the source database object from being populated into the partition when the records correspond to the logical predicate, wherein
the partitioning operation defines one or more characteristics pertaining to a structure for the partition and creates partition by storing, together with the filtering operation, a subset of records in the source database object into the partition, and
the subset of records does not contain any of the records to be discarded from the further processing according to the database language statement.
12. The computer readable medium of claim 11 , wherein the partitioning operation specification includes information about at least one of a database table or a named partition.
13. The computer readable medium of claim 11 , wherein processing the filtering operation does not generate a redo stream entry, and does not generate an undo stream entry.
14. The computer readable medium of claim 11 , wherein processing the filtering operation does not perform any locking or unlocking operations pertaining to the records to be deleted.
15. The computer readable medium of claim 11 , wherein the database language statement is a structured query language (SQL) statement, processing the partition of the partitioning operation specification creates the partition and a separate partition, and the separate partition includes the records that are discarded from being populated into the partition.
16. The computer readable medium of claim 15 , wherein at least a portion of the SQL statement comprises an ALTER keyword.
17. The computer readable medium of claim 15 , wherein the SQL statement includes at least one of, a MOVE keyword, a SPLIT keyword, or a MERGE keyword.
18. The computer readable medium of claim 11 , wherein the logical predicate is a structured query language (SQL) predicate expression.
19. A system for performing row filtering operations when performing a database object partition maintenance operation that generates at least one target database object from a source database object, comprising:
a non-transitory storage medium having stored thereon a sequence of instructions; and
one or more processors that execute the sequence of instructions, an execution of sequence of instructions causing the one or more processors to perform a set of acts, the set of acts comprising,
receiving a database language statement to discard records from further processing with the source database object, the database language statement comprising both a partitioning operation specification and a filtering operation specification that includes a logical predicate that is used to determine whether or not to include or exclude a predicate-matching record in the target database object, and
creating a new partition from the source database object at least by processing a partitioning operation of the partitioning operation specification while processing the filtering operation to discard the records in the source database object from being populated into the partition when the records correspond to the logical predicate, wherein
the partitioning operation defines one or more characteristics pertaining to a structure for the partition and creates the partition by storing, together with the filtering operation, a subset of records in the source database object into the partition, and
the subset of records does not contain any of the records discarded from the further processing deleted from the source database object according to an execution of the database language statement.
20. The system of claim 19 , wherein the partitioning operation specification includes information about at least one of a database table or a named partition, processing the partition of the partitioning operation specification creates the partition and a separate partition, and the separate partition includes the records that are discarded from being populated into the partition.