topic Re: Shell script for delteing rows in oracle DB table in Operating System - HP-UX

Shell script for delteing rows in oracle DB table

Inter_1 — Fri, 28 Nov 2008 21:05:12 GMT

Hi,

I am trying to create shell script to do the followings, but I am not good in shell scriting yet.

1. Connect to an oracle DB
2. Delete every 5000 rows and commit table TBL_TEMP_TAKS, based on TASK_ID column.

It should find the MIN number on TAKS_ID columns and MAX number.
Then should delete MIN+5000 till MIN = MAX

I apprecitae your help.

Thanks
Andy

Re: Shell script for delteing rows in oracle DB table

Hein van den Heuvel — Sat, 29 Nov 2008 16:07:45 GMT

why?

If you need to delete all rows from a table, then you should use "TRUNCATE TABLE".

To delete multiple records from a table your best bet is probably still to find a way to express all records to be deleted and execute and commit that 1 statement.

While commiting 5000 deletes is faster than 5000 times commiting one record, commiting all deletes is faster still and should be used until you can explain why that is not good enough.

SQLplus does not like / do 'counting'.
You pretty much need PL/SQL for that.
Things like "rownum < 5000" only SUGGEST it is counting... it is still finding all candidates first.

You may want to check out the new (10.2)
COMMIT options like WRITE BATCH and NOWAIT.
http://oratips-ddf.blogspot.com/2008/02/dreaded-ora-01555.html
commit work write batch nowait

And for PL/SQL there is "BULK COLLECT "

http://www.oracle.com/technology/oramag/oracle/03-sep/o53asktom.html
Bulk Up Your Processing

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876

hth,
Hein.

Re: Shell script for delteing rows in oracle DB table

Hein van den Heuvel — Sat, 29 Nov 2008 20:50:00 GMT

Now, If I _had_ to chop this up in X-row sub queries, then I would use a temporary table to get a predictable, stable, result.

First do something like:

select TAKS_ID from
(select rownum x, TAKS_ID from
(select TAKS_ID from TBL_TEMP_TAKS order by TAKS_ID)
)
where mod(x,5000)=0;

Next, use your shell or perl script to select an entry and the next from the temp table to execute and commit:
DELETE TBL_TEMP_TAKS WHERE TAKS_ID BETWEEN ... AND ...;

You may want to add the first and/or last values through SQL or hardcoded in the script.

Using SQL you could use a 'nice':

select MIN(TAKS_ID) from TBL_TEMP_TAKS
union all
select TAKS_ID from
(select rownum x, TAKS_ID from
(select TAKS_ID from TBL_TEMP_TAKS order by TAKS_ID)
)
where mod(x,5000)=0;
union all
select MAX(TAKS_ID) from TBL_TEMP_TAKS

Or a brute-force:

select 0 "TAKS_ID" from dual;
union all
select TAKS_ID from
(select rownum x, TAKS_ID from
(select TAKS_ID from TBL_TEMP_TAKS order by TAKS_ID)
)
where mod(x,5000)=0;
union all
select 99999999 "TAKS_ID" from dual;

Good luck,
Hein.

Re: Shell script for delteing rows in oracle DB table

Yogeeraj_1 — Tue, 02 Dec 2008 05:47:22 GMT

hi Andy,

Question: why use shell script when you can do the same thing using a scheduled job? (DBMS_JOB or DBMS_SCHEDULER)

You can simply write a procedure and schedule it to run at specific interval and do the purge operation accordingly.

if you need any assistance on this, please do let us know

kind regards
yogeeraj