Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement