-- drop tables
DROP TABLE t1 PURGE;

-- create table t1, historized on transaction time axis
CREATE TABLE t1 (
   vid         NUMBER(4,0)  NOT NULL PRIMARY KEY,
   oid         NUMBER(4,0)  NOT NULL,
   created_at  DATE         NOT NULL,
   outdated_at DATE         NOT NULL,
   c1          VARCHAR2(10),
   c2          VARCHAR2(10)
);
CREATE INDEX t1_oid ON t1 (OID);

-- load initial data
INSERT INTO t1 (vid, oid, created_at, outdated_at, c1, c2)
-- OID 1
SELECT 1, 1, TIMESTAMP '2012-12-19 13:00:57', TIMESTAMP '2012-12-21 08:31:01', 'A', 'A1' FROM dual
UNION ALL SELECT 2, 1, TIMESTAMP '2012-12-21 08:31:01', TIMESTAMP '2012-12-23 20:58:05', 'A', 'A2' FROM dual
UNION ALL SELECT 3, 1, TIMESTAMP '2012-12-23 20:58:05', TIMESTAMP '2012-12-27 11:40:41', 'A', 'A3' FROM dual
UNION ALL SELECT 4, 1, TIMESTAMP '2012-12-27 11:40:41', TIMESTAMP '9999-12-31 23:59:59', 'A', 'A4' FROM dual
-- OID 2
UNION ALL SELECT 5, 2, TIMESTAMP '2012-12-20 13:51:55', TIMESTAMP '9999-12-31 23:59:59', 'B', 'B1' FROM dual
-- OID 4 
UNION ALL SELECT 6, 4, TIMESTAMP '2012-12-22 11:03:22', TIMESTAMP '2012-12-23 19:36:08', 'C', 'C1' FROM dual
UNION ALL SELECT 7, 4, TIMESTAMP '2012-12-28 14:25:50', TIMESTAMP '2012-12-30 17:10:39', 'C', 'C1' FROM dual
UNION ALL SELECT 8, 4, TIMESTAMP '2012-12-30 17:10:39', TIMESTAMP '2012-12-31 12:05:40', 'C', 'C2' FROM dual;
COMMIT;

-- gather stats
BEGIN
   dbms_stats.gather_table_stats(ownname          => USER,
                                 tabname          => 't1',
                                 method_opt       => 'for all columns size 1',
                                 estimate_percent => 100);
END;