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.
