kishor -- Thanks for the question regarding "generate delete statement", version 9.2
Submitted on 20-Dec-2007 18:54 Central time zone
Last updated 21-Dec-2007 14:03
You Asked
Hi Tom
Thanks for always being there for us and also taking my question.
I have two tables, one of which gives table name and other gives column_name, column_type and column value. We need to delete rows for these column values from tables in our master database. For each table number of columns varies ( this is PK columns of table but COL_DTL table does not give column position).
We generally get around 500,000 rows to delete every day.
There are about 500 tables. Out of these 500 tables some of them are very huge and have 50 million to 150 million rows in master database. Number of rows deleted from each table varies by table by day.
Here is structure of those table ..
create table TBL_DTL
(
ROW_ID NUMBER(10) not null,
TBL_NM VARCHAR2(30) not null
)
/
insert into TBL_DTL values (351454103, 'ES_STS');
insert into TBL_DTL values (351454048, 'ES_STS_RSN');
insert into TBL_DTL values (351454037, 'BV_CLM_RCVR');
insert into TBL_DTL values (351454107, 'CS_SPL_SITN');
insert into tbl_dtl values ( 351454102, 'ES_STS');
create table COL_DTL
(ROW_ID NUMBER(10) not null,
COL_NM VARCHAR2(30) not null,
COL_DATA_TYP_IND CHAR(1) not null,
COL_VAL VARCHAR2(256) not null
)
/
insert into col_dtl values ( 351454037 ,'CLM_ID', 'N','335414');
insert into col_dtl values ( 351454037,' CLM_RCVR_ID','N', '1991914');
insert into col_dtl values ( 351454048,' CS_ID','C', 'BD67237');
insert into col_dtl values ( 351454048 ,'CWIN','N', '541099');
insert into col_dtl values ( 351454048 ,'ES_RSN_ID','N', '26333731');
insert into col_dtl values ( 351454048 ,'ES_STS_ID' ,'N', '21814947');
insert into col_dtl values ( 351454048 ,'PGM_TYP_CD','C', 'CL');
insert into col_dtl values ( 351454103 ,'CS_ID','C','BC41287');
insert into col_dtl values ( 351454103 ,'CWIN','N', '359168');
insert into col_dtl values ( 351454103 ,'ES_STS_ID','N', '21304304');
insert into col_dtl values ( 351454103 ,'PGM_TYP_CD','C', 'WW');
insert into col_dtl values ( 351454107 ,'CS_ID','C', '1B0CJ67');
insert into col_dtl values ( 351454107 ,'EFF_BGN_DT' ,'D', '20060302000000');
insert into col_dtl values ( 351454107 ,'SPL_SITN_ID' ,'N', '81434');
insert into col_dtl values ( 351454102 ,'CS_ID','C','BC41287');
insert into col_dtl values ( 351454102 ,'CWIN','N', '114540');
insert into col_dtl values ( 351454102 ,'ES_STS_ID','N', '21304300');
insert into col_dtl values ( 351454102 ,'PGM_TYP_CD','C', 'WW');
If we load these two tables as external tables or regular table, can we generate a script out of these two tables to delete corrosponding records from master tables. each record to be deleted has unique row_id in both tables.
Thanks
Kishor
Additional info --
will the same columns be used for each table?
eg: ES_STS - will the col_dtl table ALWAYS have 4 columns for it.\
Yes - for a particular table there will be same number of columns. These may change during major releases which are very infrequent.
I totally understand - denormalized ugly mess -- but since we are getting data from outside vendor who is not changing the structure we have no choice unfortunately.
Thanks
Kishor
and we said...
This is worse than a denormalized mess, it is a half way denormalized mess.
The use of surrogate keys - silly in this case, worse than silly - a performance impediment that adds NOTHING. And the halfway comment is in regards "so why not do it for the col_nm as well".
ugh.
so, basically we need to generate delete statements that look like this:
delete from CS_SPL_SITN
where (cs_id,eff_bgn_dt,spl_sitn_id) in
(
select max(decode(rn,1,col_val)) ,
to_date( max(decode(rn,2,col_val)), 'yyyymmddhh24miss') ,
to_number( max(decode(rn,3,col_val)) )
from
(select a.*, row_number() over (partition by row_id order by col_nm) rn
from col_dtl a
where row_id in (select row_id from tbl_dtl where tbl_nm = 'CS_SPL_SITN')
)
group by row_id
);
delete from the table
where the primary key is in...
that subquery is the trick, find all of the rows in tbl-dtl for that table (where row_id in (select row_id from tbl_dtl where tbl_nm = 'CS_SPL_SITN' does that..)
and then pivot them (max(decode()) does the pivot
and coerce them to the proper datatype - to TO_NUMBER/TO_DATE do that.
so, how can we do that from your data:
ops$tkyte%ORA9IR2> create or replace view vw
2 as
3 select 'delete from ' || a.tbl_nm || ' where ( ' ||
4 rtrim(
5 max( decode( b.rn, 1, col_nm ) ) || ',' ||
6 max( decode( b.rn, 2, col_nm ) ) || ',' ||
7 max( decode( b.rn, 3, col_nm ) ) || ',' ||
8 max( decode( b.rn, 4, col_nm ) ) || ',' ||
9 max( decode( b.rn, 5, col_nm ) ), ',' )
10 || ') in ( select ' ||
11 rtrim(
12 max( decode( b.rn, 1, f_of_x ) ) || ',' ||
13 max( decode( b.rn, 2, f_of_x ) ) || ',' ||
14 max( decode( b.rn, 3, f_of_x ) ) || ',' ||
15 max( decode( b.rn, 4, f_of_x ) ) || ',' ||
16 max( decode( b.rn, 5, f_of_x ) ) , ',' ) || ' from (select a.*, row_number()
over (partition by row_id order by col_nm )rn from col_dtl a where row_id in
17 (select row_id from tbl_dtl where tbl_nm = ''' || a.tbl_nm || ''') ) group by
row_id )' stmt
18 from ( select tbl_nm, max(row_id) max_row_id
19 from tbl_dtl
20 group by tbl_nm ) a,
21 (select col_nm,
22 case when col_data_typ_ind = 'N'
23 then 'to_number( max(decode(rn,' || row_number() over (partition
by row_id order by col_nm) || ',col_val)))'
24 when col_data_typ_ind = 'D'
25 then 'to_date( max(decode(rn,'||row_number() over (partition by
row_id order by col_nm)||',col_val)), ''yyyymmddhh24miss'') '
26 else 'max(decode(rn,'||row_number() over (partition by row_id
order by col_nm)||',col_val))'
27 end f_of_x,
28 row_id,
29 row_number() over (partition by row_id order by col_nm) rn
30 from col_dtl) b
31 where a.max_row_id = b.row_id
32 group by a.tbl_nm
33 /
View created.
ops$tkyte%ORA9IR2> select * from vw;
STMT
-------------------------------------------------------------------------------
delete from BV_CLM_RCVR where ( CLM_RCVR_ID,CLM_ID) in ( select to_number( max
(decode(rn,1,col_val))),to_number( max(decode(rn,2,col_val))) from (select a.*,
row_number() over (partition by row_id order by col_nm )rn from col_dtl a wher
e row_id in
(select row_id from tbl_dtl where tbl_nm = 'BV_CLM_RCVR') ) group by row
_id )
delete from CS_SPL_SITN where ( CS_ID,EFF_BGN_DT,SPL_SITN_ID) in ( select max(d
ecode(rn,1,col_val)),to_date( max(decode(rn,2,col_val)), 'yyyymmddhh24miss') ,t
o_number( max(decode(rn,3,col_val))) from (select a.*, row_number() over (parti
tion by row_id order by col_nm )rn from col_dtl a where row_id in
(select row_id from tbl_dtl where tbl_nm = 'CS_SPL_SITN') ) group by row
_id )
delete from ES_STS where ( CS_ID,CWIN,ES_STS_ID,PGM_TYP_CD) in ( select max(dec
ode(rn,1,col_val)),to_number( max(decode(rn,2,col_val))),to_number( max(decode(
rn,3,col_val))),max(decode(rn,4,col_val)) from (select a.*, row_number() over (
partition by row_id order by col_nm )rn from col_dtl a where row_id in
(select row_id from tbl_dtl where tbl_nm = 'ES_STS') ) group by row_id )
delete from ES_STS_RSN where ( CS_ID,CWIN,ES_RSN_ID,ES_STS_ID,PGM_TYP_CD) in (
select max(decode(rn,1,col_val)),to_number( max(decode(rn,2,col_val))),to_numb
er( max(decode(rn,3,col_val))),to_number( max(decode(rn,4,col_val))),max(decode
(rn,5,col_val)) from (select a.*, row_number() over (partition by row_id order
by col_nm )rn from col_dtl a where row_id in
(select row_id from tbl_dtl where tbl_nm = 'ES_STS_RSN') ) group by row_
id )
of course :)
Now you just need to:
ops$tkyte%ORA9IR2> begin
2 for x in ( select * from vw )
3 loop
4 execute immediate x.stmt;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
those deletes of course should have been provided by your "outside vendor" from the get go. but whatever.
Thanks. You are the best.
December 21, 2007 - 6pm Central time zone
Reviewer: kishor salunkhe from San Jose, CA
Great ! ! !
January 4, 2008 - 6am Central time zone
Reviewer: A reader from London