Hi!

 

I need to delete a considerable number of rows from a table, and it is taking too long.  The operation takes equally long from ij, so I think it is independent of my application code.

 

So for instance in the case below, it took about 23 seconds to execute the delete statement, which was deleting 156112 rows out of 380600:

 

ij> select count(*) from listhit;

1

-----------

380600

 

ij> delete from listhit where listresultid = 11501573;

156112 rows inserted/updated/deleted

 

23 seconds

 

The definition of the table is as follows:

 

create table ListHit (

    LISTRESULTID int,

    INDX int,

    HIT blob,

    TAG varchar(250),

    ATTS blob );

 

CREATE INDEX listhit_listresultid ON listhit(listresultid);

CREATE INDEX listhit_listresultid_indx ON listhit(listresultid,indx);

 

And from ij:

 

ij> describe listhit;

COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&

------------------------------------------------------------------------------

LISTRESULTID        |INTEGER  |0   |10  |10    |NULL      |NULL      |YES

INDX                |INTEGER  |0   |10  |10    |NULL      |NULL      |YES

HIT                 |BLOB     |NULL|NULL|21474&|NULL      |NULL      |YES

TAG                 |VARCHAR  |NULL|NULL|250   |NULL      |500       |YES

ATTS                |BLOB     |NULL|NULL|21474&|NULL      |NULL      |YES

 

5 rows selected

 

ij> show indexes;

TABLE_NAME   |COLUMN_NAME  | NON_U&|TYPE|ASC&|CARDINA&|PAGES

----------------------------------------------------------------------------------------

LISTHIT                |LISTRESULTID          |1     |3   |A   |NULL    |NULL

LISTHIT                |LISTRESULTID          |1     |3   |A   |NULL    |NULL

LISTHIT                |INDX                           |1     |3   |A   |NULL    |NULL

 

 

Any ideas?

 

Thanks for your help,

 

Monica