Deletes , inserts taking a long time

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

pst

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.

Cheers
Moff.

Hi,

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

Cheers

Fabien

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

Quote:

---

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.

exact, truncate "bypasses" Rollback Segments, not redo logs

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.