Customer Center Inefficient Query For Exact Account Number Search (Doc ID 1366231.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version: 7.4.0.0.0 and later   [Release: 7.4.0 and later ]
Information in this document applies to any platform.

Goal

Customer Center is using the following query to Search for an account given an exact Account Number.

This query does not use the index on the ACCOUNT_NO field because the lower() function is being used - and on a large database the query is very slow.

op_global_step_search input flist
# number of field entries allocated 20, used 5
0 PIN_FLD_POID POID [0] 0.0.0.1 /search -1 0
0 PIN_FLD_RESULTS ARRAY [10] allocated 20, used 4
1 PIN_FLD_ACCOUNT_NO STR [0] NULL str ptr
1 PIN_FLD_POID POID [0] NULL poid pointer
1 PIN_FLD_BRAND_OBJ POID [0] NULL poid pointer
1 PIN_FLD_NAMEINFO ARRAY [*] allocated 20, used 3
2 PIN_FLD_LAST_NAME STR [0] NULL str ptr
2 PIN_FLD_FIRST_NAME STR [0] NULL str ptr
2 PIN_FLD_COMPANY STR [0] NULL str ptr
0 PIN_FLD_ARGS ARRAY [1] allocated 20, used 1
1 PIN_FLD_ACCOUNT_NO STR [0] "ban000000000009"
0 PIN_FLD_TEMPLATE STR [0] "select X from /account 1 where lower( 1.F1 ) = V1 "
0 PIN_FLD_FLAGS INT [0] 256

the "where lower( 1.F1 ) = V1" is the cause of the problem.


Solution

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