--レンジ
CREATE TABLE range
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION p1 VALUES LESS THAN (TO_DATE('20170101','yyyymmdd'))
, PARTITION p2 VALUES LESS THAN (TO_DATE('20180101','yyyymmdd'))
, PARTITION p3 VALUES LESS THAN (TO_DATE('20190101','yyyymmdd'))
, PARTITION pmax VALUES LESS THAN (maxvalue)
);
--リスト
CREATE TABLE list
(deptno number,
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION p1 VALUES ('AA','AB'),
PARTITION p2 VALUES ('AZ','AC'),
PARTITION p3 VALUES ('NY', 'VM'),
PARTITION p4 VALUES ('FL', 'GA'),
PARTITION p5 VALUES ('SD', 'WI'),
PARTITION pmax VALUES (default));
--ハッシュ
CREATE TABLE hash
(id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (id)
PARTITIONS 4
STORE IN (system, sysaux, tbs1, tbs2);
--レンジレンジ
CREATE TABLE range_range
( order_id NUMBER NOT NULL
, order_date DATE NOT NULL
, delivery_date DATE NOT NULL
, customer_id NUMBER NOT NULL
, sales_amount NUMBER NOT NULL
)
PARTITION BY RANGE (order_date)
SUBPARTITION BY RANGE (delivery_date)
( PARTITION p_2006_jul VALUES LESS THAN (TO_DATE('01-01-2006','dd-MM-yyyy'))
( SUBPARTITION p06_jul_e VALUES LESS THAN (TO_DATE('15-02-2006','dd-MM-yyyy'))
, SUBPARTITION p06_jul_a VALUES LESS THAN (TO_DATE('01-03-2006','dd-MM-yyyy'))
, SUBPARTITION p06_jul_l VALUES LESS THAN (MAXVALUE)
)
);
--レンジリスト
CREATE TABLE range_list
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
TABLESPACE users
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-01-1999','DD-MM-YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
)
);
--レンジハッシュ
CREATE TABLE range_hash
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
SUBPARTITIONS 8 STORE IN (users,sysaux)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-01-2006','dd-MM-yyyy'))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-02-2006','dd-MM-yyyy'))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-03-2006','dd-MM-yyyy'))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-04-2007','dd-MM-yyyy'))
);
--リストレンジ
CREATE TABLE list_range
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY RANGE (balance)
( PARTITION p_northwest VALUES ('OR', 'WA')
( SUBPARTITION p_nw_low VALUES LESS THAN (1000)
, SUBPARTITION p_nw_average VALUES LESS THAN (10000)
, SUBPARTITION p_nw_high VALUES LESS THAN (100000)
, SUBPARTITION p_nw_extraordinary VALUES LESS THAN (MAXVALUE)
)
);
--リストリスト
CREATE TABLE list_list
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY LIST (status)
( PARTITION p_northwest VALUES ('OR', 'WA')
( SUBPARTITION p_nw_bad VALUES ('B')
, SUBPARTITION p_nw_average VALUES ('A')
, SUBPARTITION p_nw_good VALUES ('G')
)
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
( SUBPARTITION p_sw_bad VALUES ('B')
, SUBPARTITION p_sw_average VALUES ('A')
, SUBPARTITION p_sw_good VALUES ('G')
)
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
( SUBPARTITION p_ne_bad VALUES ('B')
, SUBPARTITION p_ne_average VALUES ('A')
, SUBPARTITION p_ne_good VALUES ('G')
)
, PARTITION p_southeast VALUES ('FL', 'GA')
( SUBPARTITION p_se_bad VALUES ('B')
, SUBPARTITION p_se_average VALUES ('A')
, SUBPARTITION p_se_good VALUES ('G')
)
, PARTITION p_northcentral VALUES ('SD', 'WI')
( SUBPARTITION p_nc_bad VALUES ('B')
, SUBPARTITION p_nc_average VALUES ('A')
, SUBPARTITION p_nc_good VALUES ('G')
)
, PARTITION p_southcentral VALUES ('OK', 'TX')
( SUBPARTITION p_sc_bad VALUES ('B')
, SUBPARTITION p_sc_average VALUES ('A')
, SUBPARTITION p_sc_good VALUES ('G')
)
);
--リストハッシュ
CREATE TABLE list_hash
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 8
( PARTITION p_northwest VALUES ('OR', 'WA')
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
, PARTITION p_southeast VALUES ('FL', 'GA')
, PARTITION p_northcentral VALUES ('SD', 'WI')
, PARTITION p_southcentral VALUES ('OK', 'TX')
);
--ハッシュレンジ
CREATE TABLE hash_range
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, balance NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY HASH (region)
SUBPARTITION BY RANGE (balance)
( PARTITION p1
( SUBPARTITION p1_nw_low VALUES LESS THAN (1000)
, SUBPARTITION p1_nw_average VALUES LESS THAN (10000)
, SUBPARTITION p1_nw_high VALUES LESS THAN (100000)
, SUBPARTITION p1_nw_extraordinary VALUES LESS THAN (MAXVALUE)
),
PARTITION p2
( SUBPARTITION p2_nw_low VALUES LESS THAN (1000)
, SUBPARTITION p2_nw_average VALUES LESS THAN (10000)
, SUBPARTITION p2_nw_high VALUES LESS THAN (100000)
, SUBPARTITION p2_nw_extraordinary VALUES LESS THAN (MAXVALUE)
)
);
--ハッシュリスト
CREATE TABLE hash_list
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
TABLESPACE users
PARTITION BY HASH (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION p1
(SUBPARTITION q11_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q11_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q11_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q11_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q11_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q11_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION p2
(SUBPARTITION q21_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q21_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q21_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q21_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q21_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q21_1999_southcentral VALUES ('OK', 'TX')
)
);
--ハッシュハッシュ
CREATE TABLE hash_hash
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
TABLESPACE users
PARTITION BY HASH (txn_date)
SUBPARTITION BY HASH (state)
(PARTITION p1
(SUBPARTITION q11,
SUBPARTITION q12
),
PARTITION p2
(SUBPARTITION q21,
SUBPARTITION q22
)
);
--レンジ
CREATE TABLE range1 (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN (3),
PARTITION p1 VALUES LESS THAN (7),
PARTITION p2 VALUES LESS THAN (9),
PARTITION p3 VALUES LESS THAN (11)
);
INSERT INTO range1 VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'CD player', '1993-11-05'),
(3, 'TV set', '1996-03-10'),
(4, 'bookcase', '1982-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'popcorn maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '1984-09-16'),
(10, 'lava lamp', '1998-12-25');
EXPLAIN PARTITIONS SELECT * FROM range1\G
EXPLAIN PARTITIONS SELECT * FROM range1 WHERE id < 5\G
alter table range1 drop partition p1,p3;
alter table range1 truncate partition p1,p3;
ALTER TABLE range1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (20));
ALTER TABLE range1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (30));
ALTER TABLE range1 ADD PARTITION (PARTITION pmax VALUES LESS THAN (MAXVALUE));
--リスト
CREATE TABLE list1 (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
region_code TINYINT UNSIGNED NOT NULL,
dob DATE NOT NULL
)
PARTITION BY LIST(region_code) (
PARTITION r0 VALUES IN (1, 3),
PARTITION r1 VALUES IN (2, 5, 8),
PARTITION r2 VALUES IN (4, 9),
PARTITION r3 VALUES IN (6, 7, 10)
);
--ハッシュ
CREATE TABLE hash1 (
id INT NOT NULL,
fname VARCHAR(30),
lname VARCHAR(30),
hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31',
job_code INT,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
--レンジ - ハッシュ
drop table rangehash;
create table rangehash (
col1 int
,col2 int)
partition by range(col1)
subpartition by hash(col2)
subpartitions 2
(
partition p0 values less than (1),
partition p1 values less than (2),
partition p2 values less than (3),
partition p3 values less than maxvalue
);
--リスト - ハッシュ
drop table listhash;
create table listhash (
col1 int
,col2 int)
partition by list(col1)
subpartition by hash(col2)
subpartitions 2
(
partition p0 values in (1),
partition p1 values in (2),
partition p2 values in (3),
partition p3 values in (4)
);
(9.4)(9.6)
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m04 (
CHECK ( logdate >= DATE '2006-04-01' AND logdate < DATE '2006-05-01' )
) INHERITS (measurement);
CREATE TABLE measurement_max (
CHECK ( logdate >= DATE '2008-02-01' )
) INHERITS (measurement);
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
CREATE INDEX measurement_y2006m04_logdate ON measurement_y2006m04 (logdate);
CREATE INDEX measurement_max_logdate ON measurement_max (logdate);
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.logdate >= DATE '2006-02-01' AND
NEW.logdate < DATE '2006-03-01' ) THEN
INSERT INTO measurement_y2006m02 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
NEW.logdate < DATE '2006-04-01' ) THEN
INSERT INTO measurement_y2006m03 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2006-04-01' AND
NEW.logdate < DATE '2006-05-01' ) THEN
INSERT INTO measurement_y2006m04 VALUES (NEW.*);
ELSE
INSERT INTO measurement_max VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
select * from measurement;
select * from measurement_y2006m02;
select * from measurement_y2006m03;
select * from measurement_y2006m04;
select * from measurement_max;
insert into measurement(city_id,logdate) values(1, '2006-04-02');
insert into measurement(city_id,logdate) values(1, '2016-04-02');
(10)
--レンジパーティション
create table range(
col1 int,
col2 int
)
partition by range (col1);
create table range1 partition of range FOR VALUES FROM (0) TO (2);
create table range2 partition of range FOR VALUES FROM (2) TO (5);
create table range3 partition of range FOR VALUES FROM (5) TO (8);
--リストパーティション
create table list(
col1 int,
col2 int
)
partition by list (col1);
create table list1 partition of list for values in (0,1,2);
create table list2 partition of list for values in (3,4,5);
create table list3 partition of list for values in (6,7,8);
--サブパーティション(レンジ-レンジ)
create table rangerange(
col1 int,
col2 int,
col3 int
)
partition by range (col1);
create table rangerange1 partition of rangerange FOR VALUES FROM (0) TO (2) PARTITION BY RANGE (col2);
create table rangerange2 partition of rangerange FOR VALUES FROM (2) TO (5) PARTITION BY RANGE (col2);
create table rangerange3 partition of rangerange FOR VALUES FROM (5) TO (maxvalue) PARTITION BY RANGE (col2);
create table rangerange11 partition of rangerange1 FOR VALUES FROM (0) TO (200);
create table rangerange12 partition of rangerange1 FOR VALUES FROM (200) TO (maxvalue);
create table rangerange21 partition of rangerange2 FOR VALUES FROM (0) TO (200);
create table rangerange22 partition of rangerange2 FOR VALUES FROM (200) TO (maxvalue);
create table rangerange31 partition of rangerange3 FOR VALUES FROM (0) TO (200);
create table rangerange32 partition of rangerange3 FOR VALUES FROM (200) TO (maxvalue);
--サブパーティション(レンジ-リスト)
create table rangelist(
col1 int,
col2 int,
col3 int
)
partition by range (col1);
create table rangelist1 partition of rangelist FOR VALUES FROM (0) TO (2) PARTITION BY list (col2);
create table rangelist2 partition of rangelist FOR VALUES FROM (2) TO (5) PARTITION BY list (col2);
create table rangelist3 partition of rangelist FOR VALUES FROM (5) TO (maxvalue) PARTITION BY list (col2);
create table rangelist11 partition of rangelist1 FOR VALUES in (0,1);
create table rangelist12 partition of rangelist1 FOR VALUES in (2,3);
create table rangelist21 partition of rangelist2 FOR VALUES in (0,1);
create table rangelist22 partition of rangelist2 FOR VALUES in (2,3);
create table rangelist31 partition of rangelist3 FOR VALUES in (0,1);
create table rangelist32 partition of rangelist3 FOR VALUES in (2,3);
--サブパーティション(リスト-レンジ)
create table listrange(
col1 int,
col2 int,
col3 int
)
partition by list (col1);
create table listrange1 partition of listrange for values in (0,1,2) PARTITION BY range(col2);
create table listrange2 partition of listrange for values in (3,4,5) PARTITION BY range(col2);
create table listrange3 partition of listrange for values in (6,7,8) PARTITION BY range(col2);
create table listrange11 partition of listrange1 FOR VALUES FROM (0) TO (2);
create table listrange12 partition of listrange1 FOR VALUES FROM (2) TO (maxvalue);
create table listrange21 partition of listrange2 FOR VALUES FROM (0) TO (2);
create table listrange22 partition of listrange2 FOR VALUES FROM (2) TO (maxvalue);
create table listrange31 partition of listrange3 FOR VALUES FROM (0) TO (2);
create table listrange32 partition of listrange3 FOR VALUES FROM (2) TO (maxvalue);
--サブパーティション(リスト-リスト)
create table listlist(
col1 int,
col2 int,
col3 int
)
partition by list (col1);
create table listlist1 partition of listlist for values in (0,1,2) PARTITION BY list(col2);
create table listlist2 partition of listlist for values in (3,4,5) PARTITION BY list(col2);
create table listlist3 partition of listlist for values in (6,7,8) PARTITION BY list(col2);
create table listlist11 partition of listlist1 FOR VALUES in (0,1,2);
create table listlist12 partition of listlist1 FOR VALUES in (3,4,5);
create table listlist21 partition of listlist2 FOR VALUES in (0,1,2);
create table listlist22 partition of listlist2 FOR VALUES in (3,4,5);
create table listlist31 partition of listlist3 FOR VALUES in (0,1,2);
create table listlist32 partition of listlist3 FOR VALUES in (3,4,5);
--ハッシュパーティショニング(11)
create table hash(
col1 int,
col2 int,
col3 int
)
partition by hash(col1);
create table hash1 partition of hash for values with (modulus 2,remainder 0);
create table hash2 partition of hash for values with (modulus 2,remainder 1);
--サブパーティション(レンジ-ハッシュ)
create table rangehash(
col1 int,
col2 int,
col3 int
)
partition by range (col1);
create table rangehash1 partition of rangehash FOR VALUES FROM (0) TO (2) PARTITION BY hash (col2);
create table rangehash2 partition of rangehash FOR VALUES FROM (2) TO (5) PARTITION BY hash (col2);
create table rangehash3 partition of rangehash FOR VALUES FROM (5) TO (maxvalue) PARTITION BY hash (col2);
create table rangehash11 partition of rangehash1 FOR VALUES with (modulus 2,remainder 0);
create table rangehash12 partition of rangehash1 FOR VALUES with (modulus 2,remainder 1);
create table rangehash21 partition of rangehash2 FOR VALUES with (modulus 2,remainder 0);
create table rangehash22 partition of rangehash2 FOR VALUES with (modulus 2,remainder 1);
create table rangehash31 partition of rangehash3 FOR VALUES with (modulus 2,remainder 0);
create table rangehash32 partition of rangehash3 FOR VALUES with (modulus 2,remainder 1);
--サブパーティション(リスト-ハッシュ)
create table listhash(
col1 int,
col2 int,
col3 int
)
partition by list (col1);
create table listhash1 partition of listhash for values in (0,1,2) PARTITION BY hash(col2);
create table listhash2 partition of listhash for values in (3,4,5) PARTITION BY hash(col2);
create table listhash3 partition of listhash for values in (6,7,8) PARTITION BY hash(col2);
create table listhash11 partition of listhash1 FOR VALUES with (modulus 2,remainder 0);
create table listhash12 partition of listhash1 FOR VALUES with (modulus 2,remainder 1);
create table listhash21 partition of listhash2 FOR VALUES with (modulus 2,remainder 0);
create table listhash22 partition of listhash2 FOR VALUES with (modulus 2,remainder 1);
create table listhash31 partition of listhash3 FOR VALUES with (modulus 2,remainder 0);
create table listhash32 partition of listhash3 FOR VALUES with (modulus 2,remainder 1);
--サブパーティション(ハッシュ-レンジ)
create table hashrange(
col1 int,
col2 int,
col3 int
)
partition by hash (col1);
create table hashrange1 partition of hashrange FOR VALUES with (modulus 2,remainder 0) PARTITION BY range (col2);
create table hashrange2 partition of hashrange FOR VALUES with (modulus 2,remainder 1) PARTITION BY range (col2);
create table hashrange11 partition of hashrange1 FOR VALUES FROM (0) TO (2) ;
create table hashrange12 partition of hashrange1 FOR VALUES FROM (2) TO (maxvalue) ;
create table hashrange21 partition of hashrange2 FOR VALUES FROM (0) TO (2) ;
create table hashrange22 partition of hashrange2 FOR VALUES FROM (2) TO (maxvalue) ;
--サブパーティション(ハッシュ-リスト)
create table hashlist(
col1 int,
col2 int,
col3 int
)
partition by hash (col1);
create table hashlist1 partition of hashlist FOR VALUES with (modulus 2,remainder 0) PARTITION BY list (col2);
create table hashlist2 partition of hashlist FOR VALUES with (modulus 2,remainder 1) PARTITION BY list (col2);
create table hashlist11 partition of hashlist1 FOR VALUES in (0,1) ;
create table hashlist12 partition of hashlist1 default;
create table hashlist21 partition of hashlist2 FOR VALUES in (0,1) ;
create table hashlist22 partition of hashlist2 default;
--サブパーティション(ハッシュ-ハッシュ)
create table hashhash(
col1 int,
col2 int,
col3 int
)
partition by hash (col1);
create table hashhash1 partition of hashhash FOR VALUES with (modulus 2,remainder 0) PARTITION BY hash (col2);
create table hashhash2 partition of hashhash FOR VALUES with (modulus 2,remainder 1) PARTITION BY hash (col2);
create table hashhash11 partition of hashhash1 FOR VALUES with (modulus 2,remainder 0);
create table hashhash12 partition of hashhash1 FOR VALUES with (modulus 2,remainder 1);
create table hashhash21 partition of hashhash2 FOR VALUES with (modulus 2,remainder 0);
create table hashhash22 partition of hashhash2 FOR VALUES with (modulus 2,remainder 1);
USE master
go
CREATE DATABASE pTestDB
ON PRIMARY
( NAME = N'pTestDB'
, FILENAME = N'E:\test\pTestDB.mdf', SIZE = 5MB),
FILEGROUP fg1
( NAME = N'fg1'
, FILENAME = N'E:\test\fg1.ndf', SIZE = 5MB),
FILEGROUP fg2
( NAME = N'fg2'
, FILENAME = N'E:\test\fg2.ndf', SIZE = 5MB),
FILEGROUP fg3
( NAME = N'fg3'
, FILENAME = N'E:\test\fg3.ndf', SIZE = 5MB),
FILEGROUP fg4
( NAME = N'fg4'
, FILENAME = N'E:\test\fg4.ndf', SIZE = 5MB)
LOG ON
( NAME = N'pTestDB_log'
, FILENAME = N'E:\test\pTestDB_log.LDF', SIZE = 5MB)
go
USE pTestDB
go
CREATE PARTITION FUNCTION pFunc1 (datetime)
AS RANGE RIGHT FOR
VALUES ('2010/01/01', '2011/01/01', '2012/01/01')
go
USE pTestDB
go
CREATE PARTITION SCHEME pScheme1
AS PARTITION pFunc1
TO (fg1, fg2, fg3, fg4)
go
USE pTestDB
CREATE TABLE t1
( id int IDENTITY(1,1) NOT NULL
,col1 datetime
) ON pScheme1(col1)
go
INSERT INTO t1
VALUES ('2009/10/15')
,('2010/01/01')
,('2010/05/22')
,('2010/12/31')
,('2011/01/01')
,('2011/12/31')
,('2012/01/01')
go
SELECT *, $PARTITION.pFunc1(col1) As [パーティション番号]
FROM t1
go
