Ilmu Sql

drop table if exists dim_date;
CREATE TABLE dim_date(
    date_key int NOT NULL,
    full_date date NULL,
    date_name char(11) NOT NULL,
    date_name_us char(11) NOT NULL,
    date_name_eu char(11) NOT NULL,
    day_of_week tinyint NOT NULL,
    day_name_of_week char(10) NOT NULL,
    day_of_month tinyint NOT NULL,
    day_of_year smallint NOT NULL,
    weekday_weekend char(10) NOT NULL,
    week_of_year tinyint NOT NULL,
    month_name char(10) NOT NULL,
    month_of_year tinyint NOT NULL,
    is_last_day_of_month char(1) NOT NULL,
    calendar_quarter tinyint NOT NULL,
    calendar_year smallint NOT NULL,
    calendar_year_month char(10) NOT NULL,
    calendar_year_qtr char(10) NOT NULL,
    fiscal_month_of_year tinyint NOT NULL,
    fiscal_quarter tinyint NOT NULL,
    fiscal_year int NOT NULL,
    fiscal_year_month char(10) NOT NULL,
    fiscal_year_qtr char(10) NOT NULL,
  PRIMARY KEY (`date_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

delimiter //

drop procedure if exists PopulateDateDimension//

CREATE PROCEDURE PopulateDateDimension(BeginDate DATETIME, EndDate DATETIME)
BEGIN

    # =============================================
    # Description:    http://arcanecode.com/2009/11/18/populating-a-kimball-date-dimension/
    # =============================================

    # A few notes, this code does nothing to the existing table, no deletes
    # are triggered before hand. Because the DateKey is uniquely indexed,
    # it will simply produce errors if you attempt to insert duplicates.
    # You can however adjust the Begin/End dates and rerun to safely add
    # new dates to the table every year.
    #
    # If the begin date is after the end date, no errors occur but nothing
    # happens as the while loop never executes.

    # Holds a flag so we can determine if the date is the last day of month
    DECLARE LastDayOfMon CHAR(1);

    # Number of months to add to the date to get the current Fiscal date
    DECLARE FiscalYearMonthsOffset INT;

    # These two counters are used in our loop.
    DECLARE DateCounter DATETIME;    #Current date in loop
    DECLARE FiscalCounter DATETIME;  #Fiscal Year Date in loop

    # Set this to the number of months to add to the current date to get
    # the beginning of the Fiscal year. For example, if the Fiscal year
    # begins July 1, put a 6 there.
    # Negative values are also allowed, thus if your 2010 Fiscal year
    # begins in July of 2009, put a -6.
    SET FiscalYearMonthsOffset = 6;

    # Start the counter at the begin date
    SET DateCounter = BeginDate;

    WHILE DateCounter <= EndDate DO
            # Calculate the current Fiscal date as an offset of
            # the current date in the loop

            SET FiscalCounter = DATE_ADD(DateCounter, INTERVAL FiscalYearMonthsOffset MONTH);

            # Set value for IsLastDayOfMonth
            IF MONTH(DateCounter) = MONTH(DATE_ADD(DateCounter, INTERVAL 1 DAY)) THEN
               SET LastDayOfMon = 'N';
            ELSE
               SET LastDayOfMon = 'Y';
            END IF;

            # add a record into the date dimension table for this date
            INSERT  INTO dim_date
                   (date_key
                   ,full_date
                   ,date_name
                   ,date_name_us
                   ,date_name_eu
                   ,day_of_week
                   ,day_name_of_week
                   ,day_of_month
                   ,day_of_year
                   ,weekday_weekend
                   ,week_of_year
                   ,month_name
                   ,month_of_year
                   ,is_last_day_of_month
                   ,calendar_quarter
                   ,calendar_year
                   ,calendar_year_month
                   ,calendar_year_qtr
                   ,fiscal_month_of_year
                   ,fiscal_quarter
                   ,fiscal_year
                   ,fiscal_year_month
                   ,fiscal_year_qtr)
            VALUES  (
                      ( YEAR(DateCounter) * 10000 ) + ( MONTH(DateCounter)
                                                         * 100 )
                      + DAY(DateCounter)  #DateKey
                    , DateCounter # FullDate
                    , CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)),'/',DATE_FORMAT(DateCounter,'%m'),'/',DATE_FORMAT(DateCounter,'%d')) #DateName
                    , CONCAT(DATE_FORMAT(DateCounter,'%m'),'/',DATE_FORMAT(DateCounter,'%d'),'/',CAST(YEAR(DateCounter) AS CHAR(4)))#DateNameUS
                    , CONCAT(DATE_FORMAT(DateCounter,'%d'),'/',DATE_FORMAT(DateCounter,'%m'),'/',CAST(YEAR(DateCounter) AS CHAR(4)))#DateNameEU
                    , DAYOFWEEK(DateCounter) #DayOfWeek
                    , DAYNAME(DateCounter) #DayNameOfWeek
                    , DAYOFMONTH(DateCounter) #DayOfMonth
                    , DAYOFYEAR(DateCounter) #DayOfYear
                    , CASE DAYNAME(DateCounter)
                        WHEN 'Saturday' THEN 'Weekend'
                        WHEN 'Sunday' THEN 'Weekend'
                        ELSE 'Weekday'
                      END #WeekdayWeekend
                    , WEEKOFYEAR(DateCounter) #WeekOfYear
                    , MONTHNAME(DateCounter) #MonthName
                    , MONTH(DateCounter) #MonthOfYear
                    , LastDayOfMon #IsLastDayOfMonth
                    , QUARTER(DateCounter) #CalendarQuarter
                    , YEAR(DateCounter) #CalendarYear
                    , CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)),'-',DATE_FORMAT(DateCounter,'%m')) #CalendarYearMonth
                    , CONCAT(CAST(YEAR(DateCounter) AS CHAR(4)),'Q',QUARTER(DateCounter)) #CalendarYearQtr
                    , MONTH(FiscalCounter) #[FiscalMonthOfYear]
                    , QUARTER(FiscalCounter) #[FiscalQuarter]
                    , YEAR(FiscalCounter) #[FiscalYear]
                    , CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)),'-',DATE_FORMAT(FiscalCounter,'%m')) #[FiscalYearMonth]
                    , CONCAT(CAST(YEAR(FiscalCounter) AS CHAR(4)),'Q',QUARTER(FiscalCounter)) #[FiscalYearQtr]
                    );

            # Increment the date counter for next pass thru the loop
            SET DateCounter = DATE_ADD(DateCounter, INTERVAL 1 DAY);
      END WHILE;


END//


CALL PopulateDateDimension('2013/01/01', '2020/12/31'); 

Penggunaaan function trunc pada field yang bertipe date ternyata mengakibatkan index yang sudah dibuat menjadi tidak berfungsi.
Misalnya :

select * from claims c where TRUNC(C.SUBMISSION_DATE) BETWEEN TO_DATE('03112014','ddmmyyyy') AND TO_DATE('03112014','ddmmyyyy')

Untuk mengatasi hal ini bisa dilakukan dengan trik sebagai berikut

select * from claims c where C.SUBMISSION_DATE BETWEEN TO_DATE('03112014','ddmmyyyy') AND TO_DATE('03112014','ddmmyyyy') + 86399/86400

SELECT * FROM DBA_IND_COLUMNS WHERE TABLE_NAME='CLAIMS'