Partitioned Table.... 11g Features

Interval-Numeric Range Partitioned Table

Also possible are Interval-Hash and Interval-List………  11g Features

create table interval_part (
person_id   number(5) not null,
first_name  varchar2(30),
last_name   varchar2(30))
partition by range (person_id)
interval (100) store in (users) (
partition p1 values less than (101))
tablespace tbs1;

desc interval_part

SELECT table_name, tablespace_name, partitioned FROM user_tables;
col high_value format a20
SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;
INSERT INTO interval_part (person_id, first_name, last_name) VALUES (100, 'Dan', 'Morgan');
SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;
INSERT INTO interval_part (person_id, first_name, last_name) VALUES (101, 'Heli', 'Helskyaho');

SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;

INSERT INTO interval_part (person_id, first_name, last_name) VALUES (567, 'Tara', 'Havemeyer');

SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;

Interval-Date Range Partitioned Table ………  11g Features

CREATE TABLE interval_date (
person_id NUMBER(5) NOT NULL,
last_name VARCHAR2(30),
dob DATE)
PARTITION BY RANGE (dob)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
STORE IN (users, example, tbs1) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2008-03-15','YYYY-MM-DD')));

INSERT INTO interval_date (person_id, last_name, dob) VALUES (1, 'Morgan', SYSDATE-365);
INSERT INTO interval_date (person_id, last_name, dob)VALUES (2, 'Lofstrom', SYSDATE-365);
INSERT INTO interval_date (person_id, last_name, dob) VALUES (3, 'Haveyer', SYSDATE-200);
INSERT INTO interval_date (person_id, last_name, dob)VALUES (4, 'Small', SYSDATE-60);
INSERT INTO interval_date (person_id, last_name, dob) VALUES (5, 'Ellison', SYSDATE+60);

col partition_name format a14
col tablespace_name format a15
col high_value format a85
SELECT partition_name, tablespace_name, high_value FROM user_tab_partitions;

conn / as sysdba

SELECT * FROM insert_tsn_list$;

Interval-Interval Range Partitioned Table with new partitions created every six months ………  11g Features

CREATE TABLE interval_interval (
prod_id NUMBER, time_id DATE, amount_sold NUMBER) PARTITION BY RANGE (time_id)
INTERVAL (NUMTOYMINTERVAL(6,'MONTH'))
STORE IN (tbs1) ( PARTITION p2000 VALUES LESS THAN (TO_DATE('01-JAN-1999', 'DD-MON-YYYY'))) ENABLE ROW MOVEMENT;

INSERT INTO interval_interval SELECT promo_id, time_id, amount_sold FROM sh.sales;
SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name = 'INTERVAL_INTERVAL';
INSERT INTO interval_interval SELECT promo_id, time_id, amount_sold FROM sh.sales;

conn / as sysdba

SELECT * FROM insert_tsn_list$;

Interval-Interval Range Partitioned Table with new partitions created every hour using OLTP compression ----……… 11g Features

CREATE TABLE hourly_interval (
datetime  DATE,
some_data NUMBER)
PARTITION BY RANGE (datetime)
INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
STORE IN (part1, part2, part3) (
PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2010 00:00:00', 'DD-MON-YYYY HH24:MI:SS'))) COMPRESS FOR OLTP;

INSERT INTO hourly_interval (datetime, some_data)VALUES (SYSDATE, 1);
INSERT INTO hourly_interval (datetime, some_data) VALUES (SYSDATE+(1/24), 1);
INSERT INTO hourly_interval (datetime, some_data) VALUES (SYSDATE+(1/24), 1);
INSERT INTO hourly_interval (datetime, some_data) VALUES (SYSDATE+(3/24), 1);

exec dbms_stats.gather_table_stats(USER,'HOURLY_INTERVAL', CASCADE=>TRUE);

SELECT table_name, partition_name, high_value, num_rows, compression, compress_for FROM user_tab_partitions;