SELECT /*+ index(e EM_PK, o OEM_PK tl SYSC002258) */       
    e.event_id AS event_id,
    e.app_id AS system_id,
    e.event_type AS event_type_code,
    to_date(tl.TIME_STRING, 'hh24:mi:ss') AS execution_timestamp,
    e.SYMBOL as ISSUE_SYMBOL_ID,
    e.terminal_id,        
    e.buy_sell_code AS buy_sell_code, 
    NULL AS act_branch_sequence,            
    e.ext_order AS order_id,
    o.received_method_code AS received_method_code,
    e.shares_quantity AS executed_shares_quantity,
    NULL AS execution_leaves_quantity,            
    NULL AS market_center_id,
    NULL AS execution_type_indicator,
    NULL AS bid_px, 
    NULL AS bid_qty, 
    NULL AS ask_px,
    NULL AS ask_qty,     
    NULL AS execution_price,
    NULL AS last_market, 
    NULL AS contrabroker,
    e.capacity AS execution_capacity, 
    NULL AS execution_reason,
    NULL AS street_exec_only_flag,     
    e.parent_order AS parent_order_id,
    e.firm_id , 
    NULL AS execution_id,        
    NULL AS act_report_time,
    r.trade_through_exempt,
    r.reason_tt_exemp_lvl2,
    r.extended_hour_sold_lvl3,
    r.unus_trade_detail_lvl4
   FROM event_main e, order_event_main o, event_reg_nms_main r, time_lookup tl,
   (SELECT DATE_ID FROM DATE_LOOKUP a where DATE_STRING = to_char(to_date('23-Jul-2007','dd-Mon-yyyy'),'YYYYMMDD')) dl
   WHERE 
   e.event_id = o.event_id
   AND e.event_date_id = dl.date_id
   AND tl.time_id = e.event_time_id
   AND r.EVENT_ID = e.EVENT_ID 
   and e.event_date_id=o.event_date_id 
   and e.event_date_id=r.event_date_id  
   and (r.TRADE_THROUGH_EXEMPT = 'Y' OR r.REASON_TT_EXEMP_LVL2 is not NULL)

EXPLAIN

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             |  Name               | Rows  | Bytes | Cost  | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |  9310 |  1272K|  1622K|       |       |
|*  1 |  HASH JOIN                            |                     |  9310 |  1272K|  1622K|       |       |
|   2 |   NESTED LOOPS                        |                     |  9310 |  1172K|  1622K|       |       |
|   3 |    NESTED LOOPS                       |                     | 28912 |  3275K|  1564K|       |       |
|   4 |     NESTED LOOPS                      |                     |   780K|    34M|  3689 |       |       |
|   5 |      TABLE ACCESS BY INDEX ROWID      | DATE_LOOKUP         |     1 |    12 |     2 |       |       |
|*  6 |       INDEX UNIQUE SCAN               | DL_U1               |     1 |       |     1 |       |       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
|   7 |      PARTITION RANGE ALL              |                     |       |       |       |     1 |    32 |
|*  8 |       TABLE ACCESS FULL               | EVENT_REG_NMS_MAIN  |   780K|    25M|  3687 |     1 |    32 |
|   9 |     PARTITION RANGE ITERATOR          |                     |       |       |       |   KEY |   KEY |
|  10 |      TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_MAIN          |     1 |    70 |     2 |   KEY |   KEY |
|* 11 |       INDEX UNIQUE SCAN               | EM_PK               |     1 |       |     1 |   KEY |   KEY |
|  12 |    PARTITION RANGE ITERATOR           |                     |       |       |       |   KEY |   KEY |
|  13 |     TABLE ACCESS BY LOCAL INDEX ROWID | ORDER_EVENT_MAIN    |     1 |    13 |     2 |   KEY |   KEY |
|* 14 |      INDEX UNIQUE SCAN                | OEM_PK              |     1 |       |     1 |   KEY |   KEY |
|  15 |   TABLE ACCESS FULL                   | TIME_LOOKUP         | 86688 |   931K|     8 |       |       |
-------------------------------------------------------------------------------------------------------------

SELECT  
    e.event_id AS event_id,
    e.app_id AS system_id,
    e.event_type AS event_type_code,
    to_date(tl.TIME_STRING, 'hh24:mi:ss') AS execution_timestamp,
    e.SYMBOL as ISSUE_SYMBOL_ID,
    e.terminal_id,        
    e.buy_sell_code AS buy_sell_code, 
    NULL AS act_branch_sequence,            
    e.ext_order AS order_id,
    o.received_method_code AS received_method_code,
    e.shares_quantity AS executed_shares_quantity,
    NULL AS execution_leaves_quantity,            
    NULL AS market_center_id,
    NULL AS execution_type_indicator,
    NULL AS bid_px, 
    NULL AS bid_qty, 
    NULL AS ask_px,
    NULL AS ask_qty,     
    NULL AS execution_price,
    NULL AS last_market, 
    NULL AS contrabroker,
    e.capacity AS execution_capacity, 
    NULL AS execution_reason,
    NULL AS street_exec_only_flag,     
    e.parent_order AS parent_order_id,
    e.firm_id , 
    NULL AS execution_id,        
    NULL AS act_report_time,
    r.trade_through_exempt,
    r.reason_tt_exemp_lvl2,
    r.extended_hour_sold_lvl3,
    r.unus_trade_detail_lvl4
   FROM event_main e, order_event_main o, event_reg_nms_main r, time_lookup tl 
   WHERE 
   e.event_date_id in (SELECT dl.DATE_ID FROM DATE_LOOKUP dl where dl.DATE_STRING = to_char(to_date('23-Jul-2007','dd-Mon-yyyy'),'YYYYMMDD'))
   AND e.event_id = o.event_id
   AND tl.time_id = e.event_time_id
   AND r.EVENT_ID = e.EVENT_ID 
   and e.event_date_id=o.event_date_id 
   and e.event_date_id=r.event_date_id  
   and (r.TRADE_THROUGH_EXEMPT = 'Y' OR r.REASON_TT_EXEMP_LVL2 is not NULL)

e.event_date_id in (SELECT dl.DATE_ID FROM DATE_LOOKUP dl where dl.DATE_STRING = to_char(to_date('23-Jul-2007','dd-Mon-yyyy'),'YYYYMMDD'))

(
  DATE_ID      INTEGER,
  DATE_STRING  VARCHAR2(8 BYTE)                 NOT NULL
)

WHERRE e.event_date_id = LOOKUP_DATE('20070723')


CREATE OR REPLACE FUNCTION LOOKUP_DATE (DT_STRING VARCHAR2)
RETURN INTEGER IS
OUT_VAL INTEGER;
BEGIN
   SELECT DATE_ID INTO OUT_VAL FROM DATE_LOOKUP WHERE DATE_STRING = DT_STRING;
   RETURN OUT_VAL;
END LOOKUP_DATE;