Alternatives for IN and NVL operators for classic report query

I have a high-cost query as something like this and it returns up 15000 records . we need all records based on my where clause :

 SELECT *
    FROM EMP E
        JOIN DEPT D on D.ID= E.DEPT_ID
 WHERE  E.ORGANIZATION= :PAGE_BIND_VARIABLE_ORG 
 AND :PAGE_BIND_VARIABLE_USER  in (nvl(USER_ID_OPERATOR,nvl(IS_ASSIGNED,IDREF_ID_1)) ,E.USER_ID)

the last line that includes IN operator is the reason of being high cost of this query.

Is there any alternatives or options to reduce the cost of this query ?

As I mentioned I need to show all records in a classic report but due to the cost , it takes a lot time to load on page .

Solution

First rule of tuning: ignore COST. If your query is performing badly, by definition Oracle's cost calculation is wrong (it always chooses the lowest-cost plan, what's wrong is that its costing is inaccurate). So COST serves no purpose for tuning. The only thing that matters is actual observed runtime. If your query or job is taking too long, that's something tunable. The fastest execution is what you want, regardless of what Oracle thinks its cost will be.

Without seeing your plan or execution stats, my guess is that your issue is with the main predicate:

AND :PAGE_BIND_VARIABLE_USER  in (nvl(USER_ID_OPERATOR,nvl(IS_ASSIGNED,IDREF_ID_1)) ,E.USER_ID)

These functions make it impossible for Oracle to leverage the binary-search capability of indexes. Assuming that this filter is meant to significantly narrow down your results, you will want to rewrite it so that there are no functions applied to the column. Also if possible avoid using OR or IN for important predicates like this. A proper data model should result in nearly all your queries using simple single-column equality predicates.

Here are some things to try:

Define each search mechanism needed separately using a UNION set:

SELECT *
  FROM EMP E
       JOIN DEPT D on D.ID= E.DEPT_ID
 WHERE E.ORGANIZATION= :PAGE_BIND_VARIABLE_ORG 
  AND USER_ID_OPERATOR = :PAGE_BIND_VARIABLE_USER
UNION
SELECT *
  FROM EMP E
       JOIN DEPT D on D.ID= E.DEPT_ID
 WHERE E.ORGANIZATION= :PAGE_BIND_VARIABLE_ORG 
  AND IS_ASSIGNED = :PAGE_BIND_VARIABLE_USER
  AND USER_ID_OPERATOR IS NULL
UNION
SELECT *
  FROM EMP E
       JOIN DEPT D on D.ID= E.DEPT_ID
 WHERE E.ORGANIZATION= :PAGE_BIND_VARIABLE_ORG 
  AND IDREF_ID_1 = :PAGE_BIND_VARIABLE_USER    
  AND USER_ID_OPERATOR IS NULL
  AND IS_ASSIGNED IS NULL
UNION
SELECT *
  FROM EMP E
       JOIN DEPT D on D.ID= E.DEPT_ID
 WHERE E.ORGANIZATION= :PAGE_BIND_VARIABLE_ORG 
  AND USER_ID = :PAGE_BIND_VARIABLE_USER

A more simply written SQL would work if the optimizer properly rewrites it using OR concatenation (basically the method above, but implicitly done for you). When this is desired it sometimes help to hint it with use_concat as Oracle doesn't always see the light by itself:

  SELECT /*+ use_concat */ *
    FROM EMP E
         JOIN DEPT D on D.ID= E.DEPT_ID
   WHERE E.ORGANIZATION= :PAGE_BIND_VARIABLE_ORG 
     AND (
            USER_ID_OPERATOR = :PAGE_BIND_VARIABLE_USER OR
            (USER_ID_OPERATOR IS NULL AND IS_ASSIGNED = :PAGE_BIND_VARIABLE_USER) OR
            (USER_ID_OPERATOR IS NULL AND IS_ASSIGNED IS NULL AND IDREF_ID_1 = :PAGE_BIND_VARIABLE_USER) OR
            USER_ID = :PAGE_BIND_VARIABLE_USER 
         )

Either of the above methods assume proper indexing. You'll want to index the following columns (4 indexes total):

create index emp_idx1 on emp(organization,user_id_operator);
create index emp_idx2 on emp(organization,is_assigned);
create index emp_idx3 on emp(organization,idref_id_1);
create index emp_idx4 on emp(organization,user_id);

Lastly, assume all three of those columns are in the same table (you should alias your columns to be explicit) you may also try creating a function-based index on the table:
```
create index emp_idx1 on emp(organization,nvl(USER_ID_OPERATOR,nvl(IS_ASSIGNED,IDREF_ID_1)))
```

Oracle might be able to use this for the first part of your IN operator, but the second part will need a separate index, and even here we'd be relying on OR expansion happening to take advantage of it:

    create index emp_idx2 on emp(organization,user_id)

And then try your original query, but hinted for OR expansion:

SELECT /*+ use_concat */ *
    FROM EMP E
        JOIN DEPT D on D.ID= E.DEPT_ID
 WHERE  E.ORGANIZATION= :PAGE_BIND_VARIABLE_ORG 
 AND :PAGE_BIND_VARIABLE_USER  in (nvl(USER_ID_OPERATOR,nvl(IS_ASSIGNED,IDREF_ID_1)) ,E.USER_ID)