Teradata Table Elimination

-- Test run on  Teradata 12 (2009-05-02)

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)
);

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)
);

CREATE  TABLE ANAT2_AnchorAttribute2 (
    AN_ID INT NOT NULL,
    ANAT2_AnchorAttribute2Value CHAR(10) NOT NULL,
    ANAT2_FromDate DATE format 'YYYY-MM-DD' 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)
);

DROP VIEW lAN_Anchor;

CREATE  VIEW lAN_Anchor
AS
SELECT
    AN.AN_ID,
    ANAT1.ANAT1_AnchorAttribute1Value,
    ANAT2.ANAT2_AnchorAttribute2Value,
    ANAT2.ANAT2_FromDate
FROM
    AN_Anchor AN
LEFT JOIN
    ANAT1_AnchorAttribute1 ANAT1
    ON
    ANAT1.AN_ID = AN.AN_ID
LEFT JOIN
    ANAT2_AnchorAttribute2 ANAT2
    ON
    ANAT2.AN_ID = AN.AN_ID
    AND
    ANAT2.ANAT2_FromDate = (
        SELECT
            MAX(sub.ANAT2_FromDate)
        FROM
            ANAT2_AnchorAttribute2 sub
        WHERE
            sub.AN_ID = AN.AN_ID
    );

INSERT  INTO AN_Anchor (
    AN_ID
)
VALUES
(1);

INSERT  INTO AN_Anchor (
    AN_ID
)
VALUES
(2);

INSERT  INTO AN_Anchor (
    AN_ID
)
VALUES
(3);

INSERT  INTO ANAT1_AnchorAttribute1 (
    AN_ID,
    ANAT1_AnchorAttribute1Value
)
VALUES
(1, 'Green');

INSERT  INTO ANAT1_AnchorAttribute1 (
    AN_ID,
    ANAT1_AnchorAttribute1Value
)
VALUES
(2, 'Blue');

INSERT  INTO ANAT1_AnchorAttribute1 (
    AN_ID,
    ANAT1_AnchorAttribute1Value
)
VALUES
(3, 'Red');

INSERT  INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(1, 'Car', '2008-01-01');

INSERT  INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(1, 'Bus', '2008-11-12');

INSERT  INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(2, 'Train', '2008-01-01');

INSERT  INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(2, 'Car', '2008-10-12');

INSERT  INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
VALUES
(3, 'Train', '2007-01-01');

SELECT  *
FROM    lAN_Anchor;

EXPLAIN SELECT  *
FROM    lAN_Anchor;

SELECT  ANAT1_AnchorAttribute1Value
FROM    lAN_Anchor;

EXPLAIN SELECT  ANAT1_AnchorAttribute1Value
FROM    lAN_Anchor;

SELECT  ANAT2_AnchorAttribute2Value
FROM    lAN_Anchor;

EXPLAIN SELECT  ANAT2_AnchorAttribute2Value
FROM    lAN_Anchor;