Friday, March 11, 2011

Creat Date Dimension in Oracle DB

Just to document nice Query for creating Date Dimension table in one command. You should add bitmap index to most columns for finding high performance on this dimension.

CREATE TABLE Date_Dim AS
SELECT
n AS Date_ID,
TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day') AS Full_Date,
To_Number(TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DD')) AS "DAY",
To_Number(TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'D')) AS Week_Day_num,
TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'DY') AS Week_Day_Short,
TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Day') AS Week_Day_Long,
To_Number(TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'W')) AS Week_of_Month,
To_Number(TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'IW')) AS Week_of_Year,
To_Number(TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MM')) AS Month_Num,
TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon') AS Month_Short,
TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Month') AS Month_Long,
To_Number(TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Q')) AS Quarter ,
To_Number((TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YYYY'))) AS "YEAR",
TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'YY') AS YY,
TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'Mon YYYY') AS Year_Month,
TO_CHAR(TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(n,'day'),'MonthYYYY') AS Year_Month_Long
FROM (
select level n from dual connect by level <= 2191
);

No comments:

Post a Comment