YY vs YYYY…It can make a BIG difference

Check out the execution plan differences here.  The fully qualified date (2nd one) gets it right, and note that the optimizer gains the partition start / stop information.

SQL> select fds.fin_account_num,
2  sum(fds.sales) as sales from whs.fin_day_summary fds
3  where activity_date >= to_date(’30-08-12′,’dd-mm-yy’) – 390
4  and activity_date <= to_date(’30-08-12′,’dd-mm-yy’)
5  and fds.fin_account_num is not null
6  group by fds.fin_account_num ;

————————————————————————————————————————————
| Id  | Operation                             | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
————————————————————————————————————————————
|   0 | SELECT STATEMENT                      |                    | 29264 |   457K|       | 48512   (1)| 00:09:43 |       |       |
|   1 |  HASH GROUP BY                        |                    | 29264 |   457K|  1312K| 48512   (1)| 00:09:43 |       |       |
|*  2 |   FILTER                              |                    |       |       |       |         |     |       |       |
|   3 |    PARTITION RANGE ITERATOR           |                    | 47222 |   737K|       | 48318   (1)| 00:09:40 |   KEY |   KEY |
|   4 |     PARTITION LIST ALL                |                    | 47222 |   737K|       | 48318   (1)| 00:09:40 |     1 |     4 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| FIN_DAY_SUMMARY    | 47222 |   737K|       | 48318   (1)| 00:09:40 |   KEY |   KEY |
|*  6 |       INDEX RANGE SCAN                | FIN_DAY_SUMMARY_UQ | 47222 |       |       |   785   (1)| 00:00:10 |   KEY |   KEY |
————————————————————————————————————————————

SQL> select fds.fin_account_num,
2  sum(fds.sales) as sales from whs.fin_day_summary fds
3  where activity_date >= to_date(’30-08-2012′,’dd-mm-yyyy’) – 390
4  and activity_date <= to_date(’30-08-2012′,’dd-mm-yyyy’)
5  and fds.fin_account_num is not null
6  group by fds.fin_account_num ;

———————————————————————————————————————
| Id  | Operation                 | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
———————————————————————————————————————
|   0 | SELECT STATEMENT          |                 | 29264 |   457K|       | 24893   (5)| 00:04:59 |       |       |
|   1 |  HASH GROUP BY            |                 | 29264 |   457K|  1312K| 24893   (5)| 00:04:59 |       |       |
|   2 |   PARTITION RANGE ITERATOR|                 | 47222 |   737K|       | 24699   (5)| 00:04:57 |    98 |   153 |
|   3 |    PARTITION LIST ALL     |                 | 47222 |   737K|       | 24699   (5)| 00:04:57 |     1 |     4 |
|*  4 |     TABLE ACCESS FULL     | FIN_DAY_SUMMARY | 47222 |   737K|       | 24699   (5)| 00:04:57 |   389 |   612 |
———————————————————————————————————————

One thought on “YY vs YYYY…It can make a BIG difference

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s