Wrong Query In DAT_BALANCEBATCH (Doc ID 565032.1)

Last updated on JUNE 22, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.3.0.0.1 to 7.3.0.0.1 [Release 7.3.0]
Information in this document applies to any platform.
This problem can occur on any platform.
***Checked on relevance on 26-Apr-2013***

Symptoms

When starting pipeline, DAT_BalanceBatch module executes the following query:

SELECT tag_bal_grp_t.poid_rev, tag_bal_grp_t.account_obj_id0,
tag_bal_grp_t.batch_cntr, tag_bal_grp_t.object_cache_type,
tag_bal_grp_sub_bals_t.obj_id0, tag_bal_grp_sub_bals_t.rec_id,
tag_bal_grp_sub_bals_t.rec_id2, tag_bal_grp_sub_bals_t.valid_from,
tag_bal_grp_sub_bals_t.valid_to, tag_bal_grp_sub_bals_t.current_bal,
tag_bal_grp_sub_bals_t.delayed_bal, tag_bal_grp_sub_bals_t.next_bal,
tag_bal_grp_sub_bals_t.contributor,
tag_bal_grp_sub_bals_t.rollover_data,
tag_bal_grp_bals_t.consumption_rule,
tag_bal_grp_sub_bals_t.grantor_obj_db,
tag_bal_grp_sub_bals_t.grantor_obj_id0,
tag_bal_grp_sub_bals_t.grantor_obj_type,
tag_bal_grp_sub_bals_t.valid_from_details,
tag_bal_grp_sub_bals_t.valid_to_details
FROM bal_grp_t tag_bal_grp_t,
bal_grp_sub_bals_t tag_bal_grp_sub_bals_t,
bal_grp_bals_t tag_bal_grp_bals_t   <------- problem cause
WHERE tag_bal_grp_sub_bals_t.obj_id0 >= :ph10
AND tag_bal_grp_sub_bals_t.obj_id0 <= :ph11
AND tag_bal_grp_sub_bals_t.obj_id0 = tag_bal_grp_t.poid_id0
AND tag_bal_grp_t.poid_type = '/balance_group'
AND ( tag_bal_grp_t.object_cache_type = 0
OR tag_bal_grp_t.object_cache_type = 2
)
AND NOT tag_bal_grp_t.poid_db IS NULL


This query is is estimated to end in 12 days for the case when BAL_GRP_BALS_T table has about 57 million records.

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