What is the diff between Exist and In Operator?

The main difference would be performance.

If the conditions are correct the results would be the same, but the queries would be executed in a different way by the database engine. When the appropriate index exists, the EXISTS approach usually performs better. You could use the autotrace option in SQL*Plus or SQL Command Line to see the execution plan for your queries.

In my point of view, EXISTS is better because it requires to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the sub query are very small. Usually to run the query that returns the smaller set of results first.


SQL Statements that use the SQL EXIST Condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table.

The EXISTS operator tests for existence of rows in the result set of the subquery.

. If a subquery row value is found :

- The condition is flagged TRUE

- The search does not continue in the inner query.


. If a subquery row  value is not found :

- The condition is flagged FALSE

- The search continues in the inner query.


-EXISTS operator is frequently used in Correlated subqueries to test whether a value retrieved by the outer query exists in the result set of the values retrieved
by the inner query.


If the subquery returns at least one row , the operator returns TRUE. If the value does not exits , it returns FALSE.

Accordingly, NOT EXISTS tests whether a value retrived by the outer query is not part of the results set of the values retrived by the inner query.


Q: Find employees who have at least one person reporting to them.

SELECT employee_id,last_name, job_id, department_id

FROM employees outer

WHERE EXISTS (SELECT 'X'
               FROM employees
              WHERE manager_id= outer.employee_id);


The EXISTS operator ensures that the search in the inner query doesn't continue when at least one match is found for the manager and employee_id condition:

WHERE manager_id =outer.employee_id;


Note that the inner SELECT query doesn't need to return a specific value , so a constant can be selected.
From a performance stand point , it is faster to select a constant than a column.

NOTE : Having employee_id in the SELECT clause of the inner query causes a table scan for that column.

Replacing it with a literal 'X' or any constant improves performance.

This is more efficient than using in operator.

A IN construct can be used as an alternative for a EXISTS operator , as shown below.

SELECT employee_id,last_name, job_id, department_id
FROM employees outer
WHERE employee_id  IN  (SELECT manager_id
                        FROM employees
                       WHERE manager_id IS NOT NULL);
             

WHERE CURRENT OF STATEMENT

WHERE CURRENT OF & FOR UPDATE

The WHERE CURRENT OF clause is used in some UPDATE and DELETE statements.

The WHERE CURRENT OF clause in an UPDATE or DELETE statement states that the most recent row fetched from the table should be updated or deleted. We must declare the cursor with the FOR UPDATE clause to use this feature. Inside a cursor loop, WHERE CURRENT OF allows the current row to be directly updated.

When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

Oracle provides the FOR UPDATE clause in SQL syntax to allow the developer to lock a set of Oracle rows for the duration of a transaction.

The syntax of using the WHERE CURRENT OF clause in UPDATE and DELETE statements follows:

WHERE [CURRENT OF cursor_name | search_condition]

The following example opens a cursor for employees and updates the commission, if there is no commission assigned based on the salary level.

EXAMPLE

UPDATING USING THE WHERE CURRENT OF STATEMENT.

DECLARE
CURSOR C1 IS SELECT empno,ename, sal
from emp
where comm IS NULL
FOR UPDATE OF comm;

var_comm NUMBER (10,2);
BEGIN
FOR rec in C1 LOOP 
  IF rec.sal < 500 THEN
      var_comm :=rec.sal*0.25;
    ELSIF rec.sal < 1500 THEN
      var_comm :=rec.sal*0.25;
    ELSIF rec.sal < 3000 THEN
      var_comm :=rec.sal*0.15;
    ELSE
   var_comm :=  rec.sal*0.12;
  END IF;
  
 UPDATE emp 
 SET comm =var_comm
 WHERE CURRENT OF C1;
 END LOOP;
 END;


SYNTAX

The syntax for the WHERE CURRENT OF statement in Oracle/PLSQL is either:

UPDATE table_name
  SET set_clause
  WHERE CURRENT OF cursor_name;

OR

DELETE FROM table_name
WHERE CURRENT OF cursor_name;


NOTE

The WHERE CURRENT OF statement allows you to update or delete the record that was last fetched by the cursor.

DELETING USING THE WHERE CURRENT OF STATEMENT

--DROP TABLE EMP;
--CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;

DECLARE
   v_empno   emp.empno%TYPE;
   v_ename   emp.ename%TYPE;
   v_sal     emp.sal%TYPE;

   CURSOR c1
   IS
          SELECT empno, ename, sal
            FROM emp
           WHERE comm IS NULL
          FOR UPDATE OF comm;

BEGIN
  OPEN C1;

   LOOP
      FETCH C1  INTO v_empno, v_ename, v_sal;

      EXIT WHEN c1%NOTFOUND;

      DELETE FROM emp
      WHERE CURRENT OF C1;

      --UPDATE emp
      --SET comm = 9999
         END LOOP;
    COMMIT;
   CLOSE C1;
END;

FOR UPDATE OF CLAUSE

Learn how to use the Oracle/PLSQL SELECT FOR UPDATE statement with syntax and examples.

DESCRIPTION

The SELECT FOR UPDATE statement allows you to lock the records in the cursor result set. You are not required to make changes to the records in order to use this statement. The record locks are released when the next commit or rollback statement is issued.

SYNTAX

The syntax for the SELECT FOR UPDATE statement in Oracle/PLSQL is:

CURSOR cursor_name

IS

   select_statement

   FOR UPDATE [OF column_list] [NOWAIT];

PARAMETERS OR ARGUMENTS

cursor_name is the name of the cursor.

select_statement is a SELECT statement that will populate your cursor result set.

column_list are the columns in the cursor result set that you wish to update.

NOWAIT is optional. The cursor does not wait for resources.

EXAMPLE

For example, you could use the SELECT FOR UPDATE statement as follows:

CURSOR c1

IS

  SELECT course_number, instructor

  FROM courses_tbl

  FOR UPDATE OF instructor;

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

Finding orphan sessions in oracle

It happened  when one of the  job get killed after reaching its max time, but it's thread processes are running, so we need a monitoring sql to check such process who does not have any parent. 

When joining v$session to v$process  the v$process.spid column should have a value (node process ID).  If this is null, that is generally an orphaned session.

--orphan_session.sql

col username heading 'USERNAME' format a10
col sessions heading 'SESSIONS'
col sid heading 'SID' format 999
col status heading 'STATUS' format a10
col machine format a10 head 'MACHINE' 
col client_program format a20 head 'CLIENT PROGRAM'
col server_program format a20 head 'SERVER PROGRAM'
col spid format a5 head 'SRVR|PID'
col serial# format 99999 head 'SERIAL#' 
col client_process format 999999 head 'CLIENT|PID'
col osuser format a7
col logon_time format a17 head 'LOGON TIME'
col idle_time format a11 head 'IDLE TIME'
col ppid format 999 head 'PID' 

set recsep off term on pause off verify off echo off
set line 200
set trimspool on

clear break
break on username skip 1

select
        s.username,
        s.sid,
        s.serial#,
        p.pid ppid,
        s.status,
        s.machine,
        s.osuser,
        substr(s.program,1,20) client_program,
        s.process client_process,
        substr(p.program,1,20) server_program,
        p.spid spid,
        to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,
        -- idle time
        -- days added to hours
        --( trunc(LAST_CALL_ET/86400) * 24 ) || ':'  ||
        -- days separately
        substr('0'||trunc(LAST_CALL_ET/86400),-2,2)  || ':'  ||
        -- hours
        substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
        -- minutes
        substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) ||
':' ||
        --seconds
        substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2)
idle_time 
from v$session s, v$process p
where s.username is not null
        -- use outer join to show sniped sessions in
        -- v$session that don't have an OS process
        and p.addr(+) = s.paddr
        -- uncomment to see only your own session 
        --and userenv('SESSIONID') = s.audsid
order by username, sid
/

Cheers
Rajani

TRIGGERS IN PL/SQL

-A trigger is a PL/SQL block  or a procedure associated with a table, view, schema or the database.
-Executes implicitly whenever a particular event occurs
There are basically two types of trigger.
a) Application Trigger
b) Database Trigger

Application Trigger :
Fires whenever an event occurs with a particular application.

Database Trigger :
Fires whenever a data event (such as DML ) or system event (such as logon or shutdown ) occurs on a schema or database.

- Database Triggers can be defined on tables and  on views. If a DML operation is issued on a view, the INSTEAD OF trigger 
defines what action to take place. If these actions include DML operations on tables, then any trigger on the base tables are fired.

When to create Triggers:
- To perform related actions
-Centralize global operations

When not to create Triggers:
- Where functionality is already built into the oracle server.
-That duplicates the other triggers
-Create stored procedures and invoke them in a trigger, if the pl/sql is very lengthy.
-The excessive  use of triggers can result in complex  interdependencies, which may be difficult to maintain in large applications.

Parts of a TRIGGER:

a) Trigger timing
- For table : BEFORE,AFTER
-For View  : INSTEAD OF 

b) Triggering Event : INSERT, UPDATE OR DELETE

c) Table Name  : On table, VIEW

d) Trigger Type : ROW or STATEMENT

e) WHEN clause :  Restricting condition

f) Trigger Body  : complete PL/SQL block

DML TRIGGER COMPONENTS :

TRIGGER TIMING : WHEN THE TRIGGER SHOULD FIRE ?

A) BEFORE : Execute the trigger body before the triggering DML event on the table.

         -Used in most of the rollback cases.

         -To initialize global variables or flags and to validate complex business rules.

           

B) AFTER  : Execute the trigger body after the triggering DML event on the table.

          -To complete the triggering statement before executing the triggering action.

          -To perform different actions on  the same triggering statement if a BEFORE trigger        already present.

C) INSTEAD OF : Execute the trigger body instead of the triggering statement .

            This is used for views that are not otherwise modifiable.

            -Used to provide a transparent way of modifying views that can't be modified                    directly through   SQL DML statements ( e.g. complex views)

              - You can write INSERT, UPDATE and  DELETE  statements against the  view. 

          -The INSTEAD OF trigger works invisibly in the background performing the                       action coded in the   trigger body directly on the underlying tables. 

Truncate function

--Truncate function will truncate the value and wont affect the integer part of value.

Cheers
Rajani

What is the difference between ROUND and TRUNC function in SQL?

--Round function will increase the value by 1 if decimal value is greater than or equal to 5 else it will return same integer value without decimal.

--Truncate function will truncate the value and wont affect the integer part of value.