Recusive delete in oracle

With below procedure we can delete dependent row from table ( either parent or child table ), with enable disable of constraints.

It is one of the type of explicit delete cascade procedure for oracle.

drop table to_be_deleted purge;
create table to_be_deleted
(tname varchar2(30)       -- table name
,rid rowid                -- rowid
,lvl number               -- level: 1=parent, 2=child, 3=grandchild, etc.
,seq_ins number           -- sequence order of record inserted
,flg_del char             -- flag deleted: Y=record deleted
,seq_del number           -- global order of record deletion
);
 

 set serveroutput on size 1000000

 
 create or replace procedure prc_delete_cascade
(p_tname varchar2  -- table name
,p_where varchar2  -- where clause identifying records to be cascade deleted
)
is
  dummy         char;
  v_sqlcode     number;
  v_sqlerrm     varchar2(32767);
  v_param_val   integer := 0;
  v_sql         varchar2(4000);
  v_ret_cde     number;
  e_bad_params  exception;
  v_iter        number;
  v_plvl        number;
  v_seq_del     number;
  v_max_iter    number := 1000000000;
  
  function delete_bott_row
  return number
  is
    v_sql        varchar2(4000);
    v_ptname     varchar2(30);  -- parent table name
    v_ppkname    varchar2(30);  -- parent primary key constraint name
    v_ppkcname   varchar2(30);  -- parnet primary key column name
  v_prowid      rowid;
    v_crowid      rowid;
  v_ctname     varchar2(30);  -- child table name
    v_cfkname    varchar2(30);  -- child foreign key constraint name
    v_cfkcname   varchar2(30);  -- child foreign key column name
  v_ins        number;
    v_seq_ins    number;
  v_sqlerrm    varchar2(4000);
    v_sqlcode    number;
    
    e_const_viol exception;
    pragma exception_init(e_const_viol, -2292);
  e_max_iter_reached exception;
    
  begin
    v_iter := v_iter + 1;
     
    if v_iter >= v_max_iter then
      raise e_max_iter_reached;
    end if;
     
    dbms_output.put_line('- Iter '||to_char(v_iter));
    dbms_output.put_line('----------');
    dbms_output.put_line('- Starting function delete_bott_row');
    v_sql := 'select tname, rid, lvl, seq_ins from (select * 
    from to_be_deleted where flg_del = ''N'' order by lvl desc, seq_ins desc) where rownum=1';
    --  dbms_output.put_line('- SQL: '||v_sql);
    execute immediate v_sql into v_ptname, v_prowid, v_plvl, v_seq_ins;
     
    dbms_output.put_line('- Selected row: table name: '||v_ptname||', level: '||v_plvl||', seq: '||v_seq_ins);
     
    v_sql := 'delete from '||v_ptname||' where rowid='''||v_prowid||'''';
    dbms_output.put_line('- SQL: '||v_sql);
    execute immediate v_sql;
    dbms_output.put_line('- Row deleted !!!');
    v_ret_cde := 1;
    v_seq_del := v_seq_del + 1;
     
    dbms_output.put_line('- Mark the row deleted');
    v_sql := 'update to_be_deleted set flg_del = ''Y'', seq_del = '||to_char(v_seq_del)
    ||' where tname='''||v_ptname||''' and rid='''||v_prowid||'''';
    -- dbms_output.put_line('- SQL: '||v_sql);
    execute immediate v_sql;
    -- dbms_output.put_line('- Updated table to_be_deleted, row marked deleted');
    -- dbms_output.put_line('- End of iter '||to_char(v_iter));
    dbms_output.put_line('----------');
      
    -- call function delete_bott_row recursively
    v_ret_cde := delete_bott_row;
 
    return 0;
      
  exception
    when no_data_found then
      dbms_output.put_line('- Table to_be_deleted is empty, delete cascade has completed successfully.');
      v_ret_cde := 0;
      return 0;
       
    when e_const_viol then
      v_sqlcode := SQLCODE;
      v_sqlerrm := SQLERRM;
 
      v_ret_cde := v_sqlcode;
        
      dbms_output.put_line('>Constraint Violation. Record has children');
      -- dbms_output.put_line('Error code: '||to_char(v_sqlcode));
      v_cfkname := substr(v_sqlerrm,instr(v_sqlerrm,'.')+1,instr(v_sqlerrm,')') - instr(v_sqlerrm,'.')-1);
       
      dbms_output.put_line('>Child FK name: '||v_cfkname);
       
      select table_name, column_name
        into v_ctname, v_cfkcname
        from user_cons_columns
       where constraint_name=v_cfkname;
      dbms_output.put_line('>Child table name: '||v_ctname||'. FK column name: '|| v_cfkcname);
       
      select constraint_name, column_name
        into v_ppkname, v_ppkcname 
        from user_cons_columns
       where constraint_name = (select r_constraint_name 
                                  from user_constraints 
                                  where constraint_name=v_cfkname);
      dbms_output.put_line('>Parent PK/UK name: '||v_ppkname||'. Parent PK/UK column: '||v_ppkcname);
                                
      v_sql := 'insert into to_be_deleted(tname, rid, lvl, seq_ins, flg_del) '||
               'select '''||v_ctname||''', rowid, '||to_char(v_plvl+1)||', rownum, ''N'' '||
               'from '||v_ctname||' '||
               'where '||v_cfkcname||' =any (select '||v_ppkcname||' from '||v_ptname||
       ' where rowid =any (select rid from to_be_deleted where tname = '''||v_ptname||'''))';
      -- dbms_output.put_line('- SQL: '||v_sql);
      execute immediate v_sql;
 
      select count(*) 
        into v_ins
        from to_be_deleted 
       where lvl = v_plvl+1 
         and tname = v_ctname
         and flg_del = 'N';
      dbms_output.put_line('>Found '||to_char(v_ins)||' child records which were added to table to_be_deleted');   
         
      v_ret_cde := delete_bott_row;
       
      return  v_ret_cde;
      
    when e_max_iter_reached then
      dbms_output.put_line('Maximum iterations reached.  Terminating procedure.');
      raise;
        
    when others then
      raise;
           
  end delete_bott_row;
          
begin
  dbms_output.put_line('Beginning');
  dbms_output.put_line('================================');
 
  -- validate p_table
  begin
    select 'Y'
      into dummy
      from user_tables
     where table_name=upper(p_tname);
  exception
    when no_data_found then
    v_param_val := 1;
    dbms_output.put_line('Table '||p_tname||' does not exist.');
    raise e_bad_params;
  end;
  dbms_output.put_line('- Parameter p_tname validated');
 
  -- validate p_where 
  begin
    execute immediate 'select ''Y'' from '||p_tname||' where '||p_where INTO dummy;
  exception
    when no_data_found then  -- where clause returns no records
      dbms_output.put_line('Record(s) not found.  Check your where clause parameter');
      v_param_val := 2;
      raise e_bad_params;
    when too_many_rows then  -- found multiple records means it is ok
      null;  
    when others then  --  any other records means where clause has something wrong.
      dbms_output.put_line('Where clause is malformed');      
      v_param_val := 2;
      raise e_bad_params;
  end;    
  dbms_output.put_line('- Parameter p_where validated');
 
  if v_param_val > 0 then raise e_bad_params; end if;
  
  v_iter := 0;
  v_plvl := 1;
  v_seq_del := 0;
  v_sql := 'insert into to_be_deleted(tname, rid, lvl, seq_ins, flg_del) select '''||
  upper(p_tname)||''', rowid, '||to_char(v_plvl)||', rownum, ''N'' from '||p_tname||' where '||p_where;
 
  dbms_output.put_line('- Inserting initial record');
  dbms_output.put_line('- SQL: '||v_sql);
  execute immediate v_sql;
  dbms_output.put_line('- Record(s) inserted'); 
  
  
  dbms_output.put_line('- Calling function delete_bott_row to delete last row of table to_be_deleted');               
  dbms_output.put_line('-----------------------------------');               
  v_ret_cde :=  delete_bott_row; 
  -- dbms_output.put_line('- Back from function delete_bott_row');               
  -- dbms_output.put_line('Return code: '||to_char(v_ret_cde));               
  dbms_output.put_line('- End of procedure');               
 
exception
  when e_bad_params then
    dbms_output.put_line('Bad parameters, exiting.');
end;
/ 



exec prc_delete_cascade('xent','xent_id between 1669 and 1670')
or
exec prc_delete_cascade('table_name','column_name = 1669');

Source:- https://community.oracle.com/thread/2196578?tstart=0

Cannot insert explicit value for identity column in table ' ' when IDENTITY_INSERT is set to OFF.

Replicate the Problem:

create table [dbo].[Color]
(colorkey int identity(1,1) not null,
colorname nvarchar(50) not null,
constraint pk_color_key primary key clustered (colorkey)
);


insert into color (colorname, colorkey) values (  'Black',  2);
insert into color (colorname, colorkey) values (   'Blue' , 3);
insert into color (colorname, colorkey) values (   'Grey' , 4);
insert into color (colorname, colorkey) values (   'Multi' , 5);
insert into color (colorname, colorkey) values (   'Red' , 6);
 
ERROR:
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ' ' when IDENTITY_INSERT is set to OFF.
 
PROBLEM:
This error means we are trying to add a value explicitly to a column (insert query), where the database automatically add it.

SOLUTION: 
Execute below command to give up the error.
set identity_insert color on;


Note*- It is not advise to assign value for column, which is set as "IDENTITY". Let the database insert value implicitly.
** color is the table-name (in heading solution)

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Replicate the problem:

create table parent (id int not null, name nvarchar(140), constraint pk_id primary key (id ));
create table child (id int not null , parent_id int  , constraint child_pk_id primary key (id ));
insert into child values (1,10);
insert into child values (2,20);
alter table child add constraint fk_parent_id foreign key (parent_id) references parent (id);

ERROR:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint " ". The conflict occurred in database " ", table " ", column ''.

PROBLEM:
We are trying to create foreign key on table, where the child table column values not found in parent table. (or parent table is empty)

SOLUTION:
Add associated values in parent your table, which shoul be reflect in child table.
insert into parent values (10, 'DBA');
insert into parent values (20, 'SE');
alter table child add constraint fk_parent_id foreign key (parent_id) references parent (id);   
Command(s) completed successfully.

Use of CASE statement in UPDATE query

Use of CASE statement in UPDATE query:
update emp
set job = case when job = 'CLERK' then 1
                when job = 'SALESMAN' then 2
                when job = 'MANAGER' then 3
                when job = 'ANALYST' then 4
                when job = 'PRESIDENT' then 5 else 0 end;
(14 row(s) affected)


Similer post:
Cannot insert the value NULL into column '', table ' '; column does not allow nulls. UPDATE fails.

Cannot insert the value NULL into column '', table ' '; column does not allow nulls. UPDATE fails.

To Get the same error i execute the below queries on schema scott.emp
here we need to make the column not null first to get the same error.

alter table emp alter column job nvarchar(20) not null;

Insert some unmatched record:
insert into emp values (3434,'GURJEET',0,7782,getdate(),1920,null,10);
 
update emp
set job = case when job = 'CLERK' then 1
                when job = 'SALESMAN' then 2
                when job = 'MANAGER' then 3
                when job = 'ANALYST' then 4
                when job = 'PRESIDENT' then 5 end

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column '', table ' '; column does not allow nulls. UPDATE fails.
The statement has been terminated.   
   
SOLUTION:1. Use of where clause
We can use the "where" clause for update query to restrict the records, which are not mentioned in "case statement". In below query i handle the record of having "JOB=0"

update emp
set job = case when job = 'CLERK' then 1
                when job = 'SALESMAN' then 2
                when job = 'MANAGER' then 3
                when job = 'ANALYST' then 4
                when job = 'PRESIDENT' then 5 end
where job not in ('0');           
(14 row(s) affected)

SOLUTION:2. Use of "ELSE" in "CASE statement", Again we add the else in CASE Statement to handle unmatched record.

update emp
set job = case when job = 'CLERK' then 1
                when job = 'SALESMAN' then 2
                when job = 'MANAGER' then 3
                when job = 'ANALYST' then 4
                when job = 'PRESIDENT' then 5 else 0 end;
(14 row(s) affected)

uniqueidentifier [guid] vs int

The uniqueidentifier data type stores 16-byte binary values that operate as globally unique identifiers (GUIDs). A GUID is a unique binary number; no other computer in the world will generate a duplicate of that GUID value.
On other hand int data type store 4-byte value.

A GUID value for a uniqueidentifier column is usually obtained by one of the following ways:

•     In a Transact-SQL statement, batch, or script by calling the NEWID function.
•     In application code by calling an application API function or method that returns a GUID.

   
   
The uniqueidentifier data type has the following disadvantages:

•     The values are long and obscure. This makes them difficult for users to type correctly, and more difficult for users to remember.
•     The values are random and cannot accept any patterns that may make them more meaningful to users.
•     There is no way to determine the sequence in which uniqueidentifier values were generated. They are not suited for existing applications that depend on incrementing key values serially.
•     At 16 bytes, the uniqueidentifier data type is relatively larger than other data types, such as 4-byte integers. This means indexes that are built using uniqueidentifier keys might be relatively slower than indexes using an int key.

   
Cluster-index can be apply on:-

1. Random GUIDs (using NEWID function)
2. Sequential GUIDs (using NewSequentialGUID function)
3. BIGINT identity value
4. INT identity value

Below is the table for comparing and ranking for above datatypes:

Criteria	GUIDs	Seq. GUIDs	BIGINT	INT
Storage	16 bytes	16 bytes	8 bytes	4 bytes
Insert/Update performance	Slowest	Comparable but the index keys are larger in size. For wider tables, this would be slower than Integer values.	Faster than sequential GUIDs	Fastest
Hotspot contention	Very rare	Yes	Yes	Highest, due to smaller size of RIDs
Fragmentation/Page Splits	High	Minimal	Minimal	Minimal
JOIN Performance/SORT operations	Least performance (Rank 4 = Least performance)	Better than random GUIDs due lesser fragmentation (Rank: 3)	High performance (Rank: 2)	High Performance (Rank: 1)
Logical reads	Rank 4=Highest	Rank 3	Rank 2	Rank 1=Least
Merging data across servers	Beneficial	Beneficial	Difficult	Difficult
Uniqueness	Rare chance of duplicates	Globally unique. Virtually no chance of collisions	Limited by range of BIGINT	Limited by range of INT

The above considerations make the use of GUIDs unfavorable for a clustered index in environments which have large number of queries performing JOIN operations in OLTP and when referential integrity is enforced on the database among multiple tables. Throw non-clustered indexes that you created on the table as covering indexes for the frequently run queries against the database, you can have a significant performance bottleneck.

Source:
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx?Redirected=true

Advantage and disavantage of MOLAP, ROLAP

MOLAP
Its the traditional way of OLAP analysis. Data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats(cube).
Advantages:

• Excellent performance: MOLAP cubes are built for fast data retrieval, and is optimal for slicing and dicing operations.
• Can perform complex calculations: All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only doable, but they return quickly.

Disadvantages:

• Limited in the amount of data it can handle: Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this is possible. But in this case, only summary-level information will be included in the cube itself.
• Requires additional investment: Cube technology are often proprietary and do not already exist in the organization. Therefore, to adopt MOLAP technology, chances are additional investments in human and capital resources are needed.

ROLAP
This methodology relies on manipulating the data stored in the relational database to give the appearance of traditional OLAP's slicing and dicing functionality. In essence, each action of slicing and dicing is equivalent to adding a "WHERE" clause in the SQL statement.
Advantages:

• Can handle large amounts of data: The data size limitation of ROLAP technology is the limitation on data size of the underlying relational database. In other words, ROLAP itself places no limitation on data amount.
• Can leverage functionalities inherent in the relational database: Often, relational database already comes with a host of functionalities. ROLAP technologies, since they sit on top of the relational database, can therefore leverage these functionalities.

Disadvantages:

• Performance can be slow: Because each ROLAP report is essentially a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large.
• Limited by SQL functionalities: Because ROLAP technology mainly relies on generating SQL statements to query the relational database, and SQL statements do not fit all needs (for example, it is difficult to perform complex calculations using SQL), ROLAP technologies are therefore traditionally limited by what SQL can do. ROLAP vendors have mitigated this risk by building into the tool out-of-the-box complex functions as well as the ability to allow users to define their own functions.

HOLAP
HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can "drill through" from the cube into the underlying relational data. 


Source - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6da8f3d7-e0d5-4ccc-926b-5d97eaeb5934/molap-rolap-holap?forum=sqlanalysisservices

Create your first OLAP Cube in SSAS

I get below links to create olap cube in SSAS using database AdventuresWorksDW. These links provide very clear cut guidance (step by step) to create a cube in ssas.

Let's create an Analysis Services Cube using the new Cube Wizard that comes with SQL Server 2008 Business Intelligence Development Studio.

Please follow the steps below (make a click on each step for details):

1. Create an Analysis Services Project
2. Create a Data Source
3. Create a Data Source View
4. Create a Cube
5. Create perspectives
6. Deploy the Analysis Services Project

Storage types of cube MOLAP, ROLAP, HOLAP

A cube is a structure made of number of dimensions, measures, etc.
Cubes usually rely on two kind of tables like 'fact-table' (for cubes) & 'dimension-table' (for cube’s different dimensions).
A cube can have only one fact-table and ‘n’ number of dimension tables (based on no: of dimensions in the cube).
It store the data in pre-aggreagted form.

Cube Storage type:
MOLAP (Multi dimensional Online Analytical Processing)
MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. Data AND aggregations are stored in optimized format in the cube. The data inside the cube will refresh only when the cube is processed, so latency is high.

ROLAP (Relational Online Analytical Processing)
ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube. Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.

HOLAP (Hybrid Online Analytical Processing)
HOLAP is a storage type between MOLAP and ROLAP. Data will be stored in relational format(ROLAP), so there will also be zero latency with this storage type. Aggregations, on the other hand, are stored in multi dimensional format(MOLAP) in the cube to give better query performance. SSAS will listen to notifications from the source relational database, when changes are made, SSAS will get a notification
and will process the aggregations again.With this mode it’s possible to offer zero latency to the users but with medium query performance compared to MOLAP and ROLAP.

We use MOLAP storage type generally for deliver high performance


More clear image to understand the storage type of cubes:-

OLAP CUBE

What is OLAP cube:- 
An OLAP cube is a collection of measures (facts) and dimensions from the data warehouse.
An OLAP cube is a multidimensional database that is optimized for data warehouse and online analytical processing (OLAP) applications.
An OLAP cube is a method of storing data in a multidimensional form, generally for reporting purposes.
In OLAP cubes, data (measures) are categorized by dimensions.

Data and aggregations are stored in a (pre-summarized across dimensions) optimized format to offer very fast query performance.
MDX (multidimensional expressions) query language is used to interact and perform tasks with OLAP cubes.
The MDX language was originally developed by Microsoft in the late 1990s, and has been adopted by many other vendors of multidimensional databases.

Although it stores data like a traditional database does, an OLAP cube is structured very differently.
OLAP cubes, however, are used by business users for advanced analytics.
Thus, OLAP cubes are designed using business logic and understanding.
They are optimized for analytical purposes, so that they can report on millions of records at a time.

When to use CUBE-
There are three reasons for adding a cube to your solution:
1. Performance-  A cube’s structure and pre-aggregation allows it to provide very fast responses to queries that would have required reading, grouping and summarizing millions of rows of relational star-schema data.
The drilling and slicing and dicing that an analyst would want to perform to explore the data would be immediate using a cube but it could take longer when using a relational data source.

2. Drill down functionality-  Many reporting software tools will automatically allow drilling up and down on dimensions with the data source is an OLAP cube.
    Some tools, like IBM Cognos’ Dimensionally Modeled Relational model will allow you to use their  product on a relational source and drill down as if it were OLAP but you would not have the performance gains you would enjoy from a cube.

3. Availability of software tools-  Some client software reporting tools will only use an OLAP data source for reporting. These tools are designed for multi-dimensional analysis and use MDX behind the scenes to query the data.

Disadvantage of using SSAS cube-
SSAS processes data from the underlying relational database into the cube.
After this is done the cube is no longer connected to the relational database so changes to this database will not be reflected in the cube.
Only when the cube is processed again, the data in the cube will be refreshed.

So Basically what the cube is:-
A cube is a structure made of number of dimensions, measures, etc.
Cubes usually rely on two kind of tables like 'fact-table' (for cubes) & 'dimension-table' (for cube’s different dimensions).
A cube can have only one fact-table and ‘n’ number of dimension tables (based on no: of dimensions in the cube).
It store the data in pre-aggreagted form.

see also storage type`s of cube MOLAP, ROLAP, HOLAP