CREATE TABLE sales
(
  customer_id NUMBER,
  sell_id     NUMBER,
  product_id  NUMBER,
  sold_date   DATE,
  price       NUMBER,
  quantity    NUMBER,
  state       VARCHAR(5)
);

INSERT INTO sales 
  VALUES(1, 10, 15, TO_DATE('20110110', 'YYYYMMDD'), 100, 5, 'CA');

INSERT INTO sales 
  VALUES(2, 15, 10, TO_DATE('20110116', 'YYYYMMDD'), 25, 2, 'OR');

INSERT INTO sales 
  VALUES(3, 20, 5, TO_DATE('20110210', 'YYYYMMDD'), 10, 3, 'WA');

CREATE TABLE sales_list_partition
(
  customer_id NUMBER,
  sell_id     NUMBER,
  product_id  NUMBER,
  sold_date   DATE,
  price       NUMBER,
  quantity    NUMBER,
  state       VARCHAR(5)
)
PARTITION BY LIST (state)
(
  PARTITION west VALUES
  (
    'CA',
    'OR',
    'WA'
  )
);

INSERT INTO sales_list_partition
SELECT * FROM sales;

INSERT INTO sales_list_partition
  VALUES(3, 20, 5, TO_DATE('20110210', 'YYYYMMDD'), 10, 3, 'NV');

ALTER TABLE sales_list_partition ADD PARTITION default_partition VALUES
(
  DEFAULT
);

INSERT INTO sales_list_partition
  VALUES(3, 20, 5, TO_DATE('20110210', 'YYYYMMDD'), 10, 3, 'NV');

CREATE TABLE sales_range_partition
(
  customer_id NUMBER,
  sell_id     NUMBER,
  product_id  NUMBER,
  sold_date   DATE,
  price       NUMBER,
  quantity    NUMBER,
  state       VARCHAR(5)
)
PARTITION BY RANGE (sold_date)
(
  PARTITION P_JANUARY VALUES LESS THAN (TO_DATE('20110131', 'YYYYMMDD')),
  PARTITION P_FEBRUARY VALUES LESS THAN (TO_DATE('20110228', 'YYYYMMDD'))
);

INSERT INTO sales_range_partition
SELECT * FROM sales;

INSERT INTO sales_range_partition
  VALUES(3, 20, 5, TO_DATE('20110301', 'YYYYMMDD'), 10, 3, 'NV');

ALTER TABLE sales_range_partition ADD PARTITION maxvalue_partition 
  VALUES LESS THAN ( MAXVALUE );

INSERT INTO sales_range_partition
  VALUES(3, 20, 5, TO_DATE('20110301', 'YYYYMMDD'), 10, 3, 'NV');

CREATE TABLE sales_hash_partition
(
  customer_id NUMBER,
  sell_id     NUMBER,
  product_id  NUMBER,
  sold_date   DATE,
  price       NUMBER,
  quantity    NUMBER,
  state       VARCHAR(5)
)
PARTITION BY HASH (customer_id) PARTITIONS 2;

INSERT INTO sales_hash_partition
SELECT * FROM sales;

DROP TABLE sales;

DROP TABLE sales_hash_partition;

DROP TABLE sales_range_partition;

DROP TABLE sales_list_partition;