MySQL Forums
Forum List  »  Partitioning

Re: particular field type not allowed to this type of partitioning
Posted by: Aftab Khan
Date: August 24, 2012 01:59AM

All columns used in the partitioning expression for a partitioned table must be part of every unique/PRIMARY key that the table may have. e.g.

UNIQUE KEY fldattendanceid (fldattendanceid, fldassigndate )

Few other mistakes:
>PARTITION BY RANGE (fldassigndate)
it should be like:
PARTITION BY RANGE (TO_DAYS(fldassigndate))

>fldassigndate varchar(20) DEFAULT NULL,
It should be like:
fldassigndate DATE DEFAULT NULL,

And don't forget to catch for invalid dates,
>PARTITION invalid_date VALUES LESS THAN (0)
(Bug#49754: Partitioning by RANGE with TO_DAYS always includes first partition when pruning)


Ok, the correct syntax is as follow:

CREATE TABLE tblattendancesetup ( 
fldattendanceid int(11) NOT NULL AUTO_INCREMENT, 
flddept varchar(100) DEFAULT NULL, 
fldemployee varchar(100) DEFAULT NULL, 
fldintime varchar(20) DEFAULT NULL, 
fldouttime varchar(20) DEFAULT NULL, 
fldlateafter varchar(20) DEFAULT NULL, 
fldearlybefore varchar(20) DEFAULT NULL, 
fldweekoff varchar(20) DEFAULT NULL, 
fldshiftname varchar(20) DEFAULT NULL, 
fldassigndate DATE DEFAULT NULL, 
fldfromdate VARCHAR(20) DEFAULT NULL, 
fldtodate varchar(20) DEFAULT NULL, 
fldrefid varchar(20) DEFAULT NULL, 
primary KEY fldattendanceid (fldattendanceid,fldassigndate) , 
KEY in_attendancesetup (fldemployee,fldintime,fldouttime,fldlateafter,fldearlybefore,fldfromdate,fldtodate,fldattendanceid), 
KEY i_emp_tmp (fldemployee), 
KEY i_emp_attendance (fldemployee) 
) 
PARTITION BY RANGE (to_days(fldassigndate)) 
(PARTITION invalid_date  VALUES LESS THAN (0), 
PARTITION p_Apr VALUES LESS THAN (TO_DAYS('2012-05-01')), 
PARTITION p_May VALUES LESS THAN (TO_DAYS('2012-06-01')), 
PARTITION p_Nov VALUES LESS THAN MAXVALUE );


>KEY in_attendancesetup (fldemployee,fldintime,fldouttime,fldlateafter,fldearlybefore,fldfromdate,fldtod

Do you really require such a long secondary index? Provide 'SELECTs' that you would be using against this table.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: particular field type not allowed to this type of partitioning
2651
August 24, 2012 01:59AM


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.