ORDERED Hint in Complex Searches (Doc ID 408049.1)

Last updated on SEPTEMBER 19, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 6.2.0.0.0 and later
Oracle Solaris on SPARC (32-bit)
***Checked for relevance on 23-Dec-2010***
***Checked for relevance on 30-Oct-2013***
***Checked for relevance on 02-Apr-2015***


Symptoms

A SELECT with the following business logic is needed:

1) select all /event/billing/charge/tmobile where process_status = TRANSFER_PENDING

2) retrieve the corresponding /event/billing/charge and keep only those where result = PASS or RETRY


The SELECT should be embedded within a Multi-Threaded Application (MTA) framework, so a single complex search joining multiple tables is needed:

1) a nested loop driven by TMO_EB_CHRGE_TMO_INFO_T and using a purpose built index on process_status I_TMO_EB_CHRGE_INFO_T_PROC_STA

2) within the nested loop, the other tables have to be first EVENT_T and second EVENT_BILLING_CHARGE_T to match the business logic, using default index on poid_id0

To this aim, the following select statement may be used, which has been verified as working fine and fast enough on real production data (analysing the EXPLAIN PLAN, it runs in a few second over several hundred thousands of events):

SELECT /*+ ORDERED USE_NL INDEX(TMO_EB_CHRGE_TMO_INFO_T I_TMO_EB_CHRGE_INFO_T_PROC_STA) */
EVENT_T.poid_DB, EVENT_T.poid_ID0, EVENT_T.poid_TYPE, EVENT_T.poid_REV,
EVENT_T.mod_t, EVENT_T.account_obj_DB, EVENT_T.account_obj_ID0,
EVENT_T.account_obj_TYPE, EVENT_T.account_obj_REV
FROM TMO_EB_CHRGE_TMO_INFO_T, EVENT_T,EVENT_BILLING_CHARGE_T
WHERE ( TMO_EB_CHRGE_TMO_INFO_T.process_status = :1 ) AND
( poid_id0 > 0 ) AND ( EVENT_BILLING_CHARGE_T.result = :2 OR EVENT_BILLING_CHARGE_T.result = :3 )
AND EVENT_T.poid_id0 = TMO_EB_CHRGE_TMO_INFO_T.obj_id0 AND
EVENT_T.poid_id0 = EVENT_BILLING_CHARGE_T.obj_id0 ORDER BY poid_id0

Then, the following search template was used:

0 PIN_FLD_TEMPLATE STR [0] "select /*+ ORDERED USE_NL INDEX(TMO_EB_CHRGE_TMO_INFO_T I_TMO_EB_CHRGE_INFO_T_PROC_STA) */ X from TMO_EB_CHRGE_TMO_INFO_T,EVENT_T,EVENT_BILLING_CHARGE_T where ( F1 = V1 ) and ( poid_id0 > 0 ) and ( F2 = V2 or F3 = V3 ) ORDER by poid_id0"

Unfortunately, Infranet generates a SELECT statement (as seen from Oracle traces), where the only difference is the order of tables event_t and tmo_eb_chrge_tmo_info in the FROM clause, but it's enough to fool the ORDERED hint:

SELECT /*+ORDERED USE_NL INDEX(TMO_EB_CHRGE_TMO_INFO_T I_TMO_EB_CHRGE_INFO_T_PROC_STA) */
event_t.poid_DB,event_t.poid_ID0, event_t.poid_TYPE, event_t.poid_REV,
event_t.mod_t,event_t.account_obj_DB, event_t.account_obj_ID0,
event_t.account_obj_TYPE, event_t.account_obj_REV
FROM event_t,tmo_eb_chrge_tmo_info_t,event_billing_charge_t
WHERE ( tmo_eb_chrge_tmo_info_t.process_status = :1 ) and
( poid_id0 > 0 ) and ( event_billing_charge_t.result = :2 or event_billing_charge_t.result = :3 )
and event_t.poid_id0 = tmo_eb_chrge_tmo_info_t.obj_id0 and
event_t.poid_id0 = event_billing_charge_t.obj_id0 order by poid_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