title: Oracle query optimization using dynamic dates
body: <p>I have a view where if I select like this:</p>
  
  <pre><code>select * from view where date = '17-sep-10'
  </code></pre>
  
  <p>it returns in seconds.</p>
  
  <p>If I use a dynamic date:</p>
  
  <pre><code>select * 
    from view 
   where date = to_date((select current_business_date 
                           from v_business_day), 
                        'mm/dd/yyyy')
  </code></pre>
  
  <p>it returns in 20 mins.</p>
  
  <p>Why would hard coding improve performance so much?  Also, the select in the to_date is not the issue (I think).  It returns almost instanteously when running it.</p>
  
  <p>Here is the desc for the view:</p>
  
  <pre><code>JAID_OWNER@algoja1p&gt; desc v_positions_rm_base
  Name                                      Null?    Type
  ----------------------------------------- -------- ----------------------------
   RUN_KEY                                   NOT NULL NUMBER(10)
   POSITION_KEY                              NOT NULL NUMBER(10)
   POSITIONS                                          VARCHAR2(50)
   INSTRUMENT_ID                                      VARCHAR2(151)
   BUSINESS_DATE                             NOT NULL DATE
   PROCESSING_DATE                                    DATE
   PROCESSING_STATUS_KEY                     NOT NULL NUMBER(10)
  </code></pre>
  
  <p>Here is the explain plan for when I hardcode the date.  I changed the query to do a to_date on a hardcoded value so it's consistent..</p>
  
  <pre><code>explain plan for select * from v_positions_rm_base where business_date = to_date('09/17/2010', 'mm/dd/yyyy')
  -----------------------------------------------------------------------------------------------------------------------------       
  | Id  | Operation                                    | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |       
  -----------------------------------------------------------------------------------------------------------------------------       
  |   0 | SELECT STATEMENT                             |                              |   518 |  1003K|  1679   (2)| 00:00:21 |       
  |*  1 |  FILTER                                      |                              |       |       |            |          |       
  |*  2 |   FILTER                                     |                              |       |       |            |          |       
  |*  3 |    HASH JOIN RIGHT OUTER                     |                              | 10360 |    19M|  1651   (2)| 00:00:20 |       
  |   4 |     INDEX FAST FULL SCAN                     | RM_SRC_ACCT_UDX              |   601 |  7212 |     2   (0)| 00:00:01 |       
  |   5 |     VIEW                                     |                              | 10360 |    19M|  1648   (2)| 00:00:20 |       
  |*  6 |      HASH JOIN RIGHT OUTER                   |                              | 10360 |    20M|  1648   (2)| 00:00:20 |       
  |   7 |       VIEW                                   | V_RM_FUTURES_CODE_TRANS_FLAT |     1 |    96 |     9  (12)| 00:00:01 |       
  |   8 |        HASH GROUP BY                         |                              |     1 |    51 |     9  (12)| 00:00:01 |       
  |   9 |         TABLE ACCESS BY INDEX ROWID          | CODE_TRANSLATION             |     3 |   153 |     8   (0)| 00:00:01 |       
  |* 10 |          INDEX RANGE SCAN                    | CODE_TRANSLATION_IDX3        |     3 |       |     7   (0)| 00:00:01 |       
  |* 11 |       HASH JOIN RIGHT OUTER                  |                              | 10360 |    19M|  1638   (2)| 00:00:20 |       
  |  12 |        VIEW                                  | V_RM_FUTURES_CODE_TRANS_FLAT |     1 |    96 |     9  (12)| 00:00:01 |       
  |  13 |         HASH GROUP BY                        |                              |     1 |    51 |     9  (12)| 00:00:01 |       
  |  14 |          TABLE ACCESS BY INDEX ROWID         | CODE_TRANSLATION             |     3 |   153 |     8   (0)| 00:00:01 |       
  |* 15 |           INDEX RANGE SCAN                   | CODE_TRANSLATION_IDX3        |     3 |       |     7   (0)| 00:00:01 |       
  |* 16 |        HASH JOIN RIGHT OUTER                 |                              | 10360 |    18M|  1629   (2)| 00:00:20 |       
  |  17 |         VIEW                                 | V_RM_FUTURES_CODE_TRANS_FLAT |   404 | 42420 |    12   (9)| 00:00:01 |       
  |  18 |          HASH GROUP BY                       |                              |   404 | 20604 |    12   (9)| 00:00:01 |       
  |  19 |           TABLE ACCESS BY INDEX ROWID        | CODE_TRANSLATION             |   404 | 20604 |    11   (0)| 00:00:01 |       
  |* 20 |            INDEX RANGE SCAN                  | CODE_TRANS_TYPE_IDX          |   404 |       |     3   (0)| 00:00:01 |       
  |* 21 |         HASH JOIN RIGHT OUTER                |                              | 10360 |    17M|  1616   (2)| 00:00:20 |       
  |  22 |          VIEW                                | V_RM_FUTURES_CODE_TRANS_FLAT |   404 | 42420 |    12   (9)| 00:00:01 |       
  |  23 |           HASH GROUP BY                      |                              |   404 | 20604 |    12   (9)| 00:00:01 |       
  |  24 |            TABLE ACCESS BY INDEX ROWID       | CODE_TRANSLATION             |   404 | 20604 |    11   (0)| 00:00:01 |       
  |* 25 |             INDEX RANGE SCAN                 | CODE_TRANS_TYPE_IDX          |   404 |       |     3   (0)| 00:00:01 |       
  |* 26 |          HASH JOIN RIGHT OUTER               |                              | 10360 |    16M|  1603   (2)| 00:00:20 |       
  |  27 |           TABLE ACCESS FULL                  | SOURCE_SYSTEM                |    68 |   748 |     3   (0)| 00:00:01 |       
  |* 28 |           HASH JOIN RIGHT OUTER              |                              | 10360 |    16M|  1599   (2)| 00:00:20 |       
  |  29 |            TABLE ACCESS BY INDEX ROWID       | CODE_TRANSLATION             |     1 |    51 |     2   (0)| 00:00:01 |       
  |* 30 |             INDEX RANGE SCAN                 | CODE_TRANS_TYPE_IDX          |     1 |       |     1   (0)| 00:00:01 |       
  |  31 |            VIEW                              |                              | 10360 |    16M|  1597   (2)| 00:00:20 |       
  |* 32 |             HASH JOIN RIGHT OUTER            |                              | 10360 |  7527K|  1597   (2)| 00:00:20 |       
  |  33 |              TABLE ACCESS BY INDEX ROWID     | PARAMETER_CONTROL            |     1 |    38 |     2   (0)| 00:00:01 |       
  |* 34 |               INDEX RANGE SCAN               | PARAMETER_CONTROL_IDX3       |     1 |       |     1   (0)| 00:00:01 |       
  |* 35 |              HASH JOIN RIGHT OUTER           |                              | 10360 |  7142K|  1594   (2)| 00:00:20 |       
  |  36 |               VIEW                           | V_ENTITY_FLAT                |  1742 |   282K|    35   (9)| 00:00:01 |       
  |* 37 |                HASH JOIN RIGHT OUTER         |                              |  1742 |   229K|    35   (9)| 00:00:01 |       
  |  38 |                 INDEX FAST FULL SCAN         | ENTITY_IDX_5                 |  1742 | 40066 |     6   (0)| 00:00:01 |       
  |* 39 |                 HASH JOIN RIGHT OUTER        |                              |  1742 |   190K|    28   (8)| 00:00:01 |       
  |  40 |                  INDEX FAST FULL SCAN        | ENTITY_IDX_5                 |  1742 | 47034 |     6   (0)| 00:00:01 |       
  |* 41 |                  HASH JOIN RIGHT OUTER       |                              |  1742 |   144K|    21   (5)| 00:00:01 |       
  |  42 |                   INDEX FAST FULL SCAN       | ENTITY_IDX_5                 |  1742 | 47034 |     6   (0)| 00:00:01 |       
  |* 43 |                   HASH JOIN RIGHT OUTER      |                              |  1742 |    98K|    15   (7)| 00:00:01 |       
  |  44 |                    INDEX FAST FULL SCAN      | ENTITY_IDX_5                 |  1742 | 47034 |     6   (0)| 00:00:01 |       
  |  45 |                    TABLE ACCESS FULL         | ENTITY                       |  1742 | 54002 |     8   (0)| 00:00:01 |       
  |* 46 |               HASH JOIN RIGHT OUTER          |                              |  2432 |  1282K|  1559   (1)| 00:00:19 |       
  |  47 |                TABLE ACCESS FULL             | INSTRUMENT_ID_TYPE           |     8 |    88 |     3   (0)| 00:00:01 |       
  |* 48 |                HASH JOIN RIGHT OUTER         |                              |  2432 |  1256K|  1555   (1)| 00:00:19 |       
  |  49 |                 TABLE ACCESS BY INDEX ROWID  | PROCESSING_STATUS            |     1 |    76 |     1   (0)| 00:00:01 |       
  |* 50 |                  INDEX UNIQUE SCAN           | PROCESSING_STATUS_PK         |     1 |       |     0   (0)| 00:00:01 |       
  |* 51 |                 HASH JOIN RIGHT OUTER        |                              |  2432 |  1075K|  1554   (1)| 00:00:19 |       
  |* 52 |                  TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION             |     1 |    65 |     2   (0)| 00:00:01 |       
  |* 53 |                   INDEX RANGE SCAN           | CODE_TRANS_TYPE_IDX          |     1 |       |     1   (0)| 00:00:01 |       
  |* 54 |                  HASH JOIN RIGHT OUTER       |                              |  2432 |   921K|  1551   (1)| 00:00:19 |       
  |* 55 |                   TABLE ACCESS BY INDEX ROWID| CODE_TRANSLATION             |     1 |    65 |     2   (0)| 00:00:01 |       
  |* 56 |                    INDEX RANGE SCAN          | CODE_TRANS_TYPE_IDX          |     1 |       |     1   (0)| 00:00:01 |       
  |* 57 |                   TABLE ACCESS BY INDEX ROWID| POSITIONS                    |  2432 |   767K|  1549   (1)| 00:00:19 |       
  |* 58 |                    INDEX RANGE SCAN          | POSITIONS_IDX_01             |  5676 |       |   737   (2)| 00:00:09 |       
  |* 59 |   INDEX RANGE SCAN                           | PARAMETER_CONTROL_IDX3       |     1 |    27 |     2   (0)| 00:00:01 |       
  ----------------------------------------------------------------------------------------------------------------------------- 
  </code></pre>
  
  <p>and here is the explain plan for the other view:</p>
  
  <pre><code>explain plan for select * from v_positions_rm_base where business_date = to_date((select current_business_date from v_business_day), 'mm/dd/yyyy');
  -------------------------------------------------------------------------------------------------------------------------------       
  | Id  | Operation                                      | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |       
  -------------------------------------------------------------------------------------------------------------------------------       
  |   0 | SELECT STATEMENT                               |                              |   171K|   324M|   366K  (3)| 01:13:16 |       
  |*  1 |  FILTER                                        |                              |       |       |            |          |       
  |*  2 |   FILTER                                       |                              |       |       |            |          |       
  |*  3 |    HASH JOIN RIGHT OUTER                       |                              |  3436K|  6485M|   366K  (3)| 01:13:16 |       
  |   4 |     INDEX FAST FULL SCAN                       | RM_SRC_ACCT_UDX              |   601 |  7212 |     2   (0)| 00:00:01 |       
  |*  5 |     VIEW                                       |                              |  3436K|  6446M|   366K  (3)| 01:13:15 |       
  |*  6 |      HASH JOIN RIGHT OUTER                     |                              |  3436K|  6806M|   366K  (3)| 01:13:15 |       
  |   7 |       VIEW                                     | V_RM_FUTURES_CODE_TRANS_FLAT |     1 |    96 |     9  (12)| 00:00:01 |       
  |   8 |        HASH GROUP BY                           |                              |     1 |    51 |     9  (12)| 00:00:01 |       
  |   9 |         TABLE ACCESS BY INDEX ROWID            | CODE_TRANSLATION             |     3 |   153 |     8   (0)| 00:00:01 |       
  |* 10 |          INDEX RANGE SCAN                      | CODE_TRANSLATION_IDX3        |     3 |       |     7   (0)| 00:00:01 |       
  |* 11 |       HASH JOIN RIGHT OUTER                    |                              |  3436K|  6492M|   366K  (3)| 01:13:15 |       
  |  12 |        VIEW                                    | V_RM_FUTURES_CODE_TRANS_FLAT |     1 |    96 |     9  (12)| 00:00:01 |       
  |  13 |         HASH GROUP BY                          |                              |     1 |    51 |     9  (12)| 00:00:01 |       
  |  14 |          TABLE ACCESS BY INDEX ROWID           | CODE_TRANSLATION             |     3 |   153 |     8   (0)| 00:00:01 |       
  |* 15 |           INDEX RANGE SCAN                     | CODE_TRANSLATION_IDX3        |     3 |       |     7   (0)| 00:00:01 |       
  |* 16 |        HASH JOIN RIGHT OUTER                   |                              |  3436K|  6177M|   366K  (3)| 01:13:14 |       
  |  17 |         VIEW                                   | V_RM_FUTURES_CODE_TRANS_FLAT |   404 | 42420 |    12   (9)| 00:00:01 |       
  |  18 |          HASH GROUP BY                         |                              |   404 | 20604 |    12   (9)| 00:00:01 |       
  |  19 |           TABLE ACCESS BY INDEX ROWID          | CODE_TRANSLATION             |   404 | 20604 |    11   (0)| 00:00:01 |       
  |* 20 |            INDEX RANGE SCAN                    | CODE_TRANS_TYPE_IDX          |   404 |       |     3   (0)| 00:00:01 |       
  |* 21 |         HASH JOIN RIGHT OUTER                  |                              |  3436K|  5833M|   366K  (3)| 01:13:13 |       
  |  22 |          VIEW                                  | V_RM_FUTURES_CODE_TRANS_FLAT |   404 | 42420 |    12   (9)| 00:00:01 |       
  |  23 |           HASH GROUP BY                        |                              |   404 | 20604 |    12   (9)| 00:00:01 |       
  |  24 |            TABLE ACCESS BY INDEX ROWID         | CODE_TRANSLATION             |   404 | 20604 |    11   (0)| 00:00:01 |       
  |* 25 |             INDEX RANGE SCAN                   | CODE_TRANS_TYPE_IDX          |   404 |       |     3   (0)| 00:00:01 |       
  |* 26 |          HASH JOIN RIGHT OUTER                 |                              |  3436K|  5489M|   366K  (3)| 01:13:13 |       
  |  27 |           TABLE ACCESS FULL                    | SOURCE_SYSTEM                |    68 |   748 |     3   (0)| 00:00:01 |       
  |* 28 |           HASH JOIN RIGHT OUTER                |                              |  3436K|  5453M|   365K  (3)| 01:13:12 |       
  |  29 |            TABLE ACCESS BY INDEX ROWID         | CODE_TRANSLATION             |     1 |    51 |     2   (0)| 00:00:01 |       
  |* 30 |             INDEX RANGE SCAN                   | CODE_TRANS_TYPE_IDX          |     1 |       |     1   (0)| 00:00:01 |       
  |  31 |            VIEW                                |                              |  3436K|  5286M|   365K  (3)| 01:13:11 |       
  |* 32 |             HASH JOIN RIGHT OUTER              |                              |  3436K|  2438M|   365K  (3)| 01:13:11 |       
  |  33 |              TABLE ACCESS BY INDEX ROWID       | PARAMETER_CONTROL            |     1 |    38 |     2   (0)| 00:00:01 |       
  |* 34 |               INDEX RANGE SCAN                 | PARAMETER_CONTROL_IDX3       |     1 |       |     1   (0)| 00:00:01 |       
  |  35 |              MERGE JOIN OUTER                  |                              |  3436K|  2313M|   365K  (3)| 01:13:11 |       
  |  36 |               MERGE JOIN OUTER                 |                              |  3436K|  2064M|   365K  (3)| 01:13:11 |       
  |  37 |                SORT JOIN                       |                              |   806K|   357M|   365K  (3)| 01:13:10 |       
  |  38 |                 MERGE JOIN OUTER               |                              |   806K|   357M|   365K  (3)| 01:13:10 |       
  |  39 |                  SORT JOIN                     |                              |   806K|   348M|   365K  (3)| 01:13:10 |       
  |* 40 |                   HASH JOIN RIGHT OUTER        |                              |   806K|   348M|   365K  (3)| 01:13:10 |       
  |* 41 |                    TABLE ACCESS BY INDEX ROWID | CODE_TRANSLATION             |     1 |    65 |     2   (0)| 00:00:01 |       
  |* 42 |                     INDEX RANGE SCAN           | CODE_TRANS_TYPE_IDX          |     1 |       |     1   (0)| 00:00:01 |       
  |* 43 |                    HASH JOIN RIGHT OUTER       |                              |   806K|   298M|   365K  (3)| 01:13:10 |       
  |* 44 |                     TABLE ACCESS BY INDEX ROWID| CODE_TRANSLATION             |     1 |    65 |     2   (0)| 00:00:01 |       
  |* 45 |                      INDEX RANGE SCAN          | CODE_TRANS_TYPE_IDX          |     1 |       |     1   (0)| 00:00:01 |       
  |* 46 |                     TABLE ACCESS FULL          | POSITIONS                    |   806K|   248M|   365K  (3)| 01:13:09 |       
  |* 47 |                  SORT JOIN                     |                              |     8 |    88 |     4  (25)| 00:00:01 |       
  |  48 |                   TABLE ACCESS FULL            | INSTRUMENT_ID_TYPE           |     8 |    88 |     3   (0)| 00:00:01 |       
  |* 49 |                SORT JOIN                       |                              |  1742 |   282K|    36  (12)| 00:00:01 |       
  |  50 |                 VIEW                           | V_ENTITY_FLAT                |  1742 |   282K|    35   (9)| 00:00:01 |       
  |* 51 |                  HASH JOIN RIGHT OUTER         |                              |  1742 |   229K|    35   (9)| 00:00:01 |       
  |  52 |                   INDEX FAST FULL SCAN         | ENTITY_IDX_5                 |  1742 | 40066 |     6   (0)| 00:00:01 |       
  |* 53 |                   HASH JOIN RIGHT OUTER        |                              |  1742 |   190K|    28   (8)| 00:00:01 |       
  |  54 |                    INDEX FAST FULL SCAN        | ENTITY_IDX_5                 |  1742 | 47034 |     6   (0)| 00:00:01 |       
  |* 55 |                    HASH JOIN RIGHT OUTER       |                              |  1742 |   144K|    21   (5)| 00:00:01 |       
  |  56 |                     INDEX FAST FULL SCAN       | ENTITY_IDX_5                 |  1742 | 47034 |     6   (0)| 00:00:01 |       
  |* 57 |                     HASH JOIN RIGHT OUTER      |                              |  1742 |    98K|    15   (7)| 00:00:01 |       
  |  58 |                      INDEX FAST FULL SCAN      | ENTITY_IDX_5                 |  1742 | 47034 |     6   (0)| 00:00:01 |       
  |  59 |                      TABLE ACCESS FULL         | ENTITY                       |  1742 | 54002 |     8   (0)| 00:00:01 |       
  |* 60 |               SORT JOIN                        |                              |     1 |    76 |     2  (50)| 00:00:01 |       
  |  61 |                TABLE ACCESS BY INDEX ROWID     | PROCESSING_STATUS            |     1 |    76 |     1   (0)| 00:00:01 |       
  |* 62 |                 INDEX UNIQUE SCAN              | PROCESSING_STATUS_PK         |     1 |       |     0   (0)| 00:00:01 |       
  |  63 |      MERGE JOIN CARTESIAN                      |                              |     1 |   119 |     4   (0)| 00:00:01 |       
  |  64 |       MERGE JOIN CARTESIAN                     |                              |     1 |    81 |     3   (0)| 00:00:01 |       
  |  65 |        MERGE JOIN CARTESIAN                    |                              |     1 |    54 |     2   (0)| 00:00:01 |       
  |* 66 |         INDEX RANGE SCAN                       | PARAMETER_CONTROL_IDX3       |     1 |    27 |     1   (0)| 00:00:01 |       
  |  67 |         BUFFER SORT                            |                              |     1 |    27 |     1   (0)| 00:00:01 |       
  |* 68 |          INDEX RANGE SCAN                      | PARAMETER_CONTROL_IDX3       |     1 |    27 |     1   (0)| 00:00:01 |       
  |  69 |        BUFFER SORT                             |                              |     1 |    27 |     2   (0)| 00:00:01 |       
  |* 70 |         INDEX RANGE SCAN                       | PARAMETER_CONTROL_IDX3       |     1 |    27 |     1   (0)| 00:00:01 |       
  |  71 |       BUFFER SORT                              |                              |     1 |    38 |     3   (0)| 00:00:01 |       
  |* 72 |        TABLE ACCESS BY INDEX ROWID             | PARAMETER_CONTROL            |     1 |    38 |     1   (0)| 00:00:01 |       
  |* 73 |         INDEX RANGE SCAN                       | PARAMETER_CONTROL_IDX1       |     2 |       |     0   (0)| 00:00:01 |       
  |* 74 |   INDEX RANGE SCAN                             | PARAMETER_CONTROL_IDX3       |     1 |    27 |     2   (0)| 00:00:01 |       
  -------------------------------------------------------------------------------------------------------------------------------       
  </code></pre>