Oracle EBS expression on indexed column prevents use of index

Question

Oracle database 11.1.0.7 and EBS Applications 11.5.10.

I have a poorly performing custom view, which includes Oracle EBS base view APPS.RA_CUSTOMER_TRX. This base view is against base table RA_CUSTOMER_TRX_ALL as shown below.

FROM RA_CUSTOMER_TRX_ALL
     WHERE NVL (
               ORG_ID,
               NVL (
                   TO_NUMBER (
                       DECODE (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 1),
                               ' ', NULL,
                               SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10))),
                   -99)) =
           NVL (
               TO_NUMBER (
                   DECODE (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 1),
                           ' ', NULL,
                           SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10))),
               -99);

When I run the Oracle Tuning Advisor from Toad against the sql of my custom view, it tells me

1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
  The predicate NVL("RA_CUSTOMER_TRX_ALL"."ORG_ID",NVL(TO_NUMBER(DECODE(SUBSTRB
  (USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),
  (-99)))=NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),'
  ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),(-99)) used at line ID 53 of
  the execution plan contains an expression on indexed column "ORG_ID". This 
  expression prevents the optimizer from efficiently using indices on table
  "AR"."RA_CUSTOMER_TRX_ALL".

  Recommendation
  --------------
  - Rewrite the predicate into an equivalent form to take advantage of
    indices. Alternatively, create a function-based index on the expression.

  Rationale
  ---------
    The optimizer is unable to use an index if the predicate is an inequality
    condition or if there is an expression or an implicit data type conversion
    on the indexed column.

So my options are either 1) to rewrite the custom view to go against the base table instead of the base view, or 2) create a function-based index. I tried creating the function-based index, but it tells me "only pure functions can be indexed."

I would like to solve this by creating the index because I believe other similar views are out there that will benefit from this. My DBA skills are not high enough for this, so hoping someone has come across this before and can advise.

Thank you.

oh man that is hard then because you have to get the org id from the session. is there any other filter criteria that can be indexed? or is there another table you can use to drive the query — Peter M, Apr 26 '18 at 15:52
The custom view is for a particular org/country, so I can hard code it no problem in the custom view. This would be the plan if I was taking option 1) and rewriting the custom view to go against the base table. — Joe, Apr 26 '18 at 15:57
that sounds like a better plan to me (use the ra_customer_trx_all and hard code the org id). The function based index can't be created because the userenv('client_id' ) call is reading a value that is set after the session is established -- that is how oracle does the row level security for the multiple orgs — Peter M, Apr 26 '18 at 17:57
In many cases, your Oracle eBS implementation is going to have just a handful of operating units. Even if you have as many as 10, an index on `ORG_ID` isn't going to be very useful for speeding up queries. On most systems, it is faster to full-scan the whole table than it is to read 10% of the rows via index range scan. In other words, consider the possibility that what you are trying to do isn't helpful. — Matthew McPeak, Apr 26 '18 at 19:09

score 0 · Answer 1 · answered Apr 28 '18 at 23:38

NVL does not short-circuit, so your entire expression is always evaluated in its entirety, including three calls to USERENV. I'm also not clear what the format of your CLIENT_INFO string is going to look like. I'm assuming it's a number or blank. I haven't entirely worked this out, but could you change the where clause to something more like:

WHERE ( org_id IS NOT NULL AND USERENV ('CLIENT_INFO') IS NOT NULL AND
        org_id = TO_NUMBER( USERENV ('CLIENT_INFO')))
   OR ( org_id IS NOT NULL AND USERENV ('CLIENT_INFO') IS NULL AND org_id = -99 )
   OR ( org_id IS NULL AND USERENV ('CLIENT_INFO') IS NULL )

You could also try caching the call to USERENV with a common table expression, but I'm not sure that will help:

WITH clientinfo AS (
    SELECT /*+ MATERIALIZE */ USERENV( 'CLIENT_INFO' ) AS client_info FROM dual
)
SELECT ...
  FROM ...
 INNER JOIN clientinfo ci
 WHERE ci.client_info = ...

Oracle EBS expression on indexed column prevents use of index

1 Answers1