Patent us20050251511 – optimizing execution of a database query by using the partitioning schema of … – google patents

One embodiment of the present invention provides a system that optimizes the execution of a database query involving a target partitioned-database-object. Database 5500 During system operation, the database receives a query. Data recovery kit If the query has a predicate that includes a partition-mapping function that uses the partitioning schema of a partitioned database-object and a list of columns from one or more tables to express a mapping of the list of column values to the partitions of the partitioned database-object, the system determines the compatibility of the partitioning schemas of the target partitioned-database-object and the partitioned database-object. Database meaning Next, if the partitioning schemas are compatible, and if the list of columns is compatible with the partitioning keys of the target partitioned-database-object and the partitioned database-object, the system attempts to identify a subset of partitions in the target partitioned-database-object that satisfy the predicate.


Gale database Finally, if a subset of partitions is successfully identified, the system performs the query only on the identified subset of partitions, and not on the other partitions, thereby optimizing the execution of the query by reducing the number of partitions that need to be accessed.

One embodiment of the present invention provides a system that optimizes the execution of a database query involving a target partitioned-database-object. Data recovery pc During system operation, the database receives a query. Data recovery easeus If the query has a predicate that includes a partition-mapping function that uses the partitioning schema of a partitioned database-object and a list of columns from one or more tables to express a mapping of the list of column values to the partitions of the partitioned database-object, the system determines the compatibility of the partitioning schemas of the target partitioned-database-object and the partitioned database-object. Top 10 data recovery software Next, if the partitioning schemas are compatible, and if the list of columns is compatible with the partitioning keys of the target partitioned-database-object and the partitioned database-object, the system attempts to identify a subset of partitions in the target partitioned-database-object that satisfy the predicate. Database oracle Finally, if a subset of partitions is successfully identified, the system performs the query only on the identified subset of partitions, and not on the other partitions, thereby optimizing the execution of the query by reducing the number of partitions that need to be accessed.

FIG. Data recovery services reviews 2 presents a flow chart illustrating the process of optimizing a database query in accordance with an embodiment of the present invention. Database 12c new features Upon receiving a database query, the system determines whether the query has a predicate that uses a partition-mapping function (step 202) that uses the partitioning schema 116 of a partitioned database-object 108 and a list of columns 112 in the partitioned table 106. Database lyrics An example of a database query that has a predicate that uses a partition-mapping function is shown below

In this query, “OPT(Y, X.C1, X.C2, X.C3)=N” is the predicate, wherein “OPT” is the partition-mapping function, “X” is the partitioned table, “Y” is the partitioned database-object, and “X.C1, X.C2, X.C3” is a list of columns in the partitioned table. Database 360 If the query has such a predicate, the system checks the compatibility of the partitioning techniques 140 and 150 (step 204). Database architect salary If the partitioning techniques 140 and 150 are compatible, the system then checks the compatibility of the list of columns 112 with the partitioning keys 142 and 152 (step 206), by checking whether the list of columns 112 is a prefix of the partitioning key 142 of the partitioned table, and by checking whether the data types of the columns in the list of columns 112 are compatible with the data types of the elements of the partitioning key 152. Database administrator job description If the list of columns 112 is compatible with the partitioning keys ( 142 and 152), the system then attempts to identify a subset of partitions (step 208). Data recovery app If a subset of partitions 120 is identified successfully, the system performs the query only on the identified subset of partitions 120 (step 210), and not on the other partitions 122, thereby optimizing the execution of the query by reducing the number of partitions that need to be accessed. R database connection For example, in the query shown above, if the partitioning schema of X is identical to the partitioning schema of Y, the system will access only the Nth partition; whereas, in the absence of the optimization, the system would have had to access all of the partitions.

banner