Pages

Saturday 8 February 2014

How to check Index usage specially when application is third party.


Indexes are optional object in database but they exist in each database, small or big.
Indexes only purpose is that they can fetch the data faster using the rowid or Fast Full scan method.

Fast Full Scan: when all the required columns of the query are present in the index.

To tune a query, people think about to create an Index but that's not right approach , just to tune a query do not create a index instead think of other tuning techniques

To get something you have to pay something apply to indexes , DML operation become slower as Index stores the row id along with value of the Indexed column data , Whenever a DML happens , Oracle Needs to update the Index Structure accordingly , so time consuming and Extra storage.

In this section we will not discuss when to create an Index, but we will focus on that the index which we created for our Apps are used by our application Queries or not?

There is no short cut as such to check whether your indexes are used or not, especially if your application is third party application. We have few methods by which we can conclude that our indexes are used or not and which queries & programs are using them.

 1. Index Monitoring

Easiest method is to enable the index monitoring for the all the indexes.

Syntax:
Alter Index |Index name| monitoring usage;

Data dictionary table: V$OBJECT_USAGE

Give the information that Index was used or not during that period in which Monitoring is Enabled.

 V$OBJECT_USAGE
Column Name
Content
INDEX Name
Self Explanatory
Monitoring
Yes or No
Used
Yes or No
Start Monitoring
Start date
End Monitoring
End Date

Drawback of the method is it will not give you details which query using the index and how many time this index was used. It gives only info that Index was used but no information like once or thousand times.

To disable the Monitoring syntax.

Alter Index |Index name| no monitoring usage;


2. v$sql_plan_statistics_all Table


Querying the v$sql_plan_statistics_all table from where you can easily make decisions about the Index usage.

Few Important Columns of the Table.

Column Name
Content
OPERATION
 Index, select Statement , Table Access full etc.
OPTION
Yes or No
OBJECT_OWNER
Yes or No
OBJECT_NAME
Start date
COST
Cost
CPU COST
Self Explanatory
IO COST
Input output cost
ACCESS PREDICATES
Access conditions
FILTER PREDICATES
Filter Conditions
PROJECTIONS
Columns of the sql
EXECUTIONS
No of Executions
SQLID
Id of the SQL statement


Query to find out the number of time Index used for a Particular query.
select count(*) "#SQL", sum(executions) "#EXE",
object_name ,options
from v$sql_plan_statistics_all
where operation = 'INDEX' and
object_owner = 'Schema Name'
and object_name in (select index_name from all_indexes where table_name IN ('xx'))
group by object_name,options
order by object_name,options

Query to find out the Access predicates and Filter predicates which is important.

select * from v$sql where sql_id in (select sql_id  from v$sql_plan_statistics_all where operation = 'INDEX' and object_owner = 'SCHEMA'     and object_name ='INDEX_NAME' and access_predicates like '%xx%' )

if you want to find the exact query in order use  V$SQLTEXT_WITH_NEWLINES instead of V$SQL.
This View gives you the information like AWR report by which you can make decision about the index usage.

3. Querying the History data 


DBA_HIST_SQL_TEXT

DBA_HIST_SNAPSHOT 

DBA_HIST_SQLSTAT

DBA_HIST_SQL_PLAN

Query to find the SQLTEXT using the Index from the history data.

select * from dba_hist_sqltext where sql_id in

 (select pl.sql_id from
 dba_hist_snapshot sn , dba_hist_sqlstat sq ,  dba_hist_sql_plan pl      
  
where SN.DBID=SQ.DBID and 
SN.SNAP_ID=SQ.SNAP_ID and
SN.INSTANCE_NUMBER=SQ.INSTANCE_NUMBER  
and SQ.SQL_ID=PL.SQL_ID and PL.OBJECT_NAME ='INDEX_NAME');


Now you got to know that a particular index was not in use and not useful but in production you can’t drop your index straightway because it could be risky .In 11g we have feature of Invisible indexes.

When you make an index invisible then optimizer can not see it and did't use this index except in case of if optimizer_use_invisible_indexes parameter is true which is unlikely a case, but in the background it will keep the index structure up to date.

select * from V$PARAMETER where name like '%invisible%'

Syntax for making an index invisible
alter Index Indexname invisible;

More details about Invisible indexes is on my Next Blog Diff B/w making and Index Unusable and Making and Index Invisible.

You can query DBA_VIEWS to get the information about the data dictionary views.I did this exercise to remove the unusable index from my application in this application we have lot of composite indexes on the transaction tables in some cases Index size is more then the data size.

I did't find this info at one place so i just tried to put it at one page. 


Your Questions and Suggestion are always welcome.









Friday 12 July 2013

Do we face no_data_found exception with cursors?

Oracle FAQ

Do we face no_data_found exception with cursors?

  • ·         No, no_data_found exception doesn’t occur with Cursors.
  • ·         We face no_data_found exception with select statements in PLSQL but not in SQL.

This is strange but true .Possible reason for this behavior is PLSQL is blocks structured language and don’t have input output capability of its own. We have to take the outputs in scalar or composite variables. SQL have output capability of its own & directly display the results.

  • ·         For DML Statements no No_data_found exception in PLSQL.
  • ·         No no_data_found exception with BULK Collect statements in PLSQL Select statement.


Examples:


There is no employee_id 0 in the employee table; SQL will give no row selected as output.


SQL> select last_name from employees where employee_id=0;
 
No rows selected

IN PLSQL same statement returns no data found exception

SQL> declare
vlname varchar2(800):=' No_DATA_FOUND EXCEPTION OCCUR WITH SELECT STATEMENT IN        PLSQL;
    begin
    select last_name into vlname from employees where employee_id=0;
    dbms_output.put_line(vlname);
    end;
    /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

Now the cursors, where we don’t face the no_data_found exception.

-- Simple cursor example
SQL>    declare
    cursor c1 is
    select last_name from employees where employee_id=0;
    vlname varchar2(500):='No No_DATA_FOUND EXCEPTION WITH CURSORS';
    begin
    open c1 ;
    loop
    fetch c1 into vlname;
   exit when c1%notfound or c1%notfound is null or c1%rowcount=0;
  end loop;
  close c1;
  dbms_output.put_line(vlname);
 end;
SQL> /
No No_DATA_FOUND EXCEPTION WITH CURSORS                                        

PL/SQL procedure successfully completed.
-------------------------------------------------------------------------------------------------------------------------------------
-- Cursor For loop Example
SQL>  declare
   cursor c1 is
    select last_name from employees where employee_id=0;
    vlname varchar2(500):='No No_DATA_FOUND EXCEPTION WITH CURSORS';
    begin
    for i in c1 loop
    dbms_output.put_line(vlname);
    end loop;
  * end;
SQL> /

PL/SQL procedure successfully completed.

-----------------------------------------------------------------------------------------------------
-- cursor for loop with subquery
SQL>
    declare
    vlname varchar2(500):='No No_DATA_FOUND EXCEPTION WITH CURSORS';
    begin
    for i in (select last_name from employees where employee_id=0) loop
    dbms_output.put_line(i.last_name);
    end loop;
  dbms_output.put_line(vlname);
  * end;
    /
No No_DATA_FOUND EXCEPTION WITH CURSORS                                        
PL/SQL procedure successfully completed.

 

No no_data_found exception with Select statement in DML Statements

SQL> create table asp as select * from employees where 1=2;

Table created.
\SQL>
   declare
  vlname varchar2(500):='NO No_DATA_FOUND EXCEPTION WITH DML STATEMENT IN PLSQL';
  begin
  insert into asp (select * from employees where 1=0);
  dbms_output.put_line(vlname);
 end;
  /

No_DATA_FOUND EXCEPTION WITH SELECT STATEMENT IN PLSQL                         
PL/SQL procedure successfully completed.
SQL> select * from asp;
no rows selected


NO NO_DATA_FOUND EXCEPTION WITH BULK COLLECT IN PLSQL SELECT STATEMENT


SQL>
   declare
    type vc_type is table of employees%rowtype
       index by pls_integer;
    ab vc_type;
    begin
    select *  into ab(1) from employees where employee_id=0;
    dbms_output.put_line('with bulk collect no no data found exception');
   end;
SQL> /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6
----------------------------------------------------------------------------------

SQL>
  declare
    type vc_type is table of employees%rowtype
       index by pls_integer;
    ab vc_type;
    begin
    select * bulk collect into ab from employees where employee_id=0;
    dbms_output.put_line('with bulk collect no no data found exception');
  * end;
SQL>
with bulk collect no no data found exception

PL/SQL procedure successfully completed.

  •  Your suggestion , Questions are always welcome ...



Sunday 24 February 2013

How to deploy the database changes



We need to deploy changes in the database either for a full version release or a bug fix.

I had seen in many projects we didn't provide much attention to this step & thinking database a just container for the data. But if pay attention to this from the start of development and test our scripts in a testing environment we can avoid lot of implementation issues in the production.

When you are planning to implement changes think following points and judge where your implementation Strategy stands.

1. What efforts require to run the database changes
Ideal Scenario It should run from a mouse click in window’s environment or just calling a single file In UNIX or Linux operating system.

2. Are your scripts providing logs for errors and messages for success or failure?
Scripts must provide the logs for each and every activity..

3 Is your scripts are robust.
How many time you run your scripts they should not fail.

4.If you face errors, during the implementation how easily you can track them and fix them.
Scripts with logs can make this process easy to track any issues.

To explain the process here we are creating the following objects in SCOTT schema.
TABLEX
one Primary key CONSTRAINTX
one sequence SEQ_X

Create all this objects in separate files so that we can debug them easily, in case of any error.

Let’s see how to create a robust script for table creation.

Check first table  exist in the database from user_tables data dictionary view if no then create the table else give the message
.-------------------------------------------------------------------------------------
Prompt Creating table ...
-- Create table
DECLARE
       fCOUNT INTEGER;
       fString Varchar2(
1000):=NULL;
BEGIN
       SELECT COUNT(*) INTO FCOUNT FROM USER_TABLES
                     WHERE TABLE_NAME=
'TABLEX';
       IF FCOUNT =
0        THEN
              fString:=
'create table TABLEX (
                      col1  VARCHAR2(5) not null,
                     col2    NUMBER(10) not null )'
;
              EXECUTE IMMEDIATE fString;
              DBMS_OUTPUT.PUT_LINE(
'TABLEX CREATED SUCCESSFULLY');
       ELSE
              DBMS_OUTPUT.PUT_LINE(
'TABLEX ALREADY EXISTS');
       END IF;
END;
/
-------------------------------------------------------------------------------------
Save this text as table.sql
-------------------------------------------------------------------------------------
Example how to create a Sequence .
Check first sequence exist in the database from user_sequences data dictionary view if no then create the sequence else give the message.
-------------------------------------------------------------------------------------
declare
       fcount number;
  fstring varchar2(
1000);
begin
           select count(*) into fcount from user_sequences where sequence_name=
'SEQ_X';
              if fcount =
0 then
   
    fstring:=
'CREATE SEQUENCE SEQ_X
                           MINVALUE 1 MAXVALUE 99999
                           INCREMENT BY 1 START WITH 1
                           NOCACHE NOORDER
                           NOCYCLE'
;
   
                     execute immediate fstring;

                     dbms_output.put_line (
'Sequence SEQ_X created successfully');
              else
                     dbms_output.put_line (
'Sequence SEQ_X allready exists');
              end if;
end ;
/
-------------------------------------------------------------------------------------
Save this text as seq.sql
-------------------------------------------------------------------------------------
Example script to create a Primary Key
Check first constraint  exist on the table from user_constraints data dictionary view if no then create the Constraint  else give the message.
-------------------------------------------------------------------------------------
DECLARE
    fCount Integer;
    fString varchar2(
250):=NULL;
BEGIN
              select COUNT(*) INTO FCOUNT  from user_constraints
              where table_name=
'TABLEX' and constraint_type='P'
    and constraint_name=
'CONSTRAINTX';
       IF FCOUNT =
0        THEN
              fString:=
'alter table TABLEX add constraint CONSTRAINTX primary key (col1)';
              EXECUTE IMMEDIATE fString;
              DBMS_OUTPUT.PUT_LINE(
'PRIMARY Key CONSTRAINT CONSTRAINTX ON TABLE TABLEX CREATED SUCCESSFULLY');
       ELSE
              DBMS_OUTPUT.PUT_LINE(
'PRIMARY Key CONSTRAINT CONSTRAINTX ON TABLE TABLEX allready EXISTS');
       END IF;
End
/
-------------------------------------------------------------------------------------
Save this text as PK.sql
-------------------------------------------------------------------------------------
Now to create the one file which call all this files .Set session settings accordingly using SQL*PLUS commands. spool is create the logs.
-------------------------------------------------------------------------------------
Set echo off
set autoprint off
set heading off
set feedback off
Set Define off
set serveroutput on SIZE 10000
SPOOL RUN_ME.LOG
@table.sql
@pk.sql
@seq.sql
PROMPT Task completed successfully
Set echo oN
set autoprint oN
set heading oN
set feedback oN
Set Define oN
set serveroutput oFF
SPOOL OFF

Exit  
-------------------------------------------------------------------------------------
Save this text as RUN_ME.SQL
-------------------------------------------------------------------------------------
How to create the batch file to run this RUN_ME.sql from window’s environment with one click
We are prompting the user are you sure you want to execute the scripts if yes then check for run_me.log file exist if yes delete it then open sqlplus in silent mode and connect to user and
Run the run_me.sql file. And for checking the logs open run_me.log file.
-------------------------------------------------------------------------------------
@echo off
cls
set /p choice=ARE YOU SURE TO EXECUTE SCRIPTS in %DbName% (y/n) ?
if '%choice%'=='y' goto begin
goto end

:begin
if exist Run_ME.log  then del RUN_ME.log
@echo on
sqlplus -S scott/tiger@test @run_me.sql
@notepad run_me.log
:end

Exit
-------------------------------------------------------------------------------------
Save this file as execute_me.bat And make sure file extension is a batch file.
-------------------------------------------------------------------------------------
And after clicking on execute_me.bat check the logs from RUN_ME.log file

Sample of logs 

Creating table ...
TABLEX ALREADY EXISTS                                                           
PRIMARY Key CONSTRAINT CONSTRAINTX ON TABLE TABLEX allready EXISTS              
SEQUENCE SEQ_X ALLREADY EXIST   



Your Comments & Suggestions are always welcome



Thursday 17 January 2013

What is Mutation & How to avoid Mutation



    Orianconsulting.com

The Mutating table error is a well-known problem encountered by most developers has come across this error.

  ORA-04091: table <tablename> is mutating,
            trigger/ function may not see it

Oracle has a very unique feature of read consistent view of data & when developers trying to break this rule Mutation errors come and stop the developer to break the rule So Mutation error is a friend of developers.

Lets Understand Mutation in detail.
  
The basic reason for this error is the way Oracle manages a read consistent view of data at a given point of time.You can only see consistent data. When we try to break this read consistency we face Mutation which simply says that you are breaking the rule and stop you do so and save your data & Object structure from corruption.

Objects with which you will face this error are

  1. Triggers
  1. Functions  
Most of the developers just think it will happen with triggers only but that’s not the case.

When & in which Situation Mutation error occurs with Triggers.

Case 1: When Trigger on table refers the same table:
  
Note: Mutating error with Triggers Occurs only for row level triggers.
not for statement level triggers


Operation
Type
Mutating
Insert
Before Row  Level
Only for Multi Row insert
Insert
After Row  Level
Yes for both
Update
Before Row  Level
Yes for both
Update
After Row  Level
Yes for both
Delete
Before Row  Level
Yes for both
Delete
After Row  Level
Yes for both



The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating.

Mutation will not occur if a single record is inserted in the table (using VALUES clause). If bulk insertion is done or data is inserted from another table mutation will occur.
  
The mutating error is not only encountered during queries, but also for insert, updates and deletes present in the trigger.

Key points for before row level Insert Triggers

  1. Only for multiple rows, with sub query syntax even in sub query we select only one value.
  2. After row level triggers generate mutation errors for single and multiple rows


See examples

SQL> create table mtt_same_table (col1 number,col2 varchar2(10));                                                                            
Table created.

SQL> create or replace trigger mutation_on_same_table
    before insert or update or delete on mtt_same_table
    for each row
    declare
    vcount number;
    begin
    select count(*) into vcount from mtt_same_table;
    dbms_output.put_line('mtt');
    end;
   /

Trigger created.

SQL> select * from mtt_same_table;

no rows selected

-- we can insert single row data with before row level triggers

SQL> insert into mtt_same_table values(1,'arun');
                                                                
1 row created.

--when we try to insert multiple rows using sub query 
-- Mutation  error prevent you to do do even sub query  returns no row.

SQL> insert into mtt_same_table select salary , last_name from employees where employee_id=101
 
insert into mtt_same_table select salary , last_name from employees where employee_id=101
         *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

-- When we try to update mutation error prevents to do so 

SQL> update mtt_same_table set col1=null ;
update mtt_same_table set col1=null
       *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

-- When we select from sub query ,either its one value or many mutation error you face because oracle don’t know how many rows are there until it fetch the rows

QL> insert into mtt_same_table select 1,'arun' from dual
SQL> /
insert into mtt_same_table select 1,'arun' from dual
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

-- DELETE example

SQL> delete from mtt_same_table;
 
delete from mtt_same_table
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'

-- Now the After row level triggers and Insert , You face Mutation with single row insertion as well unlike before row level triggers

SQL> create or replace trigger mutation_on_same_table
    After insert or update or delete on mtt_same_table
    for each row
    declare
    vcount number;
    begin
    select count(*) into vcount from mtt_same_table;
    dbms_output.put_line('mtt');
    end;
   /

Trigger created

  1* insert into mtt_same_table values (2,'mass')
SQL> /
insert into mtt_same_table values (2,'mass')
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_SAME_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.MUTATION_ON_SAME_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.MUTATION_ON_SAME_TABLE'



Case 2 when Trigger on table refers the different table

This happens when a foreign key reference is present with an on-delete-cascade option. A row level trigger on the master table will mutate if the detail table is being referred to in the trigger, for a delete transaction. This will only happen if the foreign key on the detail table is created with the on delete cascade option.

No mutation occurs if the master table is being referred in a trigger on the detail table.


-- master table

SQL> create table mtt_master_table (id number primary key ,name varchar2(10));

Table created.

-- Child table with foreign key and on delete cascade constraint.

SQL> create table mtt_child_table (id1 number references mtt_master_table(id) on delete cascade , name varchar2(10));

Table created.

-- Trigger on Master table

SQL>
   create or replace trigger trig_diff_table
    before insert or delete or update on
    mtt_master_table
    for each row
    declare
    vcount number;
    begin
    select count(*) into vcount from mtt_child_table ;
   end;
SQL> /

Trigger created.

-- insert data in master table

SQL> insert into mtt_master_table values(1,'arun');

1 row created.

SQL> insert into mtt_master_table values(2,'varun');

1 row created.

-- insert into child table

SQL> insert into mtt_child_table values(1,'bharat');

1 row created.

SQL>  insert into mtt_child_table values(1,'mbharat');

1 row created.

-- update master table as there are on delete cascade constarint is present which tries to delete the Corresponding record from the child table, and in the trigger we are selecting data from the Child table and data is changing.So mutation prevent to do so.

SQL> select * from mtt_master_table;
 
        ID NAME                                                                
---------- ----------                                                          
         1 arun                                                                
         2 varun                                                                

SQL> select * from mtt_child_table;
 
       ID1 NAME                                                                
---------- ----------                                                           
         1 bharat                                                              
         1 mbharat                                                             

SQL> delete from mtt_master_table where id=2;
 
  delete from mtt_master_table where id=2
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_CHILD_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRIG_DIFF_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRIG_DIFF_TABLE'


-- BUT WE CAN DELETE THE DATA FROM THE CHILD TABLE

SQL> delete from mtt_child_table;
 
2 rows deleted.

SQL> select count(*) from mtt_child_table;

Count(*)
-----------------
     0

 --Even there is no row in the child still we cannot delete from master table

SQL> delete from mtt_master_table
  2  /
delete from mtt_master_table
            *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_CHILD_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.TRIG_DIFF_TABLE", line 4
ORA-04088: error during execution of trigger 'SCOTT.TRIG_DIFF_TABLE'

--we just simply disable the trigger and see the results

SQL> alter trigger trig_diff_table disable;

Trigger altered.

SQL> delete from mtt_master_table ;

2 rows deleted.

We can delete now.

 Case 3 .Mutation error with Functions:

Mutation can occurs with functions as well, many of us think Mutation happens with Triggers only but it’s not true.In a function we are counting the no of rows from table and returning the count.

We are trying to insert into same table using this function.. Which will give us mutation error?

-- CREATE TABLE

  SQL> create table mtt_function_table (id number);

Table created.

-- CREATE FUNCTION WHICH REFER THE MTT_FUNCTION_TABLE

SQL > create or replace function mtt_function return number
     is
     vcount number;

     begin
     select count(*) into vcount from mtt_function_table;
    return vcount;
    end;
   /

Function created

-- Now we are trying to use this functions output in a insert statement as a value and at the same time we are refering the same table so Mutating
function ORA-04091

  SQL>  insert into mtt_function_table select mtt_function() from mtt_function_table;

insert into mtt_function_table select mtt_function() from mtt_function_table
                                      *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_FUNCTION_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.MTT_FUNCTION", line 5


SQL>  1* insert into mtt_function_table select mtt_function() from dual;

insert into mtt_function_table select mtt_function() from dual
                                      *
ERROR at line 1:
ORA-04091: table SCOTT.MTT_FUNCTION_TABLE is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.MTT_FUNCTION", line 5



Now the Question is how to avoid this

 
  • Don’t break the rule , don’t use the same table for insertion and selection at the same time , use temporary table or views.
  • Don’t use triggers, modern developers don’t use trigger 
  • If you have requirement that you have to break the rule then there is one more friend of developers Autonomous Transaction
  • Use compound triggers to avoid mutation  Compound triggers



Let us use the autonomous transaction approach for all the above examples we describe.


Case 1: When Trigger on table refers the same table:

We just use the Autonomous transaction Pragma to break the rule here

-- create trigger with Pragma autonomous transaction & see the magic


create or replace trigger mutation_on_same_table
    before insert or update or delete on mtt_same_table
    for each row
    declare
    vcount number;
pragma Autonomous_transaction;              
    begin
    select count(*) into vcount from mtt_same_table;
    dbms_output.put_line('mtt');
    end;
/

--Now as we create the trigger with Pragma autonomous transaction we are ready to break the rule  we can insert multiple value using sub query for a before row level trigger , update and delete from the same table

--single row Insert example

SQL> insert into mtt_same_table values (1,'ar'); 


1 row created.

--multiple rows with sub query example

SQL> insert into mtt_same_table select employee_id ,substr(last_name,1,6) from employees;

109 rows created.

SQL> select count(*) from mtt_same_table;
 
  COUNT(*)                                                                     
----------                                                                      
       110                                                                     

 --update table example

SQL> update mtt_same_table set col1=null ;

110 rows updated.


--delete from table example

SQL> delete from mtt_same_table;

110 rows deleted.

SQL> select count(*) from mtt_same_table ;

  COUNT(*)                                                                     
----------                                                                     
         0


Case 2 when Trigger on table refers the different table

Now we user Pragma autonomous transaction to break this rule with on delete cascade constraint, we will delete the records with trigger referring to the child table

SQL> create or replace trigger trig_diff_table
      before insert or delete or update on
        mtt_master_table
        for each row
       declare
        vcount number;
    pragma autonomous_transaction;
        begin
        select count(*) into vcount from mtt_child_table ;
     end;
   /

Trigger created.

--see the status of the trigger is enabled

SQL> select TRIGGER_NAME,STATUS from user_triggers where trigger_name='TRIG_DIFF_TABLE';

TRIGGER_NAME                   STATUS                                          
------------------------------ --------                                         
TRIG_DIFF_TABLE                ENABLED                                         


--check no of records in master table

SQL> SELECT COUNT(*) FROM MTT_MASTER_TABLE;


  COUNT(*)                                                                      
----------                                                                     
         2                                                                     

--check no of records in child table
SQL> SELECT COUNT(*) FROM MTT_CHILD_TABLE;


  COUNT(*)                                                                     
----------                                                                     
         1  

--delete from master table and see the magic of pragma  Autonomous Transaction                                                                 

SQL> DELETE FROM MTT_MASTER_TABLE;


2 rows deleted.

SQL> SELECT COUNT(*) FROM MTT_MASTER_TABLE;


  COUNT(*)                                                                     
----------                                                                      
         0                                                                     

 --see child records also deleted as we have the on delete cascade and We are referring the same table in trigger

SQL> SELECT COUNT(*) FROM MTT_CHILD_TABLE;

  COUNT(*)                                                                     
----------                                                                     
         0                                                                      

SQL> select TRIGGER_NAME,STATUS from user_triggers where trigger_name='TRIG_DIFF_TABLE';


TRIGGER_NAME                   STATUS                                          
------------------------------ --------                                         
TRIG_DIFF_TABLE                ENABLED                                         


Case 3 .Mutation error with Functions:

Now we insert the function output in the same table , and function is referring the same table


--create function with PRAGMA AUTONOMOUS_TRANSACTION


SQL>   create or replace function mtt_function return number
         is
     vcount number;

       PRAGMA AUTONOMOUS_TRANSACTION;
     begin
   
              select count(*) into vcount from mtt_function_table;
    return vcount;
    end;
   /

Function created.

--now we can insert into the same table function output and while in a function we are refering the same table



SQL>  insert into mtt_function_table select mtt_function() from mtt_function_table;

1 row created.

SQL> insert into mtt_function_table select mtt_function() from dual;

1 row created.


As we all see the magic of Autonomous Transaction Pragma , but It does not mean every time you just use the Pragma to break the rules

See detail uses of Pragama Autonomous Transaction in my next blog.

Your comments suggestions are awaited :0