DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2000
    I've set up a new database on Win2000. This database is to be used live by about atleast 20 users.
    And while trying out inserts and deletes on a table which has at the moment the most amount of data of about 280,000 rows.

    So, just for the testing purpose of my archivelog mode, I gave a delete statement on this table, so that I could try recovering all the data.

    But this delete stmt took about 4minutes to complete. I even assigned a rollback segment solely for this tranx.

    Is this normal , the delete of 280,000 rows taking 4minutes ? or am I just being paranoid?

    Could someone please advice.

    Thanks a lot


  2. #2
    Join Date
    Feb 2000
    Do you have many indexes on the table?

    Each time you delete from the table you also have to delete from the index....

    If you want to delete all rows from a table you are better to use the 'truncate table' command.


  3. #3
    Join Date
    Apr 2001

    The above statement is true (if you have indexes on the tables, it will take more time to delete)
    But, if you want to test your archivelog mode, don't use truncate, because the truncate 'bypasses' the redo logs and don't write them, so, you will be unable to recover...

    For the 4 minutes, I would make a 'lazy dba' answer: it could depends on your system architecture...
    Many points are to check for contention:
    Processor(s), memory, disks speeds, raid configuration, other processes using resources, distribution of your datafiles over disks, fragmentation of your tables/indexes, distribution of indexes/tables/redo log/archived logs over the tablespaces, datafiles and disks, etc...

    Anyway, 4 min for a 280000 rows deletion doesn't make me crazy...

    Hope it helps



  4. #4
    Join Date
    Jul 2001
    Karachi, Pakistan

    Cool Use Partiioning

    Hi Dear,
    1) r u using the partitioning option to ur tables ?
    try to impose partitioning on ur tables ( beware, try to partition the tale on number type column, if available).

    2) for what type of application u r using ur database? i-e for OLTP or DSS environment .... place appropiate type of indexes according to ur application need ...

    3) Also try to impose parallel DML operation (default for ur tables).... by this way u feel some performance improvemnet.

    take care n plz reply me for the same
    Faraz A. Farooqui
    Oracle Consultant
    InfoPak, SBS

  5. #5
    Join Date
    Dec 2000
    Ljubljana, Slovenia
    Originally posted by shadow21
    But, if you want to test your archivelog mode, don't use truncate, because the truncate 'bypasses' the redo logs and don't write them, so, you will be unable to recover...
    This is not true, TRUNCATE is fully logged as any other DDL statement. It is recorded in redo log, so there is no problem with TRUNCATE and database recovery.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Feb 2001
    Paris, France
    exact, truncate "bypasses" Rollback Segments, not redo logs

  7. #7
    Join Date
    Sep 2000
    It's a new compaq server, 1 GHz processor and RAID 1 configuration. We just had this set up recently. We have 9GB internal drive, and the rest 2 18 Gb are housed in the external array cabinet setup as RAID 1.

    There is only one I/O channel into the array cabinet to address this approx. 36G of usable disk space. I'm really doubtful about the whole setup.

    The table I was trying to delete has a composite index. That's all and I'm the only person accessing the database.

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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.