MySQL Forums
Forum List  »  Partitioning

partition pruning doesnt work on joined tables?
Posted by:
Date: November 14, 2007 04:24AM

hello!

does partition pruning work on joined tables?

example tables t1 and t2:

CREATE TABLE t1 (
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION d5 VALUES LESS THAN (2000),
PARTITION d6 VALUES LESS THAN (2005),
PARTITION d7 VALUES LESS THAN MAXVALUE
);


CREATE TABLE t2 (
fname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) (
PARTITION d5 VALUES LESS THAN (2000),
PARTITION d6 VALUES LESS THAN (2005),
PARTITION d7 VALUES LESS THAN MAXVALUE
);

query:
SELECT fname, lname, COUNT(*) FROM t1, t2 WHERE t1.dob >= 2005 AND t1.dob < 2006 AND t2.dob >= 2005 AND t2.dob < 2006 AND t1.region_code = t2.region_code GROUP BY fname, lname;

i have tested that example and the query runtime is the same as without partitioning.

thanks,
norman



Edited 2 time(s). Last edit at 11/17/2007 06:03AM by .

Options: ReplyQuote


Subject
Views
Written By
Posted
partition pruning doesnt work on joined tables?
3693
November 14, 2007 04:24AM


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.