CODEWITHSUNDEEP
informix
Roopesh Majeti
Roopesh Majeti

Reputation: 554

Deletion of rows from Informix Database

I have around 3 Million rows in a Table in Informix DB. We have to delete it, before loading new data. It has a primary key on one of its columns. For deleting the same, I thought of going with rowid usage. But when I tried 

select rowid from table

it responded with -857 error [Rowid does not exist].

So, I am not sure, how to go with the deletion. I prefer not going with primary key, as deletion with primary key is costly compared with rowid deletion.

Any suggestion on the above would be helpful.

Upvotes: 2

Views: 4480

Answers (2)

Joe R.
Joe R.

Reputation: 2042

I'm assuming this is IDS?.. How many new rows will be loaded and how often is this process repeated?.. Despite having to re-establish referential constraints and views, in my opinion, it is much better to drop the table, create it from scratch, load the data and then create the indexes because if you just delete all the rows, the deleted rows still remain physically in the table with a NULL \0 flag at the end of the row, thus the table size will be even larger when loading in the new rows and performance will suffer!.. It's also a good opportunity to create fresh indexes, and if possible, pre-sort the load data so that its in the most desirable order (like when creating a CLUSTERED INDEX). If you're going to fragment your tables on expressions or other type, then ROWID's go out the window, but use WITH ROWIDS if you're sure the table will never be fragmented. If your table has a serial column, are there any other tables using the serial columns as a foreign key?

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753665

If you get error -857, the chances are that the table is fragmented, and was created without the WITH ROWIDS option.

Which version of Informix are you using, and on which platform?

The chances are high that you have the TRUNCATE TABLE statement, which is designed to drop all the rows from a table very quickly indeed.

Failing that, you can use a straight-forward:

DELETE FROM TableName;

as long as you have sufficient logical log space available. If that won't work, then you'll need to do repeated DELETE statements based on ranges of the primary key (or any other convenient column).

Or you could consider dropping the table and then creating it afresh, possible with the WITH ROWIDS clause (though I would not particularly recommend using the WITH ROWIDS clause - it becomes a physical column with index instead of being a virtual column as it is in a non-fragmented table). One of the downsides of dropping and rebuilding a table is that the referential constraints have to be reinstated, and any views built on the table are automatically dropped when the table is dropped, so they have to be reinstated too.

Upvotes: 2

Related Questions