-1

Our workplace has a database with a client table that holds 5 million records. Each time a client is updated, another row is added to a client_history table that holds 100 million records. All columns in the Client table are indexed. Only the Primary Key (ID), Foreign Key (FK_Client_ID) and Creation Timestamp in the Client History table are indexed.

I've been asked to update several hundred thousand client records, but only if the corresponding client history record indicates that the client record has not been updated since a certain date (e.g. 19th September 2012).

I've written an SQL update query that uses an EXISTS clause. I've been told by the DBA's that I shouldn't use an EXISTS clause, as this would trigger a tablespace scan that would slow down execution of the query. This is obviously an issue when updating several hundred thousand client records -

UPDATE Client_History SET Surname = 'MisterX',
 Update_Timestamp = CURRENT_TIMESTAMP 
 WHERE (FK_Client_ID = 123 AND ID = 456)
 AND NOT EXISTS
 (SELECT * 
 FROM Client
 WHERE Client.Client_Id = Client_History.FK_Client_ID
 AND Client_History.Update_Timestamp > TIMESTAMP('2012-09-21-00:00:00')
 AND Client_History.Update_Timestamp < TIMESTAMP('4000-12-31-00:00:00')
 AND Client_History.Creation_Timestamp < NAME.Update_Timestamp);

Can anyone think of a better solution?

Spacehamster
  • 195
  • 3
  • 13
  • Have you looked at the execution plan to see if it is doing a full table scan? I would expect it to use the index instead. – Gordon Linoff Mar 04 '13 at 00:17
  • We've decided it would be a lot easier remove the EXISTS clause from the SQL update query, and develop another program to check whether records have been updated since a certain date. We run the program first to remove any updated records, then we run the SQL queries straight afterwards to update the clients. – Spacehamster Mar 05 '13 at 23:53

1 Answers1

0

A shot in the dark: try hoisting all the constants up into the main query (where they belong)

UPDATE Client_History ch
 SET Surname = 'MisterX'
     , Update_Timestamp = CURRENT_TIMESTAMP 
 WHERE ch.FK_Client_ID = 123
   AND ch.ID = 456
   AND ch.Update_Timestamp > TIMESTAMP('2012-09-21-00:00:00')
   AND ch.Update_Timestamp < TIMESTAMP('4000-12-31-00:00:00')
   AND ch.Creation_Timestamp < NAME.Update_Timestamp
   AND NOT EXISTS (
     SELECT * 
     FROM Client cl
     WHERE cl.Client_Id = ch.FK_Client_ID
     )
   ;

BTW: what is NAME ? Some kind of pseudo table, like Oracle's dual ?

wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • Sorry - "NAME." should be replaced with "ch.". So that clause should read "ch.Creation_Timestamp < NAME.Update_Timestamp". – Spacehamster Mar 04 '13 at 22:34
  • Not cl.update_timestamp? Then it can stay in the main query. ITYM: `ch.Creation_Timestamp < ch.Update_Timestamp` ? – wildplasser Mar 04 '13 at 22:39