DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: delete from long raw column

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    94

    delete from long raw column

    I've got a table that has 4 million rows in it.
    It has 2 columns an id and a description(long raw)

    i'm trying to delete some rows where the id doesnt exist in another table and its fallen over after 9 hours after blowing the undo tablespace (8GB).

    I presume that its due to the delete on the LONG RAW column on the table as the select come back in 20 seconds.

    Any ideas on how to speed this up?

    thanks

  2. #2
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    count first how many rows u r deleting dear
    Behind The Success And Failure Of A Man Is A Woman

  3. #3
    Join Date
    Nov 2000
    Location
    London
    Posts
    94
    there you are dear...

    SQL> select count(*) from B46C18100 where entryid not in (select c1 from t46);

    COUNT(*)
    ----------
    1177168


    SQL> select count(*) from b46c18100;

    COUNT(*)
    ----------
    4099705

    SQL> desc b46c18100
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    ENTRYID NOT NULL VARCHAR2(15)
    C18100 LONG RAW

  4. #4
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    so u want to delete 1+ million?
    you can delete by range of by rownum

    or make a pl/sql code to delete by row and commit;
    select cursor1_for_delete from B46C18100 where entryid not in (select c1 from t46);
    while true
    delete from B46C18100 where rowid=:rowid
    Behind The Success And Failure Of A Man Is A Woman

  5. #5
    Join Date
    Nov 2000
    Location
    London
    Posts
    94
    ok thanks..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width