Date Dimension vs. Function-Based Indexes: Is the Date Dimension Still Relevant?

Jun 3, 2008 / By Robert Hamel

Tags: ,

The purpose of this post is to verify if a date dimension is better in regards to performance and functionality than a series of function-based indexes on a date column in the fact table.

The following is the date dimension and fact table used for the testing:

Date Dimension

create table date_dimension(
date_id number, --1..x days
sql_date date, -- trunc(date)
day_of_week varchar2(30), -- (long version)
day_of_week_short varchar2(3), -- (short version)
day_number_in_week number, -- (1..7)
day_number_in_month number, --(1..31)
day_number_in_year number, --(1..366)
workday varchar2(10), --(either workday or holiday)
holiday varchar2(50), 
  --(specify text e.g. christmas, easter...)
weekday varchar2(10), -- (specify weekday or weekend)
last_day_in_month number, -- (28,29,30 or 31)
week_number_in_year number , --1..52
week_number_in_month number, --1,5
month varchar2(10) ,--(January...)
month_short varchar2(3), -- (Jan...)
month_number_in_year number, -- (1..12)
quarter_in_year number, -- (1,2,3,4)
year number, -- (4 digits)
chinesezodiac varchar2(50),
zodiac varchar2(50) ,
event varchar2(100) -- (election day,Hurricane Hugo) text field
) ;

The are rows for every day from the year 1850 to 2050 (way after I retire). There is a primary key on date_id, a unique key on sql_date, and bitmap indexes on all other columns. The table is compressed and its stats computed.

Segment sizes:

WEEK_NUMBER_IN_YEAR_IDX	.125
MONTH_IDX	.0625
MONTH_SHORT_IDX	.0625
MONTH_NUMBER_IN_YEAR_IDX	.0625
QUARTER_IN_YEAR_IDX	.0625
YEAR_IDX	.0625
EVENT_IDX	.0625
WEEK_NUMBER_IN_MONTH_IDX	.125
CHINESEZODIAC_IDX	.0625
ZODIAC_IDX	.0625
WORKDAY_IDX	.0625
HOLIDAY_IDX	.0625
WEEKDAY_IDX	.0625
LAST_DAY_IN_MONTH_IDX	.0625
DATE_DIMENSION_SQL_DATE	2
DATE_DIMENSION_PK	2
DAY_OF_WEEK_IDX	.1875
DAY_OF_WEEK_SHORT_IDX	.1875
DAY_NUMBER_IN_WEEK_IDX	.1875
DAY_NUMBER_IN_MONTH_IDX	.1875
DAY_NUMBER_IN_YEAR_IDX	.3125

Fact Table

create table fact (id number, tx_date_dm_id number, tx_date date, amount number);

There are 1 million rows in this table, with a compressed index on the date_id and tx_date (since it’s truncated to the day), as well as the following function-based indexes on the tx_date column:

create index fact_tx_date_mm on fact( to_char(tx_date,'mm')) ;
create index fact_tx_date_yyyy on fact( trunc(tx_date,'yyyy')) ;
create index fact_tx_date_wnim on fact( to_char(tx_date,'w')) ;
create index fact_tx_date_dow on fact( to_char(tx_date,'dy')) ;

Segment Sizes:

FACT	32
FACT_TX_DATE_YYYY	.25
FACT_TX_DATE_WNIM	.25
FACT_TX_DATE_DOW	.25
FACT_TX_DATE_MM	.25
FACT_TX_DATE_IDX	13
FACT_DATE_ID_IDX	13

So let’s start looking at typical queries and see how they perform.

Query 1: sum of amount for a specific date range

The fact date column-based query:

select sum(amount), count(*) from fact f where f.tx_date between to_date('20070501','yyyymmdd') and to_date('20070601','yyyymmdd');

SUM(AMOUNT)   COUNT(*)
----------- ----------
 9.8095E+10      33251

Elapsed: 00:00:00.38

Execution Plan
----------------------------------------------------------
Plan hash value: 2208459562

-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |    21 |   324   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE              |                  |     1 |    21 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| FACT             | 34530 |   708K|   324   (1)| 00:00:04 |
|*  3 |    INDEX RANGE SCAN          | FACT_TX_DATE_IDX | 34530 |       |    57   (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("F"."TX_DATE">=TO_DATE('2007-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "F"."TX_DATE"<=TO_DATE('2007-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        309  consistent gets
        107  physical reads
          0  redo size
        594  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

The date dimension-based query:

select  sum(amount), count(*) from fact f, date_dimension d where f.date_id  = d.date_id and sql_date between to_date('20070501','yyyymmdd') and to_date('20070601','yyyymmdd');

SUM(AMOUNT)   COUNT(*)
----------- ----------
 1.1881E+12     400584

Elapsed: 00:00:29.95

Execution Plan
----------------------------------------------------------
Plan hash value: 182493474

----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |     1 |    31 |   116   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE                |                         |     1 |    31 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID  | FACT                    |  1437 | 25866 |    15   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |                         | 47414 |  1435K|   116   (1)| 00:00:02 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DATE_DIMENSION          |    33 |   429 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | DATE_DIMENSION_SQL_DATE |    33 |      |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | FACT_DATE_ID_IDX        |  1437 |      |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("SQL_DATE">=TO_DATE('2007-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "SQL_DATE"<=TO_DATE('2007-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   6 - access("F"."DATE_ID"="D"."DATE_ID")

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        415  consistent gets
          0  physical reads
          0  redo size
        594  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Query 2: sum of amount for all Thanksgiving days

The fact-only-based query:

select sum(amount), count(*) from fact where to_char(tx_date,'dy') = 'thu' and to_char(tx_date,'mm')='11' and to_char(tx_date,'w')='4';

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
Plan hash value: 1038184904

---------------------------------------------------------------------------------------------------
| Id | Operation 			| Name 		    | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT 			| 		    |    1 |    21 | 374 (1)| 00:00:05 |
| 1 | SORT AGGREGATE 			| 		    |    1 |    21 |        |          |
| 2 | TABLE ACCESS BY INDEX ROWID 	| FACT 		    | 2381 | 50001 | 374 (1)| 00:00:05 |
| 3 | BITMAP CONVERSION TO ROWIDS	| 		    |      |       |        | 	       |
| 4 | BITMAP AND 			| 		    |      |       | 	    | 	       |
|* 5 | BITMAP INDEX SINGLE VALUE	| FACT_TX_DATE_MM   |      |       | 	    | 	       |
|* 6 | BITMAP INDEX SINGLE VALUE	| FACT_TX_DATE_DOW  |      |       | 	    | 	       |
|* 7 | BITMAP INDEX SINGLE VALUE	| FACT_TX_DATE_WNIM |      |       | 	    | 	       |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access(TO_CHAR(INTERNAL_FUNCTION("TX_DATE"),'mm')='11')
6 - access(TO_CHAR(INTERNAL_FUNCTION("TX_DATE"),'dy')='thu')
7 - access(TO_CHAR(INTERNAL_FUNCTION("TX_DATE"),'w')='4')

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
39 consistent gets
0 physical reads
0 redo size
591 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

The date-dimension-based query:

select sum(amount), count(*) from fact f, date_dimension d where f.date_id = d.date_id and d.holiday = 'Thanksgiving';

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 688750045

----------------------------------------------------------------------------------------------------
| Id | Operation                  | Name             | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT            |                  | 1    | 25    | 758 (2)    | 00:00:10 |
| 1 | SORT AGGREGATE              |                  | 1    | 25    |            | |
| 2 | TABLE ACCESS BY INDEX ROWID | FACT             | 1437 | 25866 | 15 (0)     | 00:00:01 |
| 3 | NESTED LOOPS                |                  | 302K | 7383K | 758 (2)    | 00:00:10 |
| 4 | TABLE ACCESS BY INDEX ROWID | DATE_DIMENSION   | 211  | 1477  | 94 (2)     | 00:00:02 |
| 5 | BITMAP CONVERSION TO ROWIDS |                  |      |       |            | |
|* 6 | BITMAP INDEX SINGLE VALUE  | HOLIDAY_IDX      |      |       |            | |
|* 7 | INDEX RANGE SCAN           | FACT_DATE_ID_IDX | 1437 |       | 3 (0)      | 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("D"."HOLIDAY"='Thanksgiving')
7 - access("F"."DATE_ID"="D"."DATE_ID")

Statistics
----------------------------------------------------------
410 recursive calls
0 db block gets
742 consistent gets
0 physical reads
116 redo size
591 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed

So far, from a query performance point of view, the function-based indexes significantly outperform the date_dimension. The only performance-related drawback so far is the extra indexes to update during the fact table’s loading, as well as the additional 10-or-so indexes required for every date column in the fact table.

But it is query complexity where the date dimension really shines. Although I agree that most of the time, everybody is interested only in what happened in the last 30 days or year-to-date, which is easy SQL to write. What about something never specified in the specs? What about when some marketing guy gets on the datamart and wants to compare sales on holidays that were on weekend days versus sales on holidays that were on weekdays? Without a pre-populated date dimension, this person would have to figure out which holidays they were interested in, and then historically tie them to dates. That’s relatively easy for Christmas and New Years, but not so obvious for Thanksgiving, and even worse for Good Friday and Easter Sunday.

So even though the date_dimension would be slower, it would take more time to actually write the query otherwise than to run it. This is what it looks like:

select holiday, weekday, sum(amount) from fact f, date_dimension d where f.date_id = d.date_id
and holiday is not null
group by holiday, weekday;

So what are we to do? Like most things, you need a little bit of both. I would have only the regular date column and setup the reporting tool to join the straight date or an index on trunc(date), if it constrains a time component, and a date_id joining to the date_dimension for more complex queries.

2 Responses to “Date Dimension vs. Function-Based Indexes: Is the Date Dimension Still Relevant?”

  • Vyacheslav Rasskazov says:

    Hi, Robert
    Am I correct in thinking that there is little error at date dimension-based query for a specific date range. Condition f.date_id >= d.date_id must be changed for f.date_id = d.date_id. Otherwise, comparision is not correct, because two queries simply produced different results.

  • Polarski Bernard says:

    The article seems interresting butI gave up. The enclosed SQL output font are so small I can barely read. Setting the character to biggest does not affect this part of the text. I bet I am not the only one to be affected.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>