oracle中的than,range分区values less than，代表的是小于等于还是小于啊。

原帖由 MicroJoey 于 2010-6-20 10:20 发表

测试的number 类型也是是小于，仅供参考：

SQL> DROP TABLE a;

Table dropped.

SQL> CREATE TABLE a (ID NUMBER)

2  PARTITION BY RANGE(ID)

3  (

4  PARTITION P_01 VALUES LESS THAN (3),

5  PARTITION P_02 VALUES LESS THAN (10),

6  PARTITION P_03 VALUES LESS THAN (Maxvalue));

Table created.

SQL> INSERT INTO a

2  SELECT 3 FROM dual;

1 row created.

SQL> INSERT INTO a

2  SELECT 10 FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM a PARTITION (P_01);

no rows selected

SQL> SELECT * FROM a PARTITION (P_02);

ID

----------

3

SQL> SELECT * FROM a PARTITION (P_03);

ID

----------

10

SQL>

下面是表的创建语句，你可以试试：

CREATE TABLE t (

id NUMBER,

d1 DATE,

n1 NUMBER,

n2 NUMBER,

n3 NUMBER,

pad VARCHAR2(4000),

CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (n1, d1) (

PARTITION t_1_jan_2007 VALUES LESS THAN (1, to_date('2007-02-01','yyyy-mm-dd')),

PARTITION t_1_feb_2007 VALUES LESS THAN (1, to_date('2007-03-01','yyyy-mm-dd')),

PARTITION t_1_mar_2007 VALUES LESS THAN (1, to_date('2007-04-01','yyyy-mm-dd')),

PARTITION t_1_apr_2007 VALUES LESS THAN (1, to_date('2007-05-01','yyyy-mm-dd')),

PARTITION t_1_may_2007 VALUES LESS THAN (1, to_date('2007-06-01','yyyy-mm-dd')),

PARTITION t_1_jun_2007 VALUES LESS THAN (1, to_date('2007-07-01','yyyy-mm-dd')),

PARTITION t_1_jul_2007 VALUES LESS THAN (1, to_date('2007-08-01','yyyy-mm-dd')),

PARTITION t_1_aug_2007 VALUES LESS THAN (1, to_date('2007-09-01','yyyy-mm-dd')),

PARTITION t_1_sep_2007 VALUES LESS THAN (1, to_date('2007-10-01','yyyy-mm-dd')),

PARTITION t_1_oct_2007 VALUES LESS THAN (1, to_date('2007-11-01','yyyy-mm-dd')),

PARTITION t_1_nov_2007 VALUES LESS THAN (1, to_date('2007-12-01','yyyy-mm-dd')),

PARTITION t_1_dec_2007 VALUES LESS THAN (1, to_date('2008-01-01','yyyy-mm-dd')),

PARTITION t_2_jan_2007 VALUES LESS THAN (2, to_date('2007-02-01','yyyy-mm-dd')),

PARTITION t_2_feb_2007 VALUES LESS THAN (2, to_date('2007-03-01','yyyy-mm-dd')),

PARTITION t_2_mar_2007 VALUES LESS THAN (2, to_date('2007-04-01','yyyy-mm-dd')),

PARTITION t_2_apr_2007 VALUES LESS THAN (2, to_date('2007-05-01','yyyy-mm-dd')),

PARTITION t_2_may_2007 VALUES LESS THAN (2, to_date('2007-06-01','yyyy-mm-dd')),

PARTITION t_2_jun_2007 VALUES LESS THAN (2, to_date('2007-07-01','yyyy-mm-dd')),

PARTITION t_2_jul_2007 VALUES LESS THAN (2, to_date('2007-08-01','yyyy-mm-dd')),

PARTITION t_2_aug_2007 VALUES LESS THAN (2, to_date('2007-09-01','yyyy-mm-dd')),

PARTITION t_2_sep_2007 VALUES LESS THAN (2, to_date('2007-10-01','yyyy-mm-dd')),

PARTITION t_2_oct_2007 VALUES LESS THAN (2, to_date('2007-11-01','yyyy-mm-dd')),

PARTITION t_2_nov_2007 VALUES LESS THAN (2, to_date('2007-12-01','yyyy-mm-dd')),

PARTITION t_2_dec_2007 VALUES LESS THAN (2, to_date('2008-01-01','yyyy-mm-dd')),

PARTITION t_3_jan_2007 VALUES LESS THAN (3, to_date('2007-02-01','yyyy-mm-dd')),

PARTITION t_3_feb_2007 VALUES LESS THAN (3, to_date('2007-03-01','yyyy-mm-dd')),

PARTITION t_3_mar_2007 VALUES LESS THAN (3, to_date('2007-04-01','yyyy-mm-dd')),

PARTITION t_3_apr_2007 VALUES LESS THAN (3, to_date('2007-05-01','yyyy-mm-dd')),

PARTITION t_3_may_2007 VALUES LESS THAN (3, to_date('2007-06-01','yyyy-mm-dd')),

PARTITION t_3_jun_2007 VALUES LESS THAN (3, to_date('2007-07-01','yyyy-mm-dd')),

PARTITION t_3_jul_2007 VALUES LESS THAN (3, to_date('2007-08-01','yyyy-mm-dd')),

PARTITION t_3_aug_2007 VALUES LESS THAN (3, to_date('2007-09-01','yyyy-mm-dd')),

PARTITION t_3_sep_2007 VALUES LESS THAN (3, to_date('2007-10-01','yyyy-mm-dd')),

PARTITION t_3_oct_2007 VALUES LESS THAN (3, to_date('2007-11-01','yyyy-mm-dd')),

PARTITION t_3_nov_2007 VALUES LESS THAN (3, to_date('2007-12-01','yyyy-mm-dd')),

PARTITION t_3_dec_2007 VALUES LESS THAN (3, to_date('2008-01-01','yyyy-mm-dd')),

PARTITION t_4_jan_2007 VALUES LESS THAN (4, to_date('2007-02-01','yyyy-mm-dd')),

PARTITION t_4_feb_2007 VALUES LESS THAN (4, to_date('2007-03-01','yyyy-mm-dd')),

PARTITION t_4_mar_2007 VALUES LESS THAN (4, to_date('2007-04-01','yyyy-mm-dd')),

PARTITION t_4_apr_2007 VALUES LESS THAN (4, to_date('2007-05-01','yyyy-mm-dd')),

PARTITION t_4_may_2007 VALUES LESS THAN (4, to_date('2007-06-01','yyyy-mm-dd')),

PARTITION t_4_jun_2007 VALUES LESS THAN (4, to_date('2007-07-01','yyyy-mm-dd')),

PARTITION t_4_jul_2007 VALUES LESS THAN (4, to_date('2007-08-01','yyyy-mm-dd')),

PARTITION t_4_aug_2007 VALUES LESS THAN (4, to_date('2007-09-01','yyyy-mm-dd')),

PARTITION t_4_sep_2007 VALUES LESS THAN (4, to_date('2007-10-01','yyyy-mm-dd')),

PARTITION t_4_oct_2007 VALUES LESS THAN (4, to_date('2007-11-01','yyyy-mm-dd')),

PARTITION t_4_nov_2007 VALUES LESS THAN (4, to_date('2007-12-01','yyyy-mm-dd')),

PARTITION t_4_dec_2007 VALUES LESS THAN (4, to_date('2008-01-01','yyyy-mm-dd'))

-- , PARTITION t_maxvalue VALUES LESS THAN (MAXVALUE, MAXVALUE)

);

INSERT INTO t

SELECT rownum AS id,

trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4) AS d1,

1+mod(rownum,4) AS n1,

255+mod(trunc(dbms_random.normal*1000),255) AS n2,

round(4515+dbms_random.normal*1234) AS n3,

dbms_random.string('p',255) AS pad

FROM dual

CONNECT BY level <= 10000

ORDER BY dbms_random.value;