>> > (...) >> > PARTITION BY RANGE (emp_id) >> > ( >> > emp_500 (START 1 END 500), >> > emp_1500 (START 500 END 1500), >> > emp_4000 (START 1520 END 4000) >> > ); >> >> What if I need more columns to set the partitions? > > > You can do so by using command like this, > > CREATE TABLE emp ( > emp_id int not null primary key, > designation text not null, > location varchar(50) not null, > jdate date not null, > ctc float not null > > ) > PARTITION BY RANGE (jdate, emp_id) > ( > emp_500 (START '01-01-1980',1 END '01-01-1990',500), > emp_1500(START '01-01-1990',500 END '01-01-2000',1500+10), > emp_4000 (START '1 Jan 2000',1500+20 END 'Jan 1, 2010', 4000-50) > ); > > You can have multiple columns as partition key attributes and values for > these attributes should appear in the order specified.
-1, this is really ugly
Yeah, but what is the syntax for multiple column ranges in Orcl - is it similarly ugly?
As to Kedar's original question about gap-based ranges or Orcl style no-gap ranges, I don't think while partitioning such fine-grained gap-based ranges is such a stringent requirement.
Also specification of the maxvalue range in Orcl's case nicely maps to the overflow partition that is being mentioned upthread.
So whichever way we go maybe we can also consider accomodating the maxval for different datatypes if possible within the syntax itself.