Be careful when using SYSDATE in the predicate on 9i databases

I had a task to check a SQL and modify it to be used to generate hourly report. The original SQL given looks like (table and column names modified as usual):

---- literal string for the date  
SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count
  FROM o_tr tr, 
       o_mas mas
 WHERE mas.is_pq = 'N'
   AND mas.int_o_id = tr.int_o_id
   AND tr.nt_id IN (201, 209, 217, 222)
   AND mas.o_save_date >= To_Date('01/22/2009 00:00:00','MM/DD/YYYY HH24:MI:SS') 
   AND mas.o_save_date < To_Date('01/23/2009 00:00:00','MM/DD/YYYY HH24:MI:SS')
 GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;

I run it and the excution plan looks like:

-------------------------------------------------------------------------
| Id  | Operation                      |  Name    |Rows | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     |       |    41 |
|   1 |  SORT GROUP BY                 |          |   4 |   148 |    41 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | O_TR     |   2 |    40 |     9 |
|   3 |    NESTED LOOPS                |          |   4 |   148 |    25 |
|   4 |     TABLE ACCESS BY INDEX ROWID| O_MAS    |   2 |    34 |     7 |
|   5 |      INDEX RANGE SCAN          | O_MAS_IX2|   3 |       |     4 |
|   6 |     INDEX RANGE SCAN           | O_TR_FK3 |  14 |       |     3 |
-------------------------------------------------------------------------

The SQL returns in less than a minute. Then I execuate the following SQL which uses
SYSDATE instead of literal date.

SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count
  FROM o_tr tr, 
       o_mas mas
 WHERE mas.is_pq = 'N'
   AND mas.int_o_id = tr.int_o_id
   AND tr.nt_id IN (201, 209, 217, 222)
   AND mas.o_save_date >= trunc(sysdate)-1 
   AND mas.o_save_date < trunc(sysdate) 
 GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;

The execution plan changed to hash join and checked v$longops, indicating table scan will takes more than 1 hour, ie:

-------------------------------------------------------------------------------
| Id  | Operation                      |  Name     |Rows  |Bytes |TempSpc| Cost |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |      |      |       |   699K|
|   1 |  SORT GROUP BY                 |           | 2445K|   86M|   243M|   699K|
|   2 |   FILTER                       |           |      |      |       |       |
|   3 |    HASH JOIN                   |           | 2445K|   86M|    30M|   683K|
|   4 |     TABLE ACCESS BY INDEX ROWID| O_MAS     | 1113K|   18M|       | 38909 |
|   5 |      INDEX RANGE SCAN          | O_MAS_IX2 |  401K|      |       |  2976 |
|   6 |     TABLE ACCESS FULL          | O_TR      |   55M| 1053M|       |   617K|
---------------------------------------------------------------------------------

I tried to use hint to force the Oracle to use the first execution plan, the cost is 10M, no surprise that Oracle won't choose this plan.

SELECT 
/*+ use_nl( mas, tr) index(tr O_TR_FK3) */
SELECT To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count
  FROM o_tr tr, 
       o_mas mas
 WHERE mas.is_pq = 'N'
   AND mas.int_o_id = tr.int_o_id
   AND tr.nt_id IN (201, 209, 217, 222)
   AND mas.o_save_date >= trunc(sysdate)-1 
   AND mas.o_save_date < trunc(sysdate) 
 GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
;


-------------------------------------------------------------------------------------
| Id  | Operation                       |  Name     | Rows  | Bytes |TempSpc| Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |       |       |       |    10M|
|   1 |  SORT GROUP BY                  |           |  2445K|    86M|   243M|    10M|
|   2 |   FILTER                        |           |       |       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID  | O_TR      |     2 |    40 |       |     9 |
|   4 |     NESTED LOOPS                |           |  2445K|    86M|       |    10M|
|   5 |      TABLE ACCESS BY INDEX ROWID| O_MAS     |  1113K|    18M|       | 38909 |
|   6 |       INDEX RANGE SCAN          | O_MAS_IX2 |   401K|       |       |  2976 |
|   7 |      INDEX RANGE SCAN           | O_TR_FK3  |    14 |       |       |     3 |
-------------------------------------------------------------------------------------

For the hourly report, I finally obtained the following SQL:

SELECT  /*+ use_nl( mas, tr) index(mas O_MAS_IX2) index(tr O_TR_FK3) */
       To_Char(mas.o_save_date, 'MM/DD/YYYY HH24') o_time,
       DECODE(tr.nt_id, '201','D','209','F','217','V','222','FV') svc_type,
       tr.tr_type_id,
       Count(DISTINCT tr.bo_id) o_count 
 FROM 
      o_mas mas,
      o_tr tr 
WHERE mas.is_pq = 'N'
  AND mas.int_o_id = tr.int_o_id
  AND tr.nt_id IN (201, 209, 217, 222)
  AND mas.o_save_date >= trunc(sysdate,'HH24') - 1/24 
  AND mas.o_save_date <  trunc(sysdate, 'HH24')
GROUP BY To_Char(mas.o_save_date, 'MM/DD/YYYY HH24'), tr.nt_id, tr.tr_type_id
/

This SQl returns in seconds. If not using the hint, Oracle will choose hash join. What happend here? I seek answers from Jonathan Lewis's book "Cost-Based Oracle Fundamentals" as I vaguelly remember there is something about SYSDATE. I am right. His book has a section titled "Surprising Sysdate" (p130) that shed light on this issue. Basically, Oracle treat "Sysdate+N" as unknown, and treat it same as bind variable. As a result, optimizer can not calculate the cardinality and selectivity in the same way as the case of literal string. Oracle 10g has identified and addressed this problem.