Dear Nice People...

I have a relatively simple UPDATE statement and for some reason it is
quite slow. This is it:

UPDATE TC_CS_JOIN j SET LOCK_TIME=? WHERE FK_TERMINAL_CLIENT_ID =
(SELECT PK_ID FROM TERMINAL_CLIENT tc WHERE tc.EXPIRES > ? AND
tc.SIP_URL=?)

The first param is a timestamp, the second is a string. There are
indexes on EXPIRES, SIP_URL and FK_TERMINAL_CLIENT_ID. The execution
plan is:

Operation Object
UPDATE STATEMENT () <null>
UPDATE () TC_CS_JOIN
INDEX (RANGE SCAN) TC_CS_JOIN_FK_TC_ID
TABLE ACCESS (BY INDEX ROWID) TERMINAL_CLIENT
INDEX (RANGE SCAN) TC_EXPIRES_IDX

which looks really good don't you think? I'm getting here execution
times of 100s of milliseconds!!! That's way too slow.

Let me know what you think.

Yours,
Emmanuel

Answering my own question...

I have no idea why this statement was slow in the first place. But I
was able to make it faster by splitting it into 2:

1- SELECT PK_ID FROM TERMINAL_CLIENT tc WHERE tc.EXPIRES > ? AND
tc.SIP_URL=?

which returns a single key, then I pass it to:

2- UPDATE TC_CS_JOIN j SET LOCK_TIME=? WHERE FK_TERMINAL_CLIENT_ID = ?

This is now more than 10 times faster.

Yours,
Emmanuel

emmanuel_proulx (AT) hotmail (DOT) com (Emmanuel Proulx) wrote in message news:<f06c5840.0404051538.6fdcda82 (AT) posting (DOT) google.com>...

It is likely the type of index that your using I would suggest you do what
I'm going to do. Review the different indexes and when they will slow down
one setup and speed up another.

----- Original Message -----
From: "Emmanuel Proulx" <emmanuel_proulx (AT) hotmail (DOT) com>
Newsgroups: comp.database.oracle
Sent: Monday, April 05, 2004 7:38 PM
Subject: Slow Update, simple?