I need help on lexical parameter in cursor

Asked to Learn

Dear Experts,

Hope you are fine.

I'm using oracle DB 10.1.2 and forms 10.1.2.3. I want to write a cursor where  "few where condition" may exist or not exist.

Like bellow are the main cursor SQL.

SELECT TEMP_DATA
FROM TAB1
WHERE BDATE BETWEEN :B_DATE AND :E_DATE
AND DEPT_ID=:P1
AND DIVM_ID=:P2
ORDER BY BDATE;

Here i want, user may pass the value of  P1,P2 or any one or none. With more information. B_DATE and E_DATE must entered by user and

If user pass the both value of P1 and P2 then the above sql is ok for cursor. but if user don't pass the valu of P1 then cursor sql should like

SELECT TEMP_DATA
FROM TAB1
WHERE BDATE BETWEEN :B_DATE AND :E_DATE
AND DIVM_ID=:P2
ORDER BY BDATE;

How i do this in one cursor ?

Thanks for your time.

Ask2Learn

Sudhakar_B

Hi,

You could write the SQL as below...

SELECT TEMP_DATA  
FROM TAB1  
WHERE BDATE BETWEEN :B_DATE AND :E_DATE  
AND (DEPT_ID=:P1 OR :P1 is NULL)
AND (DIVM_ID=:P2 OR :P2 is NULL)
ORDER BY BDATE;  

This way if P1 is not entered by user then it will be null (same for P2).

Hope this helps.

vr,

Sudhakar

Sudhakar_B

Hi,

You could write the SQL as below...

SELECT TEMP_DATA  
FROM TAB1  
WHERE BDATE BETWEEN :B_DATE AND :E_DATE  
AND (DEPT_ID=:P1 OR :P1 is NULL)
AND (DIVM_ID=:P2 OR :P2 is NULL)
ORDER BY BDATE;  

This way if P1 is not entered by user then it will be null (same for P2).

Hope this helps.

vr,

Sudhakar

Etbin

Sometimes supervisors don't want to see or among predicates

SELECT TEMP_DATA 

  FROM TAB1 

WHERE BDATE BETWEEN :B_DATE AND :E_DATE 

   AND DEPT_ID = NVL(:P1,DEPT_ID)

   AND DIVM_ID = NVL(:P2,DIVM_ID)

ORDER BY BDATE

Regards

Etbin

Ramin Hashimzadeh

Etbin wrote:

Sometimes supervisors don't want to see or among predicates

SELECT TEMP_DATA  
  FROM TAB1  
 WHERE BDATE BETWEEN :B_DATE AND :E_DATE  
   AND DEPT_ID = NVL(:P1,DEPT_ID) 
   AND DIVM_ID = NVL(:P2,DIVM_ID)
 ORDER BY BDATE


Regards

Etbi

HI Etbin

In my own practice I found that

 :P1 is null or column = :P1
faster  than
 column = nvl(:P1,column)

i think  it is faster because short circuit is working ... But I would like to note that it is only my opinion and not documented

----

Ramin Hashimzade

Asked to Learn

Sudhakar_B wrote:

Hi,
You could write the SQL as below...

1. SELECT TEMP_DATA   
2. FROM TAB1   
3. WHERE BDATE BETWEEN :B_DATE AND :E_DATE   
4. AND (DEPT_ID=:P1 OR :P1 is NULL) 
5. AND (DIVM_ID=:P2 OR :P2 is NULL) 
6. ORDER BY BDATE;   

SELECT TEMP_DATA  
FROM TAB1  
WHERE BDATE BETWEEN :B_DATE AND :E_DATE  
AND (DEPT_ID=:P1 OR :P1 is NULL)
AND (DIVM_ID=:P2 OR :P2 is NULL)
ORDER BY BDATE;  

This way if P1 is not entered by user then it will be null (same for P2).

Hope this helps.
vr,
Sudhakar

Thanks Sudhakar. It's work like a charm.

Etbin

I'm making no claims. It just occurred to me that from time to time I have to deal with stereotypes like this (and the OP might too)

Regards

Etbin

Sudhakar_B

Thanks Etbin, Ramin,

We are all victims of profiling (I mean performance related) from time to time. One thing I have come to firmly believe, over years is that, Oracle Optimizer for the most part is way more intelligent than me ;-)

Got my 12c test DB few days back and tried this scenario out.

In my very limited testing it seems column = nvl(:P1,column) performs better than :P1 is null or column = :P1 (From explain plan and consistent gets perspective)

Moral of the story is as far as performance in Oracle is concerned, for your specific case test it out o find what works better!!!

Also,

With column = nvl(:P1,column) construct Oracle 12c builds and access path using TABLE ACCESS BY INDEX ROWID BATCHED operation. I guess this new in 12c. Need to read more about this.

SQL> select * from v$version;
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL>
SQL> set autotrace traceonly statistics explain;
SQL> set timing on;
SQL> set line 200;
SQL>
SQL> var e1 number;
SQL> -- E1 is null
SQL> select * from employees
  2  where employee_id = :e1 OR :e1 is null;


107 rows selected.


Elapsed: 00:00:00.00


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117


-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     6 |   414 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


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


   1 - filter(:E1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:E1))




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
      10121  bytes sent via SQL*Net to client
        621  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed


SQL>
SQL> select * from employees
  2  where employee_id  = nvl( :e1 ,employee_id);


107 rows selected.


Elapsed: 00:00:00.00


Execution Plan
----------------------------------------------------------
Plan hash value: 929977954


-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |   108 |  7452 |     0   (0)| 00:00:01 |
|   1 |  CONCATENATION                        |               |       |       |            |          |
|*  2 |   FILTER                              |               |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |   107 |  7383 |     0   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN                   | EMP_EMP_ID_PK |   107 |       |     0   (0)| 00:00:01 |
|*  5 |   FILTER                              |               |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID        | EMPLOYEES     |     1 |    69 |     0   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN                 | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------


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


   2 - filter(:E1 IS NULL)
   4 - filter("EMPLOYEE_ID" IS NOT NULL)
   5 - filter(:E1 IS NOT NULL)
   7 - access("EMPLOYEE_ID"=:E1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
      10121  bytes sent via SQL*Net to client
        621  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        107  rows processed


SQL>
SQL> -- E1 is 10 KNOWN to be not found;
SQL> begin :e1 := 10; end;
  2  /


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.00
SQL>
SQL> select * from employees
  2  where employee_id = :e1 OR :e1 is null;


no rows selected


Elapsed: 00:00:00.00


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117


-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     6 |   414 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


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


   1 - filter(:E1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:E1))




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1061  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL>
SQL> select * from employees
  2  where employee_id  = nvl( :e1 ,employee_id);


no rows selected


Elapsed: 00:00:00.00


Execution Plan
----------------------------------------------------------
Plan hash value: 929977954


-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |   108 |  7452 |     0   (0)| 00:00:01 |
|   1 |  CONCATENATION                        |               |       |       |            |          |
|*  2 |   FILTER                              |               |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |   107 |  7383 |     0   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN                   | EMP_EMP_ID_PK |   107 |       |     0   (0)| 00:00:01 |
|*  5 |   FILTER                              |               |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID        | EMPLOYEES     |     1 |    69 |     0   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN                 | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------


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


   2 - filter(:E1 IS NULL)
   4 - filter("EMPLOYEE_ID" IS NOT NULL)
   5 - filter(:E1 IS NOT NULL)
   7 - access("EMPLOYEE_ID"=:E1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
       1061  bytes sent via SQL*Net to client
        533  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL>
SQL> -- E1 is 107 KNOWN to be found;
SQL> begin :e1 := 107; end;
  2  /


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.00
SQL>
SQL> select * from employees
  2  where employee_id = :e1 OR :e1 is null;


Elapsed: 00:00:00.00


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117


-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     6 |   414 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


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


   1 - filter(:E1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:E1))




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1323  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>
SQL> select * from employees
  2  where employee_id  = nvl( :e1 ,employee_id);


Elapsed: 00:00:00.00


Execution Plan
----------------------------------------------------------
Plan hash value: 929977954


-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |   108 |  7452 |     0   (0)| 00:00:01 |
|   1 |  CONCATENATION                        |               |       |       |            |          |
|*  2 |   FILTER                              |               |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |   107 |  7383 |     0   (0)| 00:00:01 |
|*  4 |     INDEX FULL SCAN                   | EMP_EMP_ID_PK |   107 |       |     0   (0)| 00:00:01 |
|*  5 |   FILTER                              |               |       |       |            |          |
|   6 |    TABLE ACCESS BY INDEX ROWID        | EMPLOYEES     |     1 |    69 |     0   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN                 | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------


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


   2 - filter(:E1 IS NULL)
   4 - filter("EMPLOYEE_ID" IS NOT NULL)
   5 - filter(:E1 IS NOT NULL)
   7 - access("EMPLOYEE_ID"=:E1)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1323  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL>

vr,

Sudhakar