There is this stored procedure that builds a dynamic query string and then execute it. The sp works *fine* in development and testing environment, but the DBA of the client company has informed that this query is hitting really hard to the database in production. The IT area has asked us to tune up the query. So far so good, we've moved almost all this sp from building the query string dynamically into a single big query that performs really fast (compared to the old query).

We have found (among other things) that the sp built the where clause of the query string by evaluating if a parameter has a default value or a real value i.e.

```
IF P_WORKFLOWSTATUS <> 0 THEN
    L_SQL := TRIM(L_SQL) || ' AND WORKFLOW.STATUS = ' || TO_CHAR(P_WORKFLOWSTATUS);
END IF;

```

So we optimized this behavior to

```
WHERE
    ...
    AND (WORKFLOW.STATUS = P_WORKFLOWSTATUS OR P_WORKFLOWSTATUS = 0)

```

This kind of change has improved the query that affected numeric columns, but we have found a problem with a VARCHAR2 parameter and column. The current behavior is

```
--CLIENT.CODE is a VARCHAR2(14) column and there is an unique index for this column.
--The data stored in this field is like 'N0002077123', 'E0006015987' and similar
IF NVL(P_CLIENT_CODE, '') <> '' THEN
    L_SQL := TRIM(L_SQL) || ' AND CLIENT.CODE = ''' || P_CLIENT_CODE || '''';
END IF;

```

We tried to change this to our *optimized* version of the query by doing

```
WHERE
    ...
    AND (CLIENT.CODE = P_CLIENT_CODE OR NVL(P_CLIENT_CODE, '') = '')

```

but this change made the query lost performance. Is there a way to optimize this part of the query or should we turn our big query into a dynamic query (again) just to evaluate if this `VARCHAR2` parameter should be added or not into the where clause?

Thanks in advance.