My Oracle Support Banner

Join on FT Parent ID and Adjustment Type Code Slows Down Billing (Doc ID 2465593.1)

Last updated on MARCH 26, 2019

Applies to:

Oracle Financial Services Revenue Management and Billing - Version 2.6.0.1.0 and later
Information in this document applies to any platform.

Symptoms

On : 2.6.0.1.0 version, BI - Billing

ACTUAL BEHAVIOR
---------------

While running billing, we identified a slow query that is executed by the base code ('com.splwg.ccb.domain.financial.common.ProgramCIPFBLCN_Impl', SQL name 'U_LNKOIFTB').

Found that when we change the following join in EXISTS statement:

AND EXISTS (SELECT 'Y'
  FROM CI_ADJ_TYPE AT
  WHERE AT.ADJ_TYPE_CD = FT.PARENT_ID
  AND AT.INFLNC_BILL_BAL = 'N')

into the following literal comparison instead:

AND EXISTS (SELECT 'Y'
  FROM CI_ADJ_TYPE AT
  WHERE AT.ADJ_TYPE_CD = CAST(FT.PARENT_ID AS CHAR(8))
  AND AT.INFLNC_BILL_BAL = 'N')

the execution time reduces considerably (please consider that CI_FT.PARENT_ID is of type CHAR(12), whereas CI_ADJ_TYPE.ADJ_TYPE_CD is CHAR(8)).



Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.