how to optimize sql performance when use function index?
because of other reasons,we need use the fuction upper(),not expected 'instr' ,and we use function index on upper();
sql like this:
create index INV.MTL_SYSTEM_ITEMS_B_N2 on INV.MTL_SYSTEM_ITEMS_B (UPPER(DESCRIPTION));
SELECT *
FROM (SELECT /*+ index(item,MTL_SYSTEM_ITEMS_B_N2)*/
inventory_item_id,
segment1 concatenatedsegments,
description,
nvl(comms_nl_trackable_flag, 'N') comms_nl_trackable_flag,
revision_qty_control_code,
organization_id,
item_type
FROM apps.mtl_system_items_b item
WHERE item.serv_req_enabled_code = 'E'
AND item.organization_id = 84
AND item.enabled_flag = 'Y'
) qrslt
WHERE ((qrslt.item_type = 'O' OR