Performance enhancing of correlated query?

[OLAPer]

In a correlated sub query is it better to use EXISTS or IN?

Is it dependent on whether or not an index exists?

Here is an example:

UPDATE TABLE1 A SET A.COL1 = (SELECT B.COL2
FROM
TABLE2 B
WHERE
A.COLX = B.COLY)
WHERE EXISTS (SELECT B.COL2
FROM
TABLE2 B
WHERE
A.COLX = B.COLY);

The UPDATE WHERE is used to limit the update so that NULL is not placed attempted to be updated where there are matching records in A but not in B.

Assistance appreciated !

OLAPer
:

 

[EdVassie]

For your situation, I would normally use
UPDATE TABLE1 A SET A.COL1 = (SELECT B.COL2
FROM
TABLE2 B
WHERE
A.COLX = B.COLY)

It would be worthwhile to use Visual Explain or similar to check access paths and run both versions a few times to check performance. However, I would expect the above code to both the clearest code for whoever maintains the application in the future and perform as well as any other alternative.

Using EXISTS can give better performance in a NON_CORRELATED subquery if no index is present. For a correlated sub-query with the requirements you have it should have no benefits, and may marginally slow the query by the need to run the code for the EXISTS logic. This posting is a personal opinion only, and may not reflect reality.

 

[OLAPer]

EdVassie

Thanks for your post.
The reason for the EXISTS is to ensure that only records that have a relevant record in table2 are updated. The reason for this is that otherwise a NULL is written; the column I am updating is 'NOT NULL' !

I will try to obtain some metrics for the queries, I was concerned about introducing an Index if the advantages in the UPDATE case presented were marginal, if not negative!!

Thanks again

OLAPer
: