SYSTEMS AND/OR METHODS FOR CONDITIONAL DATA PARTITIONING
A partitioning scheme for a database has an associated partitioning scheme identifier, a partition-defining query, and an indication of a primary table to be partitioned. A first, primary table is associated with first and second partition types, the second being a query type for which the identifier is passed as a parameter. Each of one or more second tables has an associated partition type, namely, a query type for which the identifier is passed as a parameter. The partition-defining query includes one or more outer queries conditioned on an innermost query, the innermost query defined on the first table, each outer query querying a table other than the first. The first table is partitioned based on the first partition type. The database is updated by making a change to a partition of the first table and to a partition of each second table based on the partition-defining query.
1 . A method of managing a database stored to a data store, the method comprising:
receiving a definition of a partitioning scheme, the definition of the partitioning scheme having associated therewith a partitioning scheme identifier, a partition-defining query, and an indication of a primary table to be partitioned;
receiving a definition of a first table, the first table being the primary table, the definition of the first table having associated therewith at least first and second partition types by which the first table is to be partitioned, the second partition type being a query type for which the partitioning scheme identifier is passed as a parameter;
receiving a definition of one or more second tables, each said second table being defined so as to have associated therewith a partition type by which the respective second table is to be partitioned, the partition type of each said second table being a query type for which the partitioning scheme identifier is passed as a parameter,
wherein the partition-defining query includes one or more outer queries conditioned on an innermost query, the innermost query being a query on the first table, each of the one or more outer queries being a query on a table in the database other than the first table;
partitioning the first table based on the first partition type; and
updating the database in response to an operation performed thereon, wherein the updating includes making a change to a partition of the first table and to a partition of each said second table based on an outcome of the partition-defining query.
2 . The method of claim 1 , wherein the first partition type is a range or list type partition.
3 . The method of claim 1 , wherein two or more second tables are included in the database, and wherein the partition-defining query is nested such that a first outcome of a first query on one of the second tables is conditioned on an outcome of the innermost query, and such that a second outcome on a second query on another one of the second tables is conditioned on the first outcome.
4 . The method of claim 1 , wherein each definition of each first table is provided in a respective create table statement.
5 . The method of claim 1 , wherein changes are made to the second table(s) provided that a flag indicates that changes are to be cascaded thereto.
6 . The method of claim 1 , wherein the operation is an insert or update data operation.
7 . The method of claim 1 , wherein the operation is an archive and/or purge operation where at least some data from the first and second tables is to be archived and/or purged.
8 . The method of claim 7 , wherein a first set of data is to be archived and/or purged from a first partition of the first table and a second set of data is to be archived and/or purged from a counterpart partition of the second table(s).
9 . A non-transitory computer readable storage medium storing instructions that, when executed by at least one processor, perform operations comprising:
receiving a definition of a partitioning scheme for a database, the definition of the partitioning scheme having associated therewith a partitioning scheme identifier, a partition-defining query, and an indication of a primary table to be partitioned;
receiving a definition of a first table, the first table being the primary table, the definition of the first table having associated therewith at least first and second partition types by which the first table is to be partitioned, the second partition type being a query type for which the partitioning scheme identifier is passed as a parameter;
receiving a definition of one or more second tables, each said second table being defined so as to have associated therewith a partition type by which the respective second table is to be partitioned, the partition type of each said second table being a query type for which the partitioning scheme identifier is passed as a parameter,
wherein the partition-defining query includes one or more outer queries conditioned on an innermost query, the innermost query being a query on the first table, each of the one or more outer queries being a query on a table in the database other than the first table;
partitioning the first table based on the first partition type; and
updating the database in response to an operation performed thereon, wherein the updating includes making a change to a partition of the first table and to a partition of each said second table based on an outcome of the partition-defining query.
10 . The non-transitory computer readable storage medium of claim 9 , wherein the first partition type is a range or list type partition.
11 . The non-transitory computer readable storage medium of claim 9 , wherein two or more second tables are included in the database, and wherein the partition-defining query is nested such that a first outcome of a first query on one of the second tables is conditioned on an outcome of the innermost query, and such that a second outcome on a second query on another one of the second tables is conditioned on the first outcome.
12 . The non-transitory computer readable storage medium of claim 9 , wherein changes are made to the second table(s) provided that a flag indicates that changes are to be cascaded thereto.
13 . The non-transitory computer readable storage medium of claim 9 , wherein the operation is an archive and/or purge operation where at least some data from the first and second tables is to be archived and/or purged.
14 . The non-transitory computer readable storage medium of claim 13 , wherein a first set of data is to be archived and/or purged from a first partition of the first table and a second set of data is to be archived and/or purged from a counterpart partition of the second table(s).
15 . A data management system, comprising:
a data store;
a database stored to the data store; and
a memory coupled to at least one hardware processor configured to perform operations comprising:
receiving a definition of a partitioning scheme for the database, the definition of the partitioning scheme having associated therewith a partitioning scheme identifier, a partition-defining query, and an indication of a primary table to be partitioned;
receiving a definition of a first table, the first table being the primary table, the definition of the first table having associated therewith at least first and second partition types by which the first table is to be partitioned, the second partition type being a query type for which the partitioning scheme identifier is passed as a parameter;
receiving a definition of one or more second tables, each said second table being defined so as to have associated therewith a partition type by which the respective second table is to be partitioned, the partition type of each said second table being a query type for which the partitioning scheme identifier is passed as a parameter,
wherein the partition-defining query includes one or more outer queries conditioned on an innermost query, the innermost query being a query on the first table, each of the one or more outer queries being a query on a table in the database other than the first table;
partitioning the first table based on the first partition type; and
updating the database in response to an operation performed thereon, wherein the updating includes making a change to a partition of the first table and to a partition of each said second table based on an outcome of the partition-defining query.
16 . The system of claim 15 , wherein the first partition type is a range or list type partition.
17 . The system of claim 15 , wherein two or more second tables are included in the database, and wherein the partition-defining query is nested such that a first outcome of a first query on one of the second tables is conditioned on an outcome of the innermost query, and such that a second outcome on a second query on another one of the second tables is conditioned on the first outcome.
18 . The system of claim 15 , wherein changes are made to the second table(s) provided that a flag indicates that changes are to be cascaded thereto.
19 . The system of claim 15 , wherein the operation is an archive and/or purge operation where at least some data from the first and second tables is to be archived and/or purged.
20 . The system of claim 19 , wherein a first set of data is to be archived and/or purged from a first partition of the first table and a second set of data is to be archived and/or purged from a counterpart partition of the second table(s).