Delete ignores index on primary key

[Former Member]

In one of our tables a delete-statement ignores the primary key index on that table. In any other table a delete is executed at no time but in that table a delete takes about 14 seconds (The table has about 7,000,000 rows). A select on that table using the primary key in the where clause uses the index on the PK.

Aka:

select * from table where id = 1 --> 30 ms

delete from table where id = 1 --> 14 sec

Any ideas?

[Former Member · Accepted solution]

Hi,

did you forget to tell about referential integrity for this table?

Or did you forget to mention some kind of trigger?

Before doing the delete you should do some select * from lockstatistics to see, if there are locks on that table which may prevent the delete to work as fast as expected.

Elke

[Former Member · Accepted solution]

HI, Elke,

hi Lars,

blame on me.

I've lots (> 20) of table definitions like that:

create table clntcotab502 (
        id fixed(19,0) not null,
        conditionName varchar(6) null,
        validFrom date null,
        validTo date null,
        cv_id fixed(19,0) null,
        clientId varchar(255) null,
        salesOrderTypeName varchar(255) null,
        primary key (id)
    );

   
    create index IDX_001 on clntcotab502 (clientId, conditionName, salesOrderTypeName);

    alter table clntcotab502 
        foreign key FK_clntcotab502_coval (cv_id) 
        references sd_pr_coval;

After adding an index on cv_id the delete executes in about 60ms.

Thanks for your help.

Regards

Dirk

[lbreddemann]

>

> In one of our tables a delete-statement ignores the primary key index on that table. In any other table a delete is executed at no time but in that table a delete takes about 14 seconds (The table has about 7,000,000 rows).  A select on that table using the primary key in the where clause uses the index on the PK. 
> 
> Aka:
> 
> select * from table where id = 1  --> 30 ms
> delete from table where id = 1 --> 14 sec
> 
> Any ideas?

On MaxDB you don't have indexes on the primary key.

The table B*Tree is your primary key!

Could it be that your real primary key consists of more than just the id field and you have an additional index on ID?

Could it be that somebody locked the entry to be deleted?

How many entries do have an id = 1 ?

And not to forget: what version of MaxDB are we talking about?

Post something real here. Table defintion, test data, real statements... that way we can reproduce what you tell us.

regards,

Lars

[Former Member]

Hi Lars,

thanks for your quick reply.

We are using SAPDB 7.6.05, Build 009-123-191-997

The table definition is as follows:

create table "CORE"."SD_PR_COVAL"(

"ID" FIXED (19) not null,

"CALCULATIONTYPE" INTEGER,

"CONDITIONNAME" VARCHAR (4) ASCII,

"PRICINGUNIT" FLOAT (20) not null,

"SCALEBASE" INTEGER,

"SCALETYPE" INTEGER,

"AMOUNT" FIXED (19),

"CURRENCY" VARCHAR (255) ASCII,

primary key ("ID"))

sample 20000 rows

There are no indexes on that table.

Doing an insert followed by a delete i get the following results:

select * from sd_pr_coval where id = 1

Statement 'select * from sd_pr_coval where id = 1' successfully executed in 0 ms. - No result

insert into sd_pr_coval values(1, 1, 'test', 1, 1, 1, 1, 'EUR')

Statement 'insert into sd_pr_coval values(1, 1, 'test', 1, 1, 1, 1, 'EUR')' successfully executed in 47 ms. - Rows Affected: 1

select * from sd_pr_coval where id = 1

Statement 'select * from sd_pr_coval where id = 1' successfully executed in 0 ms.

--> Gives one result row

delete from sd_pr_coval where id = 1

Statement 'delete from sd_pr_coval where id = 1' successfully executed in 19.359 seconds. - Rows Affected: 1

[lbreddemann]

>

> delete from sd_pr_coval where id = 1
> Statement 'delete from sd_pr_coval where id = 1' successfully executed in 19.359 seconds.  - Rows Affected: 1

Sorry but I cannot reproduce this...

create table "SD_PR_COVAL"(
	"ID" FIXED (19) not null,
	"CALCULATIONTYPE" INTEGER,
	"CONDITIONNAME" VARCHAR (4) ASCII,
	"PRICINGUNIT" FLOAT (20) ,
	"SCALEBASE" INTEGER,
	"SCALETYPE" INTEGER,
	"AMOUNT" FIXED (19),
	"CURRENCY" VARCHAR (255) ASCII,
primary key ("ID"))
sample 20000 rows

--> Just for this test purpose I removed the NOT NULL und pricing unit!

Let's load some test data:

insert into sd_pr_coval (ID) (select rowno from tables a, tables b)

Statement 'insert into sd_pr_coval (ID) (select rowno from tables a, tables b)' successfully executed in 27.081 seconds 
Started: 2010-10-05 18:46:40 - Rows Affected: 281.961 

--> Yes, the INSERT took long (I had to fix a log full situation first.... )

Now let's see:

select count(*) from  sd_pr_coval

281.961 


 'select * from sd_pr_coval where id = 1' successfully executed in 2 ms 

--> straight from the cache!

'delete from sd_pr_coval where id = 1' successfully executed in 3 ms  - Rows Affected: 1 

--> WOW 😉 one ms more !
--> but hey, we maybe did perform some tree balancing stuff, so that should be OK...

As you see, there must be something else happening when you run your delete statement.

Is any other transaction open and reads the row? Depending on your isolation level this might lead to a locking situation where your delete needs to wait!

regards,

Lars

[Former Member]

Hi Lars,

is there any way to see lock holders in sapdb?

Regards,

Dirk

[Former Member]

Hi Lars,

i've used the resource monitor to get more information. After clearing old data i executed one delete and after that the following select:

select sql_statement, session, call_count, rows_read, rows_qual, runtime, min_runtime, max_runtime

from syscmd_analyze, sysdata_analyze

where syscmd_analyze.cmdid = sysdata_analyze.cmdid

order by runtime desc:

and i got:

SQL_STATEMENT;SESSION;CALL_COUNT;ROWS_READ;ROWS_QUAL;RUNTIME;MIN_RUNTIME;MAX_RUNTIME

DELETE FROM SD_PR_COVAL WHERE ID=?;0001B9F2;1;1356710;1;14.842863;14.842863;14.842863

So for one qualified row to delete the kernel read 1.3 million rows from table sd_pr_coval. Looks like a table scan for that delete. Is this the expected behaviour or do i have some bigger problem?

Regards,

Dirk

[lbreddemann]

Definitively something bigger...

There must be something else about your table you haven't told us yet - as you can see in my example the "normal" behavior is to use the table b*tree.

Can you provide a backup of this database?

Or can you try to export/import it and reproduce the behavior?

If so, can you send us the export files?

Or (even simpler) if you're an SAP customer: please open a support message for this!

regards,

Lars

[lbreddemann]

> is there any way to see lock holders in sapdb?

SAP DB is dead.

It's SAP MaxDB today

And, sure, there is an even easy way.

Systemtables [LOCKS|http://maxdb.sap.com/doc/7_6/28/0d883cb0bd2019e10000000a114084/content.htm] will deliver all required information.

BTW: you may be interested in the internals couse material and the expert sessions for MaxDB available for free at [http://maxdb.sap.com/training].

regards,

Lars