-- drop tables
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

-- create table for scenario A (consistent data set)
CREATE TABLE t1 (
   vid        NUMBER(4,0)  NOT NULL PRIMARY KEY,
   oid        NUMBER(4,0)  NOT NULL,
   valid_from DATE         NOT NULL,
   valid_to   DATE         NOT NULL,
   c1         VARCHAR2(10),
   c2         VARCHAR2(10)
);
CREATE INDEX t1_oid ON t1 (OID);

-- load initial data
INSERT INTO t1 (vid, oid, valid_from, valid_to, c1, c2)
-- OID 1 (gaps, partly mergeable)
SELECT 1, 1, DATE '2010-01-01', DATE '2010-12-31', 'A', 'B1' FROM dual
UNION ALL SELECT 2, 1, DATE '2011-01-01', DATE '2011-03-31', 'A', 'B2' FROM dual
UNION ALL SELECT 3, 1, DATE '2011-06-01', DATE '2012-01-31', 'A', 'B2' FROM dual
UNION ALL SELECT 4, 1, DATE '2012-04-01', DATE '9999-12-31', 'A', 'B4' FROM dual
-- OID 2 (no gaps, mergeable)
UNION ALL SELECT 5, 2, DATE '2010-01-01', DATE '2012-07-31', 'B', 'B1' FROM dual
UNION ALL SELECT 6, 2, DATE '2012-08-01', DATE '9999-12-31', 'B', 'B2' FROM dual
-- OID 4 (no gaps, but not mergable
UNION ALL SELECT 18, 4, DATE '2010-01-01', DATE '2011-09-30', 'D', 'D1' FROM dual
UNION ALL SELECT 19, 4, DATE '2011-10-01', DATE '2012-09-30', NULL, 'D2' FROM dual
UNION ALL SELECT 20, 4, DATE '2012-10-01', DATE '9999-12-31', 'D', 'D3' FROM dual;
COMMIT;

-- create table for scenario B (inconsistent data set)
CREATE TABLE t2 AS SELECT * FROM t1;
ALTER TABLE t2 MODIFY vid PRIMARY KEY;
CREATE INDEX t2_oid ON t2 (OID);

-- load initial data
INSERT INTO t2 (vid, oid, valid_from, valid_to, c1, c2)
-- OID 3 (overlapping scenarios)
SELECT 7, 3, DATE '2010-01-01', DATE '2010-12-31', 'C', 'B1' FROM dual -- overlapping 8, 9, 10
UNION ALL SELECT 8, 3, DATE '2010-01-01', DATE '2010-03-31', 'C', 'B2' FROM dual -- included in 7
UNION ALL SELECT 9, 3, DATE '2010-06-01', DATE '2010-08-31', 'C', 'B3' FROM dual -- included in 7
UNION ALL SELECT 10, 3, DATE '2010-10-01', DATE '2010-12-31', 'C', 'B4' FROM dual -- included in 7
UNION ALL SELECT 11, 3, DATE '2011-02-01', DATE '2011-06-30', 'C', 'B5' FROM dual -- gap to 7
UNION ALL SELECT 12, 3, DATE '2011-02-01', DATE '2011-06-30', 'C', 'B6' FROM dual -- conflict with 11
UNION ALL SELECT 13, 3, DATE '2011-06-01', DATE '2011-08-31', 'C', 'B7' FROM dual -- overlapping with  11/12
UNION ALL SELECT 14, 3, DATE '2011-08-31', DATE '2011-09-30', 'C', 'B8' FROM dual -- overlapping with 13
UNION ALL SELECT 15, 3, DATE '2011-12-01', DATE '2012-05-31', 'C', 'B9' FROM dual -- gap to 14
UNION ALL SELECT 16, 3, DATE '2011-12-01', DATE '2012-05-31', 'C', 'B9' FROM dual -- same as 15
UNION ALL SELECT 17, 3, DATE '2012-06-01', DATE '2012-12-31', 'C', 'B10' FROM dual -- no gap to 16
;
COMMIT;

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