" (+) = " Operator in Oracle SQL in Where Clause

What does a (+) sign mean in an Oracle SQL WHERE clause?

This is an Oracle-specific notation for an outer join. It means that it will include all rows from t1, and use NULLS in the t0 columns if there is no corresponding row in t0.

In standard SQL one would write:

SELECT t0.foo, t1.bar
  FROM FIRST_TABLE t0
 RIGHT OUTER JOIN SECOND_TABLE t1;

Oracle recommends not to use those joins anymore if your version supports ANSI joins (LEFT/RIGHT JOIN) :

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions […]

Oracle SQL: Meaning of (+)= in WHERE clause

The (+) identifies the table that is being outer joined to. The way I was taught, the (+) indicated the table that would have missing rows for which new NULL rows had to be added.

If you look at the alternate left outer join syntaxes that various databases supported before LEFT OUTER JOIN became part of the ANSI standard, the proprietary operator was generally applied to the table that was "missing" rows. DB2 also supports the (+) operator for outer joins in the same way that Oracle does.

Answer: Old Style Oracle Outer Join Syntax - Why locate the (+) on the right side of the equals sign in a Left Outer join?

Oracle WHERE clause ( AND , OR operators)

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?

What is the purpose of (+) operator in a where clause, other than outer joins, in Oracle SQL?

It's the same as:

select *
from table_a a
   left outer join table_b b 
     on a.id = b.id 
    and b.type_cd = 'DOLLR'
    and b.seq_nb = 1

Sometimes also referred to as a "filtered outer join".

It is equivalent to an outer join with a derived table:

select *
from table_a a
   left outer join (
     select * 
     from table_b
     where b.type_cd = 'DOLLR'
       and b.seq_nb = 1
   ) b on a.id = b.id

SQL Alternative for 'OR' in where clause when using outer join

Since you have to use the old-style outer join syntax, here's one way (simplified, since you didn't supply us with sample data and/or table creation scripts):

with assignments as (select 1 assignment_id, 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 2 assignment_id, 1 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('04/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 assignment_id, 1 person_id, to_date('06/08/2015', 'dd/mm/yyyy') start_date, to_date('10/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 4 assignment_id, 2 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual),
       employees as (select 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual)
select *
from   assignments dah,
       employees emp
where  dah.person_id = emp.person_id (+)
and    dah.start_date <= emp.end_date (+)
and    dah.end_date >= emp.start_date (+);

ASSIGNMENT_ID  PERSON_ID START_DATE END_DATE   PERSON_ID_1 START_DATE_1 END_DATE_1
------------- ---------- ---------- ---------- ----------- ------------ ----------
            2          1 02/08/2015 04/08/2015           1 01/08/2015   03/08/2015
            1          1 01/08/2015 03/08/2015           1 01/08/2015   03/08/2015
            3          1 06/08/2015 10/08/2015                                    
            4          2 02/08/2015 03/08/2015

Are you sure you got your outer joins the right way round? Are you sure you're not actually after the following instead?:

with assignments as (select 1 assignment_id, 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 2 assignment_id, 1 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('04/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 assignment_id, 1 person_id, to_date('06/08/2015', 'dd/mm/yyyy') start_date, to_date('10/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 4 assignment_id, 2 person_id, to_date('02/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual),
       employees as (select 1 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual union all
                     select 3 person_id, to_date('01/08/2015', 'dd/mm/yyyy') start_date, to_date('03/08/2015', 'dd/mm/yyyy') end_date from dual)
select *
from   assignments dah,
       employees emp
where  dah.person_id (+) = emp.person_id
and    dah.start_date (+) <= emp.end_date
and    dah.end_date (+) >= emp.start_date;

ASSIGNMENT_ID  PERSON_ID START_DATE END_DATE   PERSON_ID_1 START_DATE_1 END_DATE_1
------------- ---------- ---------- ---------- ----------- ------------ ----------
            1          1 01/08/2015 03/08/2015           1 01/08/2015   03/08/2015
            2          1 02/08/2015 04/08/2015           1 01/08/2015   03/08/2015
                                                         3 01/08/2015   03/08/2015

Oracle: What does `(+)` do in a WHERE clause?

Depending on which side of the "=" the "(+) is on, it denotes a LEFT OUTER or a RIGHT OUTER join (in this case, it's a left outer join). It's old Oracle syntax that is sometimes preferred by people who learned it first, since they like that it makes their code shorter.

Best not to use it though, for readability's sake.

Function in Oracle SQL where clause

A function returns a value.

In the where clause you need to compare that return value with something, e.g.

SELECT * 
FROM employees emp 
WHERE emp.loc = 'US' 
AND usr.fnctn('emp_sal', :sal1, :sal2) = 42;