SQL Performance (MOSC)

MOSC Banner

how to optimize sql performance when use function index?

edited Jan 27, 2014 1:33AM in SQL Performance (MOSC) 7 commentsAnswered
hi ,

   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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center