Home » SQL & PL/SQL » SQL & PL/SQL » Deletion from tables as new tables are added to schema
Deletion from tables as new tables are added to schema [message #410421] Fri, 26 June 2009 19:16 Go to next message
anonymous2009
Messages: 23
Registered: June 2009
Junior Member
Hello,
There is this scheduler job that run every night and deletes from couple of tables.
As new tables are added to the schema with FK reference to the tables I'm already deleting from, I obviously need to delete from the newly added table first (child table).

My question is, What is the efficient way to do this without changing the code to include deletion from the new added table. The nightly job (or) the stored procedure that's invoked from the nightly job must somehow identify those newly added table, then join with the reference column, and delete the record.

Thanks
Re: Deletion from tables as new tables are added to schema [message #410423 is a reply to message #410421] Fri, 26 June 2009 19:33 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>stored procedure that's invoked from the nightly job must somehow identify those newly added table, then join with the reference column, and delete the record.
Small Matter Of Programmimg

What is the algorithm for code to identify which table should be involved?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Deletion from tables as new tables are added to schema [message #410439 is a reply to message #410421] Sat, 27 June 2009 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create the foreign key constraint with ON DELETE CASCADE option.

Regards
Michel
Re: Deletion from tables as new tables are added to schema [message #410630 is a reply to message #410439] Mon, 29 June 2009 06:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
ON DELETE CASCADE fills the 'Without Changing the Code' part of the requirement - the performance will start to deteriorate if you are doing a significant number of deletions, as it deletes the child records on each child table for each record deleted inthe parent table.
If you decide to delete 10% of the parent rows, you're going to notice a real hit on the performance.

The other way of doing it that I'd look at would be to make all the FK constraints deferrable, meaning that they're not checked until changes are committed, and then add two triggers to the parent table:
a before Delete one to record the PK value of the deleted parent row in a GTT
a statement level after delete trigger that did a delete on each table with a FK link to the parent table - you ould either do this dynamically, or edit the triger each time you added a new table in.
Re: Deletion from tables as new tables are added to schema [message #410632 is a reply to message #410630] Mon, 29 June 2009 06:48 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
JRowbottom wrote on Mon, 29 June 2009 06:41
ON DELETE CASCADE fills the 'Without Changing the Code' part of the requirement - the performance will start to deteriorate if you are doing a significant number of deletions, as it deletes the child records on each child table for each record deleted inthe parent table.



Hey, just curious, but have you done much with this in the way of testing / performance evaluation? I have only needed to use it occasionally myself. Is there anything about it that is inherrently slow? I mean, would this technique be any slower than issuing two independent delete statements?
Re: Deletion from tables as new tables are added to schema [message #410636 is a reply to message #410632] Mon, 29 June 2009 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
smartin wrote on Mon, 29 June 2009 13:48
JRowbottom wrote on Mon, 29 June 2009 06:41
ON DELETE CASCADE fills the 'Without Changing the Code' part of the requirement - the performance will start to deteriorate if you are doing a significant number of deletions, as it deletes the child records on each child table for each record deleted inthe parent table.



Hey, just curious, but have you done much with this in the way of testing / performance evaluation? I have only needed to use it occasionally myself. Is there anything about it that is inherrently slow? I mean, would this technique be any slower than issuing two independent delete statements?

Interested to know/see that too.

Regards
MIchel

Re: Deletion from tables as new tables are added to schema [message #410638 is a reply to message #410632] Mon, 29 June 2009 07:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're deleting a lot of rows, then yes, it's quite a bit slower to use the on delete cascade - it has to do a delete for each row, whereas the other approach just does on big delete and a bunch of simple inserts.

Here's an example:
create table test_234 (pk_col  number
                      ,other_col  number
                      ,constraint test_234_pk primary key (pk_col));

create table test_235 (fk_col  number
                      , constraint test_235_fk foreign key(fk_col) references test_234(pk_col) on delete cascade);

create index test_235_fk_idx on test_235(fk_col);

create table test_236 (pk_col  number
                      ,other_col  number
                      ,constraint test_236_pk primary key (pk_col));

create table test_237 (fk_col  number
                      , constraint test_237_fk foreign key(fk_col) references test_236(pk_col) deferrable initially deferred);

create index test_237_fk_idx on test_237(fk_col);

                      
insert into test_234 (select level,mod(level,100) from dual connect by level <= 100000);

insert into test_235 (select nullif(t1.lvl*t2.lvl,0)
                      from   (select level lvl from dual connect by level<=1000) t1
                            ,(select level lvl from dual connect by level <= 100) t2);
                            
                           
                            
insert into test_236 (select level,mod(level,100) from dual connect by level <= 100000);

insert into test_237 (select nullif(t1.lvl*t2.lvl,0)
                      from   (select level lvl from dual connect by level<=1000) t1
                            ,(select level lvl from dual connect by level <= 100) t2);                            

create global temporary table test_236_deletes (pk_col  number) on commit delete rows;
/

create or replace trigger test_236_bdr 
before delete on test_236 
for each row
begin
  insert into test_236_deletes (pk_col) values (:old.pk_col);
end;
/

create or replace trigger test_236_ad
after delete on test_236
begin
  delete test_237
  where  fk_col in (select * from test_236_deletes);
end;
/

set timing on

delete test_234 where mod(pk_col,5)=1;

commit;

delete test_236 where mod(pk_col,5)=1;

commit;


Results:
SQL> 
SQL> set timing on
SQL> 
SQL> delete test_234 where mod(pk_col,5)=1;

20000 rows deleted.

Elapsed: 00:00:11.09
SQL> 
SQL> commit;

Commit complete.

Elapsed: 00:00:00.26
SQL> 
SQL> delete test_236 where mod(pk_col,5)=1;

20000 rows deleted.

Elapsed: 00:00:05.64
SQL> 
SQL> commit;

Commit complete.

Elapsed: 00:00:00.15
Re: Deletion from tables as new tables are added to schema [message #410681 is a reply to message #410421] Mon, 29 June 2009 11:31 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Thanks for the example. I haven't scrutinized it yet to attempt to tear it apart, but my first impression is, "Wow".
icon4.gif  Re: Deletion from tables as new tables are added to schema [message #411107 is a reply to message #410421] Thu, 02 July 2009 00:11 Go to previous messageGo to next message
anonymous2009
Messages: 23
Registered: June 2009
Junior Member
JRowbottom, interesting solution. Thanks.

But this approach still requires me to add DELETE statment in the after trigger (trigger test_236_ad)every time a new table is added to the schema.
But what if I do not have the privilege of maintaining this trigger every time a new table is added to the schema.

My approach...
I was thinking of having a table where I'll maintain the table name and the reference column. Record must be inserted into this table whenever new table is added to the schema (More like a lookup table).
Table Name: Tbls_Cols
Table Name Join_col
---------- --------
Salary Eno
EmpDetails Eno

When the deletion SP is invoked must from the nightly job, the SP must check this table (Tbls_Cols) for any new table. If exists, then it must dynamically build the delete statement using the table name and column name in Tbls_Cols.join_col, for the WHERE clause.

This way the only effort from my side will be to maintain the Tbls_Cols table.
But I'm not sure if this approach will work out, and the performance issues associated with this.

Please let me know if anyother approach available to handle this case....
Thanks.
Re: Deletion from tables as new tables are added to schema [message #411155 is a reply to message #411107] Thu, 02 July 2009 03:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
But what if I do not have the privilege of maintaining this trigger every time a new table is added to the schema.


I'm sorry? Are you saying that people can just add tables in your application without you knowing about it?

Quote:
I was thinking of having a table where I'll maintain the table name and the reference column. Record must be inserted into this table whenever new table is added to the schema

If you can force rows to be added to this table, then why can't you force a change to be made in the trigger?

Anyway, you're re-inventing the wheel. Oracle already holds all that information in USER_CONSTRAINTS and USER_CONS_COLUMNS - that's where you should get the data from if you want to generate dynamic deletes.

The best solution if you can't/won't maintain the trigger is to write a procedure that will use the USER_CONSTRAINTS data to re-build the trigger - that way you just need to call a single procedure after adding a table and the trigger gets rebuilf and will rn as efficiently as possible.
Previous Topic: Partition error
Next Topic: where clause for previous month date
Goto Forum:
  


Current Time: Fri Dec 09 01:52:04 CST 2016

Total time taken to generate the page: 0.10066 seconds