In Oracle 8i manual , it was suggested to use union for these kind of queries ( aka nvl(nvl(:empno,empno)) ...

Is this statement still holds good ?

<Quote from Oracle Manual>
Predicate Collapsing
Predicate collapsing occurs when a column predicate involves more than one bind variable. An expression of the form [col = DECODE (:b1,'',:b3,col)] is a an example of predicate collapsing. This implies that if the bind variable 1 is null, then the bind variable 3 should be used; otherwise, the expression will result in [ col = col]. This prevents the optimizer from utilizing the index on the "col" column due to the decode construct.

The following example demonstrates how predicate collapsing is used to collapse a name bind variable with the delivery_id bind variable in a single filter. As can be seen from the EXPLAIN PLAN, this results in a full table scan on the wsh_deliveries table because of the NVL() construct on the delivery_id column, as well as the DECODE() construct on the name column.

SELECT delivery_id, planned_departure_id, organization_id, status_code
FROM wsh_deliveries
WHERE delivery_id = NVL(:b1,delivery_id) AND name = DECODE(:b1,'',:b3, NAME)
ORDER BY UPPER(HRE.full_name)

PLAN:

Cost=2090 SELECT STATEMENT
Cost=2090 TABLE ACCESS FULL WSH_DELIVERIES


This query can be rewritten using a UNION to short-circuit one-side of the UNION based on the bind variable values. For example, if the delivery_id bind is supplied, only the first branch of the UNION is executed.

....
....
....
</Quote from Oracle Manual>

Thanks.Now it is working fine.

Hi Tom,

DECODE function in WHERE clause is not capable of handling sub-query with multiple rows. Suppose the following scenario:

VARIABLE ParaCCenter NUMBER

BEGIN
:paraccenter:=0;
END;
/

CREATE TABLE ccenters
(id NUMBER,
name VARCHAR2(20),
base number);

INSERT INTO ccenters VALUES(1,'NUST',null);
INSERT INTO ccenters VALUES(2,'SEECS',1)
INSERT INTO ccenters VALUES(3,'NBS',1)
commit;

SELECT * FROM ccenters
WHERE id IN DECODE(:ParaCCenter, 0, id,
(SELECT id FROM ccenters
START WITH base=:ParaCCenter
CONNECT BY PRIOR id = base
UNION
SELECT :ParaCCenter FROM dual
)
)
/
BEGIN
:paraCCenter:=1;
END;
/

SELECT * FROM ccenters
WHERE id IN DECODE(:ParaCCenter, 0, id,
(SELECT id FROM ccenters
START WITH base=:ParaCCenter
CONNECT BY PRIOR id = base
UNION
SELECT :ParaCCenter FROM dual))
/
The result is
(SELECT id FROM ccenters
*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row

How this query can be rewritten for the same result.

Thanks

where ( 
        (:paraCCenter = 0) and id is not null)
        OR 
        (:paraCCenter<> 0) and id in (select id from ccenters ....)
      )

Hi Tom,

very interesting discussion, however in some circumstances, COALESCE is preferable to NVL (due to short-circuiting), however, it seems the NVL OR expansion has not been implemented for COALESCE (this is 10.2.0.4, haven't tried it on 11g) :

SQL> explain plan for
  2   select * from emp where empno = nvl(:bind_variable,empno); 

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 851340519

----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |    15 |   555 |     3   (0)| 00:00:01 |
|   1 |  CONCATENATION                |              |       |       |            |          |
|*  2 |   FILTER                      |              |       |       |            |          |
|*  3 |    TABLE ACCESS FULL          | EMP          |    14 |   518 |     2   (0)| 00:00:01 |
|*  4 |   FILTER                      |              |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    37 |     1   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | SYS_C0066895 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:BIND_VARIABLE IS NULL)
   3 - filter("EMPNO" IS NOT NULL)
   4 - filter(:BIND_VARIABLE IS NOT NULL)
   6 - access("EMPNO"=:BIND_VARIABLE)

21 rows selected.

SQL> explain plan for
  2  select * from emp where empno = coalesce(:bind_variable,empno); 

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2872589290

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"=COALESCE(:BIND_VARIABLE,"EMPNO"))

13 rows selected.

Are there any plans to implement a similar optimisation using COALESCE? (apologies if there already is in 11g, can't try it here). 

Hi Tom,

Yep, understand that COALESCE can take a variable number of inputs (and the generic expansion would be unwieldy), but I could imagine the situation that the optimizer, if the COALESCE call has two (and only two) input parameters, could do a similar expansion. One for the future, maybe... ;-)

Thanks for the info.

Martin.

Tom, I have learned a lot from your site. Currently I'm facing small issue. I'm using subquery in case statement. Its working in 11g and nor working in 10g(10.2.0.4.0). Am doing some thing wrong? please find the sql.

SELECT
 TO_DATE(:p_cob_date,'DD-MON-YYYY') month,
  case when  WORKFLOW_TYPE IN (SELECT DOCUMENT_TYPE FROM DOCUMENT_TYPE_FILTERS WHERE FILTERS='INITIAL') then 'CAWV_INITIAL_CA'
        when  WORKFLOW_TYPE IN (SELECT DOCUMENT_TYPE FROM DOCUMENT_TYPE_FILTERS WHERE FILTERS='ANNUAL') then 'CAWV_ANNUAL_CA'           
  end kri,COUNT(WORKFLOW_ID) itms_total,
FROM ME_DATA
WHERE to_date(MONTH, 'DD-MON-YYYY') = to_date(:p_cob_date, 'DD-MON-YYYY')
    AND (WORKFLOW_TYPE IN (SELECT DOCUMENT_TYPE FROM DOCUMENT_TYPE_FILTERS WHERE FILTERS IN ('ANNUAL','INITIAL')
GROUP BY NVL(COMPLETED_BY, 'UNKNOWN'),
case when  WORKFLOW_TYPE IN (SELECT DOCUMENT_TYPE FROM DOCUMENT_TYPE_FILTERS WHERE FILTERS='INITIAL') then 'CAWV_INITIAL_CA'
     when  WORKFLOW_TYPE IN (SELECT DOCUMENT_TYPE FROM DOCUMENT_TYPE_FILTERS WHERE FILTERS='ANNUAL') then 'CAWV_ANNUAL_CA'          
  end;

Thanks & Regards,
Venkat.

ops$tkyte%ORA10GR2> l
  1  select dummy,
  2         case when dummy in (select 'Y' from dual) then 'it is y'
  3              when dummy in (select 'X' from dual) then 'it is x'
  4          end esac
  5*  from dual
ops$tkyte%ORA10GR2> /

D ESAC
- -------
X it is x

Hi,

We find out this is bug Oracle 10g . We are apply the fix.

ORA-00979 GROUP BY expression Error for SDE_ORA_PersistedStage_WorkforceEvent_SupervisorStatus_Full [ID 884298.1]

Modified 08-SEP-2009 Type PROBLEM Status MODERATED
Symptoms
When attempting to run 7.9.6 SDE_ORA_PersistedStage_WorkforceEvent_SupervisorStatus_Full, the following error occurs:

ORA-00979: not a GROUP BY expression
Database driver error...
Function Name : Execute
SQL Stmt : SELECT

The Oracle DB version for source and target instances is Oracle 10.2.0.4
Cause
It was found that this issue has been caused by Oracle Database issue, which is reported in Bug:
Bug 8564792: SR 3-934264767: WORFORCEENHANCEMENT: R12:ORA-00979: NOT A GROUP BY EXPRERROR

This OBI Apps Bug reference and Oracle DB Bug 5059447.
Solution
The patch specified in bug 5059447 fixed the issue.
Bug 5059447 - ORA-979 WITH CASE, SUBQUERY AND COMPLEX VIEW MERGING

After logging a ticket with Oracle DB Support to download and apply this Oracle DB Patch the issue was resolved.

Regards,
Venkat.