Oracle WHERE clause ( AND , OR operators)

Question

Friends while executing where clause in Oracle SQL suppose I have ``` UPDATE schema1.TBL_SCHEMA1_PROCESS_FEED F SET F.TBL_SCHEMA1_PROCESS_LINE_ID = V_LINE_ID, F.TBL_SCHEMA1_PROCESS_LINE_TYPE_ID = V_LINE_TYPE_ID, F.TBL_SCHEMA1_PROCESS_LINE_SUB_TYPE_ID = V_SUB_TYPE_ID, WHERE F.CURR_DATE = V_CURR_DATE AND F.NEXT_DATE = V_NEXT_BUSINESS_DATE OR F.NEXT_DATE IS NULL; ``` How this code can be optimized for the condition ``` F.NEXT_DATE = V_NEXT_BUSINESS_DATE OR F.NEXT_DATE IS NULL ```

Accepted Answer

Is that your actual `where` clause? Do you mean it to be:

```
WHERE F.CURR_DATE = V_CURR_DATE  
  AND ( F.NEXT_DATE = V_NEXT_BUSINESS_DATE 
        OR F.NEXT_DATE IS NULL )
```

If so then you need an index, unique if possible, on `curr_date`.

If you're not satisfied that this provides a large enough improvement in the execution time then think about extending it to `curr_date, next_date`. Don't create a larger index if you don't need to.

You might also consider chaning your conditions slightly, though I doubt it would make much, if any, difference.

```
WHERE F.CURR_DATE = V_CURR_DATE  
  AND NVL(F.NEXT_DATE, V_NEXT_BUSINESS_DATE) = V_NEXT_BUSINESS_DATE 
```

The best possible option is, to update using the `rowid`. Without a lot more information it's impossible to know if you're in a situation where this might be possible but as the `rowid` is a unique address in the table it always is quicker than indexes, when updating a single row. If you're collecting data from this table then populating your variables before writing back to the table then this would be possible.

Are those your actual schema and table names... if they are then why not think about chosing something more descriptive?

Oracle WHERE clause ( AND , OR operators)

Solution

Problem