-- Test run on:
-- Oracle Database 11g Express Edition Release 11.2.0.2.0 (2012-02-21)

SELECT * FROM v$version;

DROP VIEW rowGenerator;
DROP VIEW lAN_Anchor;
DROP VIEW lANAT2_AnchorAttribute2;
DROP TABLE ANAT2_AnchorAttribute2;
DROP TABLE ANAT1_AnchorAttribute1;
DROP TABLE AN_Anchor;

CREATE TABLE AN_Anchor (
  AN_ID int NOT NULL,
  CONSTRAINT pk_AN_Anchor PRIMARY KEY(AN_ID)
) ORGANIZATION INDEX;

CREATE TABLE ANAT1_AnchorAttribute1 (
  AN_ID int NOT NULL,
  ANAT1_AnchorAttribute1Value CHAR(10) NOT NULL,
  CONSTRAINT pk_ANAT1_AnchorAttribute1
    PRIMARY KEY(AN_ID),
  CONSTRAINT fk_ANAT1_AnchorAttribute1
    FOREIGN KEY(AN_ID) REFERENCES AN_Anchor(AN_ID)
) ORGANIZATION INDEX;

CREATE TABLE ANAT2_AnchorAttribute2 (
  AN_ID int NOT NULL,
  ANAT2_AnchorAttribute2Value CHAR(10) NOT NULL,
  ANAT2_FromDate DATE NOT NULL,
  CONSTRAINT pk_ANAT2_AnchorAttribute2
    PRIMARY KEY(AN_ID, ANAT2_FromDate),
  CONSTRAINT fk_ANAT2_AnchorAttribute2
    FOREIGN KEY(AN_ID) REFERENCES AN_Anchor(AN_ID)
) ORGANIZATION INDEX;

CREATE VIEW rowGenerator
AS
WITH rowGen(n) AS (
  SELECT
    1 AS n
  FROM
    dual
  UNION ALL
  SELECT
    1 + n AS n
  FROM
    rowGen
  WHERE
    n < 100000
)
SELECT
  *
FROM
  rowGen;

INSERT INTO AN_Anchor
SELECT
  n
FROM
  rowGenerator;

INSERT INTO ANAT1_AnchorAttribute1
SELECT
  n,
  CASE MOD(n, 3)
    WHEN 0 THEN 'Red'
    WHEN 1 THEN 'Green'
    WHEN 2 THEN 'Blue'
  END
FROM
  rowGenerator;

INSERT INTO ANAT2_AnchorAttribute2
SELECT
  n,
  'Car',
  TO_DATE('20120101','yyyymmdd')
FROM
  rowGenerator;

INSERT INTO ANAT2_AnchorAttribute2
SELECT
  n,
  'Bus',
  TO_DATE('20120102','yyyymmdd')
FROM
  rowGenerator;

INSERT INTO ANAT2_AnchorAttribute2
SELECT
  n,
  'Train',
  TO_DATE('20120103','yyyymmdd')
FROM
  rowGenerator;

CREATE VIEW lANAT2_AnchorAttribute2 (
  AN_ID,
  ANAT2_AnchorAttribute2Value,
  ANAT2_FromDate,
  CONSTRAINT pk_lANAT2_AnchorAttribute2
    PRIMARY KEY(AN_ID) DISABLE NOVALIDATE,
  CONSTRAINT fk_lANAT2_AnchorAttribute2
    FOREIGN KEY(AN_ID) REFERENCES AN_Anchor(AN_ID) DISABLE NOVALIDATE
)
AS
SELECT
  AN_ID,
  MAX(ANAT2_AnchorAttribute2Value)
    KEEP (DENSE_RANK LAST ORDER BY ANAT2_FromDate),
  MAX(ANAT2_FromDate)
FROM
  ANAT2_AnchorAttribute2
GROUP BY
  AN_ID;

ALTER VIEW lANAT2_AnchorAttribute2
MODIFY CONSTRAINT pk_lANAT2_AnchorAttribute2 RELY;
ALTER VIEW lANAT2_AnchorAttribute2
MODIFY CONSTRAINT fk_lANAT2_AnchorAttribute2 RELY;

CREATE VIEW lAN_Anchor
AS
SELECT
  AN.AN_ID,
  ANAT1.ANAT1_AnchorAttribute1Value,
  ANAT2.ANAT2_AnchorAttribute2Value,
  ANAT2.ANAT2_FromDate
FROM
  AN_Anchor AN,
  ANAT1_AnchorAttribute1 ANAT1,
  lANAT2_AnchorAttribute2 ANAT2
WHERE
  ANAT1.AN_ID (+)= AN.AN_ID
AND
  ANAT2.AN_ID (+)= AN.AN_ID;

ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

-- still no table elimination on the second (historized) attribute
SET AUTOTRACE ON;
SELECT COUNT(*) FROM lAN_Anchor;

SET AUTOTRACE ON;
SELECT COUNT(ANAT1_AnchorAttribute1Value) FROM lAN_Anchor;

SET AUTOTRACE ON;
SELECT COUNT(ANAT2_AnchorAttribute2Value) FROM lAN_Anchor;

SET AUTOTRACE OFF;