Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    20

    Unanswered: Performance deleting rows

    Hi,

    I'm looking for a way of deleting rows quicker than the delete dml sentence.
    I know truncate sentences, but i don't want to truncate a table or a partition, I want to delete a great number of rows (identified by a where clause), but not a whole partition or table.

    For example, inserting rows i can use insert sentences or i can use sql-loader, Ώis there something similar deleting rows??

    My problem is deleting time.

    Any advice will be greatly apreciatted. Thanks in advance

    Juan

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Depending on how many rows you have to delete, the following should work.

    for example, say you have a table with 10,000,000 rows and you only want to keep the 10,000 with a 'Y' in col2. You could do the following.

    create table junk as
    select * from my_table
    where col2 = 'Y';

    truncate table my_table;

    insert into my_table
    select * from junk;

    drop table junk;

    I have used this method, but please check it out on a test table before using it on production.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2006
    Posts
    20

    what about parallel dml??

    Thanks for all the answers

    What about parallel dml??

    Has anybody work with parallel deletes?? (/*+ PARALLEL (t2,2) */)

  4. #4
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    If you have the setup to perform parallel this will help.

    The problem with the deletes is the logging of them.

    Parallel will help but still beils better solution when dealing with larger amounts of data.

    If the delete is on the primary key this greatly increases performance

Posting Permissions

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