OraFAQ Forum: SQL & PL/SQL » problem in deleting records from a table.

Home » SQL & PL/SQL » SQL & PL/SQL » problem in deleting records from a table.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem in deleting records from a table. [message #8178]

Fri, 01 August 2003 01:53

Zia Khattak
Messages: 22
Registered: July 2003

Junior Member

I have problem in deleting records from a table.

Layout of mismst
MI_CON_NO NUMBER(11)
MI_NAME VARCHAR2(25)
MI_ADR_1 VARCHAR2(25)
MI_ADR_2 VARCHAR2(25)
MI_ADR_3 VARCHAR2(25)
MI_ADR_4 VARCHAR2(25)
MI_METR_STS CHAR(1)
MI_DEP_NO NUMBER(11)
MI_METR_NO VARCHAR2(10)

In my master table(mismst) records of different regions consumers are identified by different numbers.

like:
1 for region1
2 for region2
3 for region3
4 for region4
5 for region5
6 for region6

etc.

1st numeric of mi_con_no represent region
and in every region there are million records.
now the problem is i want to delete all records in region 6.

my query is :

DECLARE
CURSOR C1 IS SELECT * FROM MISMST WHERE SUBSTR(MI_CON_NO,1,1) = '6';
CNTR NUMBER(8);
BEGIN
FOR A IN C1 LOOP
BEGIN

CNTR := C1%ROWCOUNT;
DELETE FROM MISMST WHERE MI_CON_NO = A.MI_CON_NO;

IF MOD(CNTR,100) = 0 THEN
UPDATE TMP_COUNTER SET COUNT = CNTR;
COMMIT;
END IF;
END;
END LOOP;
UPDATE TMP_COUNTER SET COUNT = CNTR;
COMMIT;
END;
/

but response of this cursor query is slow.

plz help me if you have another better idea.

Report message to a moderator

Re: problem in deleting records from a table. [message #8179 is a reply to message #8178]

Fri, 01 August 2003 02:25

Maaher
Messages: 7065
Registered: December 2001

Senior Member

How about this?

begin
  loop
    delete from big_table
     where substr(id,1,1) = '6'
       and rownum < 100;
    exit when sql%ROWCOUNT=0;
    commit;
  end loop;
end;

Concerning the commits, this generates a lot of overhead. This is what Tom Kyte has to say about it.
HTH,
MHE

Report message to a moderator

Re: problem in deleting records from a table. [message #8180 is a reply to message #8178]

Fri, 01 August 2003 09:52

sverch
Messages: 582
Registered: December 2000

Senior Member

DELETE FROM mismst WHERE MI_CON_NO ='6';

Report message to a moderator

Re: problem in deleting records from a table. [message #8183 is a reply to message #8180]

Fri, 01 August 2003 17:30

Maaher
Messages: 7065
Registered: December 2001

Senior Member

It is the most obvious choice, but taking into consideration the amount of data I would rather follow the advice of Tom Kyte in my previous reply.

MHE

BTW: don't forget the SUBSTR part.

Report message to a moderator

Re: problem in deleting records from a table. [message #8185 is a reply to message #8179]

Sat, 02 August 2003 01:42

Zia Khattak
Messages: 22
Registered: July 2003

Junior Member

Thanks a lot Maaher! you are absolutly right.

zia

Report message to a moderator

Previous Topic:	Compare two .CSV files
Next Topic:	Retrieving records from a Table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 00:44:02 CDT 2024