Method and mechanism for database partitioning

• US 20050038784A1
• Filed: 09/27/2004
• Published: 02/17/2005
• Est. Priority Date: 02/27/2001
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Zait, Mohamed, Dageville, Benoit
• Granted Patent: US 7,680,761 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/22 Indexing; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/24557 Efficient disk access during query execution
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

The present invention is directed to a method and mechanism for partitioning using information not directly located in the object being partitioned. According to an embodiment of the invention, foreign key-primary key relationships are utilized to create join conditions between multiple database tables to implement partitioning of a database object. Also, disclosed are methods and mechanisms to perform partition pruning.

Claims

1. A method of executing a query against a first database object that is partitioned using a partition definition based upon information located in a second database object, comprising:

accessing metadata containing the partition definition;

matching the partition definition against a predicate in the query to determine a set of one or more partitions not relevant to the query; and

pruning the set of one or more partitions not relevant to the query.

2. The method of claim 1 in which the act of pruning is performed at compile time for the query.

3. The method of claim 1 in which the metadata is stored in a data dictionary.

4. The method of claim 1 in which the query contains one or more bind variables, and the act of pruning is performed after values are determined for the one or more bind variables.

5. The method of claim 3 in which the act of pruning is performed at execution time for the query.

6. The method of claim 1, wherein at least one hierarchical level exists between the first database object and the second database object, the information in the second database object being indirectly referenced through the at least one hierarchical level.

7. The method of claim 1, where the first partition definition and a second partition definition are based on information located in a third database object.

8. A computer program product that includes a medium usable by a processor, the medium having stored thereon a sequence of instructions which, when executed by said processor, causes said processor to execute a process for executing a query against a first database object that is partitioned using a partition definition based upon information located in a second database object, said process comprising:

accessing metadata containing the partition definition;

matching the partition definition against a predicate in the query to determine a set of one or more partitions not relevant to the query; and

pruning the set of one or more partitions not relevant to the query.

9. The computer program product of claim 8 in which the act of pruning is performed at compile time for the query.

10. The computer program product of claim 8 in which the metadata is stored in a data dictionary.

11. The computer program product of claim 8 in which the query contains one or more bind variables, and the act of pruning is after values are determined for the one or more bind variables.

12. The computer program product of claim 11 in which the act of pruning is performed at execution time for the query.

13. The computer program of claim 8, wherein at least one hierarchical level exists between the first database object and the second database object, the information in the second database object being indirectly referenced through the at least one hierarchical level.

14. The computer program of claim 8, where the first partition definition and a second partition definition are based on information located in a third database object.

15. A system for executing a query against a first database object that is partitioned using a partition definition based upon information located in a second database object, the system comprising:

means for accessing metadata containing the partition definition;

means for matching the partition definition against a predicate in the query to determine a set of one or more partitions not relevant to the query; and

means for pruning the set of one or more partitions not relevant to the query.

16. The system of claim 15 in which pruning is performed at compile time for the query.

17. The system of claim 15 in which the metadata is stored in a data dictionary.

18. The system of claim 15 in which the query contains one or more bind variables and pruning is performed after values are determined for the one or more bind variables.

19. The system of claim 18 in which pruning is performed at execution time for the query.

20. The system of claim 15, where the first partition definition and a second partition definition are based on information located in a third database object.