Which two types of column filtering may benefit from partition pruning?
A. Equally operates on range-partitioned tables.
B. In-list operators on system-partitioned tables
C. Equality operators on system-partitioned tables
D. Operators on range-partitioned tables
E. Greater than operators on hash-partitioned tables
Correct Answer: AD
Explanation/Reference:
The query optimizer can perform pruning whenever a WHERE condition can be
reduced to either one of the following two cases:
partition_column = constant
partition_column IN (constant1, constant2, …, constantN)
In the first case, the optimizer simply evaluates the partitioning expression for the value given, determines which partition contains that value, and scans only this partition. In many cases, the equal sign can be replaced with another arithmetic comparison, including <, >, <=, >=, and <>.
Some queries using BETWEEN in the WHERE clause can also take advantage of partition pruning.
Note:
*The core concept behind partition pruning is relatively simple, and can be described as “Do not scan partitions where there can be no matching values”.
When the optimizer can make use of partition pruning in performing a query, execution of the query can be an order of magnitude faster than the same query against a nonpartitioned table containing the same column definitions and data.
* Example:
Suppose that you have a partitioned table t1 defined by this statement:
CREATE TABLE t1 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) (
PARTITION p0 VALUES LESS THAN (64),
PARTITION p1 VALUES LESS THAN (128),
PARTITION p2 VALUES LESS THAN (192),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Consider the case where you wish to obtain results from a query such as this one:
SELECT fname, lname, region_code, dob
FROM t1
WHERE region_code > 125 AND region_code < 130;
It is easy to see that none of the rows which ought to be returned will be in either of the partitions p0 or p3; that is, we need to search only in partitionsp1 and p2 to find matching rows. By doingso, it is possible to expend much less time and effort in finding matching rows than would be required to scan all partitions in the table. This”cutting away” of unneeded partitions is known aspruning.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
