Thank you all in advanced.
APEX Version : 24.1.0
I create a dynamic LOV with additional column. The SQL statement has no performance issue (I've verified with SQL Plus). However, when I use this SQL statement in shared component LOV, it runs very slow.
My SQL is like this and index is associated with part_no column
select zim.part_no r
, zim.bu
, zim.model_name
from my_item_list zim
where zim.organization_id = 116
and zim.part_no like 'A%'
and zim.part_no NOT like '%M'
and zim.bu not in ('AGT BU','IDM BU','BRU BU')
and zim.item_description not like 'INACTIVE%'
But through the debug log, I found that APEX rewrites my SQL at run time as below. As you can see, APEX adds UPPER function to the where clause, causing the index to be ignored. Is there a way to stop APEX from rewriting the SQL to add the UPPER function?
select /*+qb_name(apex$40006_60)*/* from(select a.*,row_number()over(order by null)apx$rownum from(select i.*
from (select "R","MODEL_NAME","BU"
from(select /*+ qb_name(apex$inner) */d."R",d."MODEL_NAME",d."BU" from(select zim.part_no r
, zim.bu
, zim.model_name
from my_item_list zim
where zim.organization_id = 116
and zim.part_no like 'A%'
and zim.part_no NOT like '%M'
and zim.bu not in ('AGT BU','IDM BU','BRU BU')
and zim.item_description not like 'INACTIVE%'
)d
)i
)i where 1=1
and (((upper("R")like upper(:apex$f1))))
order by "R" asc nulls last
)a
)where apx$rownum<=:p$_max_rows