Implementing a logically partitioned data warehouse using a container map

• US 10,628,422 B2
• Filed: 10/21/2016
• Issued: 04/21/2020
• Est. Priority Date: 10/23/2015
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Baby, Thomas
• Primary Examiner(s): Owyang, Michelle N
• Application Number:
  US15/331,540
• Publication Number:
  US 20170116278A1
• Time in Patent Office: 1,278 Days
• Field of Search: 707705-780
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/24554 Unary operations; Data partitioning operations
• CPC Class Codes: G06F 16/25 Integrating or interfacing systems involving database management systems
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

In an approach, a database management system logically partitions a database object (such as a table) across multiple pluggable databases of a container database. When a database server receives a query at an application root, the database server consults a container map which provides a mapping between a set of partitioning criteria and a set of member pluggable databases of the application root. Using the container map, the database server identifies one or more pluggable databases of the set of member pluggable databases of the application root that contain records which potentially have the ability to match the predicates of the query. The database server then limits the execution of the query to the identified pluggable databases, effectively pruning away records contained by the other pluggable databases to increase the efficiency of executing the query.

Claims

1. A method comprising:

receiving at a first database server a query from a database client during a session with an application root specifying to retrieve one or more records of a database object that match one or more predicates from a plurality of member pluggable databases of the application root, wherein the plurality of member pluggable databases are contained within a container database to which the first database server provides access;

the first database server identifying one or more particular pluggable databases of the plurality of member pluggable databases that are implicated by the query based on a container map stored within the application root, wherein the container map specifies a mapping between each partitioning criterion of a set of partitioning criteria and a corresponding pluggable database of the plurality of member pluggable databases;

the first database server executing the query only on each pluggable database of the one or more particular pluggable databases to produce a respective result set of records from the database object;

the first database server aggregating the respective result set of results from each pluggable database of the one or more particular pluggable databases into an aggregated result set for the query;

the first database server sending the aggregated result set to the database client.

2. The method of claim 1, wherein said set of partitioning criteria of the container map is based on one of:

a list partitioning scheme, a range partitioning scheme, a hash partitioning scheme, or a composite partitioning scheme.

3. The method of claim 1, wherein the partitioning criteria is based on a partitioning key representing a value of an attribute within the database object.

4. The method of claim 1, wherein identifying the one or more particular pluggable databases includes comparing the one or more predicates of the query to the set of partitioning criteria and determining which of the plurality of member pluggable databases stores records which could potentially match the one or more predicates.

5. The method of claim 1, wherein receiving at a first database server a query includes a query coordinator process of the first database server spawning one or more slave processes to execute the query on each pluggable database of the one or more particular pluggable databases in parallel.

6. The method of claim 5, further comprising rewriting the query before providing the query to the one or more slave processes, wherein rewriting the query causes the one or more predicates to be applied by the one or more slave processes in parallel.

7. The method of claim 5, wherein the query requires a join to be performed between the database object and a second database object and further comprising:

in response to a determination that the join is to be performed locally within each pluggable database of the one or more particular pluggable databases, rewriting the query before providing the query to the one or more slave processes, wherein rewriting the query causes the join to be performed by the one or more slave processes in parallel.

8. The method of claim 1, further comprising:

in response to a determination that at least one of the one or more particular pluggable databases is a proxy pluggable database, using information stored within the proxy pluggable database to forward the query to a target pluggable database of the proxy pluggable database that is contained within a second container database and accessible through a second database server.

9. The method of claim 8, wherein the target pluggable database is an application root replica and the second database server, in response to receiving the query, identifies a second one or more particular pluggable databases that are members of the application root replica within the second container database which are implicated by the query, wherein the second one or more particular pluggable databases are identified by consulting a second container map stored by the application root replica that specifies a second mapping between each partitioning criteria of a second set of partitioning criteria and a corresponding pluggable database of the members of the application root replica.

10. The method of claim 9, wherein the second database server executes the query only on each pluggable database of the second one or more particular pluggable databases, aggregates a result of executing the query on each pluggable database of the one or more pluggable databases into a second aggregated result, and sends the second aggregated result back to the first database server.

11. The method of claim 8, wherein the query requires a join to be performed between the database object and a second database object and further comprising:

in response to a determination that the second database server applying the join would cause execution of the query at the second database server to produce fewer records than without applying the join, the first database server rewriting the query to cause the join to be performed by the second database server.

12. The method of claim 11, wherein the determination that the second database server applying the join would cause execution of the query at the second database server to produce fewer records than without applying the join is performed based on one or more statistics related to database objects stored by the target pluggable database that are periodically sent to the first database server from the second database server and stored within the proxy pluggable database.

13. A non-transitory computer-readable storage medium storing one or more sequences of instructions that, when executed by one or more processors, cause:

receiving at a first database server a query from a database client during a session with an application root specifying to retrieve one or more records of a database object that match one or more predicates from a plurality of member pluggable databases of the application root, wherein the plurality of member pluggable databases are contained within a container database to which the first database server provides access;

the first database server identifying one or more particular pluggable databases of the plurality of member pluggable databases that are implicated by the query based on a container map stored within the application root, wherein the container map specifies a mapping between each partitioning criterion of a set of partitioning criteria and a corresponding pluggable database of the plurality of member pluggable databases;

the first database server executing the query only on each pluggable database of the one or more particular pluggable databases to produce a respective result set of records from the database object;

the first database server aggregating the respective result set of results from each pluggable database of the one or more particular pluggable databases into an aggregated result set for the query;

the first database server sending the aggregated result set to the database client.

14. The non-transitory computer-readable storage medium of claim 13, wherein the set of partitioning criteria of the container map is based on one of:

a list partitioning scheme, a range partitioning scheme, a hash partitioning scheme, or a composite partitioning scheme.

15. The non-transitory computer-readable storage medium of claim 13, wherein the set of partitioning criteria is based on a partitioning key representing a value of an attribute within the database object.

16. The non-transitory computer-readable storage medium of claim 13, wherein identifying the one or more particular pluggable databases includes comparing the one or more predicates of the query to the set of partitioning criteria and determining which of the plurality of member pluggable databases stores records which could potentially match the one or more predicates.

17. The non-transitory computer-readable storage medium of claim 13, wherein receiving at a first database server a query includes a query coordinator process of the first database server spawning one or more slave processes to execute the query on each pluggable database of the one or more particular pluggable databases in parallel.

18. The non-transitory computer-readable storage medium of claim 17, wherein the sequences of instructions include instructions that, when executed by the one or more processors, cause rewriting the query before providing the query to the one or more slave processes, wherein rewriting the query causes the one or more predicates to be applied by the one or more slave processes in parallel.

19. The non-transitory computer-readable storage medium of claim 17, wherein the query requires a join to be performed between the database object and a second database object and the sequences of instructions include instructions that, when executed by said one or more processors, cause:

in response to a determination that the join is to be performed locally within each pluggable database of the one or more particular pluggable databases, rewriting the query before providing the query to the one or more slave processes, wherein rewriting the query causes the join to be performed by the one or more slave processes in parallel.

20. The non-transitory computer-readable storage medium of claim 13, wherein the sequences of instructions include instructions that, when executed by said one or more processors, cause:

in response to a determination that at least one of the one or more particular pluggable databases is a proxy pluggable database, using information stored within the proxy pluggable database to forward the query to a target pluggable database of the proxy pluggable database that is contained within a second container database and accessible through a second database server.

21. The non-transitory computer-readable storage medium of claim 20;

wherein the target pluggable database is an application root replica, and

wherein the sequences of instructions include instructions that, when executed by said one or more processors, cause;

the second database server, in response to receiving the query, identifying a second one or more particular pluggable databases that are members of the application root replica within the second container database which are implicated by the query, wherein the second one or more particular pluggable databases are identified by consulting a second container map stored by the application root replica that specifies a second mapping between each partitioning criteria of a second set of partitioning criteria and a corresponding pluggable database of the members of the application root replica.

22. The non-transitory computer-readable storage medium of claim 21, wherein the sequences of instructions include instructions that, when executed by said one or more processors, cause:

the second database server to execute the query only on each pluggable database of the second one or more particular pluggable databases, aggregate a result of executing the query on each pluggable database of the one or more pluggable databases into a second aggregated result, and send the second aggregated result back to the first database server.

23. The non-transitory computer-readable storage medium of claim 20, wherein the query requires a join to be performed between the database object and a second database object, and wherein the sequences of instructions include instructions that, when executed by said one or more processors, cause:

in response to a determination that the second database server applying the join would cause the execution of the query at the second database server to produce fewer records than without applying the join, the first database server rewriting the query to cause the join to be performed by the second database server.

24. The non-transitory computer-readable storage medium of claim 23, wherein the sequences of instructions include instructions that, when executed by said one or more processors, cause:

the determination that the second database server applying the join would cause execution of the query at the second database server to produce fewer records than without applying the join to be based on one or more statistics related to database objects stored by the target pluggable database that are periodically sent to the first database server from the second database server and stored within the proxy pluggable database.