SELECT  /*+ INDEX (A TXN_BB1_N3)INDEX(B DEVX_EVENT_F1) INDEX(D MANUFACTURER_BB_PK) */ --DEVX_EVENT_SEQ.NEXTVAL,         --objid
a.eventid,                                   --event_id
a.sid,                                       --sid
'Y',                                         --md5_gen_flag
DECODE(a.type, 'TA', a.adjustment_dap, a.dap),             --dev_price.
0,                                           --amt.
0,                                           --net_amt
a.adjustment_eventid,                        --adjustment_event_id
a.adjustment_reason,                         --adjustment_rsn
TRUNC(a.bds_date_created),                   --bds_date_created
a.gmt,                                       --gmt
a.ext_count,                                 --ext_cnt
a.type,                                      --type.
'',                                          --exclusion_flag
'',                                          --exclusion_reason
'TB',                                        --table_src
a.eventid,                                   --txn_orig_event_id. Verify what this thing is.
a.primary_carrier_name,                      --pc_name
a.manufacturer,                              --mfr_name
TO_CHAR(a.bds_date_created, 'YYYYMM'),       --year_month
a.mfg_partno,                                --mfg_partno
a.mfg_partname,                              --mfg_partname. Select Partname from Boomers on.
'',                                          --bill_type
'',                                          --mpi_rule_id
a.bill_flag,                                 --bill_flag
a.method,                                    --method
a.value,                                     --value
a.dap_currency,                              --currency
a.basis,                                     --basis
a.network_flag,                              --network_flag
a.gps_flag,                                  --gps_flag
a.platformid,                                --platform_id
a.item_list_name,                            --application_name
a.vendordata,                                --vendordata
'NON-INVOICE',                               --stage_type
0,                                           --stage_id
a.apptype,                                   --apptype
a.billing_level,                             --billing_level
a.subtype,                                   --subtype
a.source_type,                               --source_type
a.substart_billdate,                         --substart_billdate
a.subend_billdate,                           --subend_billdate
a.ITEMID,
a.ITEM_NAME,
a.API,
a.LANGUAGE,
a.PRICE_TYPE,
a.PRICE_TYPE_NAME,
a.mime_type
from 
BILLING.txn_bb a, 
BILLING.devx_event b,
BILLING.manufacturer_bb d
where a.bds_date_created >= '29-May-2008'
and a.bds_date_created < '30-May-2008'
and NOT ((a.type in ('DA', 'TR', 'CA', 'ER')) OR
          (a.type = 'DL' and a.method = '3') OR
          ( a.restrict_flag = 'Y' and NVL(a.ext_count, 0) = 0))
and a.primary_carrier_name = 'Verizon'
and a.manufacturer = d.mfr_name
and a.primary_carrier_name = b.pc_name(+)
--Original: commented by Sandeep Maini for CR85205 Improve performance of the PKG_DEV_EXTRACT
--and a.eventid = b.event_id(+)
--changed by smaini for CR85205 Improve performance of the PKG_DEV_EXTRACT
and to_char(a.eventid) = b.event_id(+)
and b.event_id is null
/

--------------------------------------------
explain plan is

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2873170473

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |   658K|   341M|  3050K  (1)| 10:10:03 |       |       |
|*  1 |  FILTER                               |                    |       |       |            |          |       |       |
|   2 |   NESTED LOOPS OUTER                  |                    |   658K|   341M|  3050K  (1)| 10:10:03 |       |       |
|*  3 |    HASH JOIN                          |                    |   527K|   263M|  1465K  (1)| 04:53:12 |       |       |
|   4 |     INDEX FULL SCAN                   | MANUFACTURER_BB_PK |  4090 | 40900 |    13   (0)| 00:00:01 |       |       |
|   5 |     PARTITION RANGE SINGLE            |                    |   566K|   277M|  1465K  (1)| 04:53:12 |    29 |    29 |
|*  6 |      TABLE ACCESS BY LOCAL INDEX ROWID| TXN_BB             |   566K|   277M|  1465K  (1)| 04:53:12 |    29 |    29 |
|*  7 |       INDEX RANGE SCAN                | TXN_BB1_N3         |   259K|       | 30795   (1)| 00:06:10 |    29 |    29 |
|*  8 |    INDEX RANGE SCAN                   | DEVX_EVENT_F1      |     1 |    20 |     3   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   4 - SEL$1 / D@SEL$1
   6 - SEL$1 / A@SEL$1
   7 - SEL$1 / A@SEL$1
   8 - SEL$1 / B@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------