MySQL Forums
Forum List  »  Partitioning

Partition pruning - how much optimization is smart?
Posted by: Michał Stolarczyk
Date: May 09, 2010 04:21AM

Hello,
I can't find more complex examples of select queries according to querying partitioned tables. So I don't know how smart optimization can be. I'll quote example from official documentation:

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
);

And now I want to:

SELECT * FROM t1
WHERE region_code > 125 AND region_code < 130 AND lname='123';

Of course it could be optimized to search only on one partition, but I don't know that optimization algorithm can handle it. Can anyone answer my question or tell how to check that optimization algorithm is handling query well?

Options: ReplyQuote


Subject
Views
Written By
Posted
Partition pruning - how much optimization is smart?
3489
May 09, 2010 04:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.