Billing Sql Slowness Due to Use of Lower on BILL_NO Column (Doc ID 2816138.1)

Last updated on AUGUST 13, 2024

Applies to:

Goal

Scenario:

• Customer observed that below sql query fired during billing is slow due to the use of 'lower'
  
    select distinct billinfo_obj_DB, billinfo_obj_ID0, billinfo_obj_TYPE, billinfo_obj_REV, poid_DB, poid_ID0, poid_TYPE, poid_REV, ar_billinfo_obj_DB, ar_billinfo_obj_ID0, ar_billinfo_obj_TYPE, ar_billinfo_obj_REV from bill_t where lower( bill_t.bill_no ) = :1
  
  
• As per analysis, bill_no is always a number - so using "lower" is irrelevant here and will trigger full table scan instead of using the index for BILL_NO.

Question:

How to address this concern?
 

Solution

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