CODEWITHSUNDEEP
sqldatabasedb2sql-deletecomposite-primary-key
Raja
Raja

Reputation: 647

How composite index works ? and what is the efficient way to do data manipulation on composite index

How does composite index work in db2 ? We have a table with data, similar to what I described below. primary key (B,C) 

  Table "D" has Table B's key and Table C's key
   B|C
  -----
   1|1
   1|2
   1|3
   2|4
   2|5
   3|5

Now if I want to delete rows with C column having values in ( 1,2,3 ) will it be efficient if I query like this 

Delete from D where C in ( 1,2,3)

or should I use B column values as well to achieve better performance ?

If I have to explain a little more elaborate, then

Now we have another table D which holds keys of both Table B and Table C.

Here I am trying to delete rows on the basis of Table C's key. If I required to use Table B's key as well, how should I query it ? I have written select query but I had to use C.* to filter table D values. 

SELECT C.Bkey, C.Ckey 
    FROM TableC C 
    INNER JOIN TableB B ON C.id = B.id
    INNER JOIN TableA A ON B.other_id = A.other_id
    WHERE A.other_id = 123 AND Ckey in (1,2,3)

Do we have anything similar for delete ? Or should I use two "IN" clauses ( one for BKey column values and one for Ckey column )

 DELETE FROM TableD 
    WHERE Bkey IN 
     ( 
        SELECT B.Bkey 
          FROM TableB tB 
          INNER JOIN TableA tA
          ON tB.someId = tA.someId
          WHERE someId = 123
      ) AND 
  Ckey IN (1,2,3)

Upvotes: 0

Views: 307

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If you have a composite index with columns (B, C), then the ordering makes a difference. In general, a query only restricting C will not use the index (or will not use it efficiently).

The best situation is an equality condition on B followed by any condition on C.

This is true regardless of whether the logic is in a delete or select.

Upvotes: 1

Related Questions