Inefficient Index I_EVENT_PAYMENT_TRANS__ID During Payment Reversal (Doc ID 763129.1)

Last updated on MARCH 30, 2012

Applies to:

Oracle Communications Billing and Revenue Management - Version: 7.3.0.0.0 to 7.3.0.0.1 - Release: 7.3.0 to 7.3.0
Oracle Solaris on SPARC (32-bit)
Sun Solaris SPARC (32-bit)

Symptoms

When using opcode PCM_OP_BILL_REVERSE (used for payment reversal), the performance on a large database, with a substantial number of payment items, may be degraded. The reason could be attributed to the index I_EVENT_PAYMENT_TRANS__ID on event_billing_payment_t table not being properly used.

The following SQL queries may be identified, that don't effectively use the index:

select distinct event_t.poid_DB, event_t.poid_ID0, event_t.poid_TYPE, event_t.poid_REV,
event_t.account_obj_DB, event_t.account_obj_ID0, event_t.account_obj_TYPE,
event_t.account_obj_REV, event_t.item_obj_DB, event_t.item_obj_ID0,
event_t.item_obj_TYPE,event_t.item_obj_REV from event_t ,event_billing_payment_t where
((event_t.poid_ID0 >= 35184372088832 and event_t.poid_ID0 < 1152921504606846976)) and
event_billing_payment_t.trans_id = :1and event_t.poid_id0 = event_billing_payment_t.obj_id0;


select distinct event_t.poid_DB, event_t.poid_ID0, event_t.poid_TYPE, event_t.poid_REV from
event_t ,event_billing_payment_t where
((event_t.poid_ID0 >= 35184372088832 and
event_t.poid_ID0 < 1152921504606846976))
 and event_billing_payment_t.trans_id =:1 and
event_t.poid_id0 = event_billing_payment_t.obj_id0;

 


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms