Re: performance question

On Tue, 08 Feb 2005 21:02:19 +0100, Jan van Veldhuizen wrote:

> What will be better?
>
> UPDATE table1
> SET table1.some_col =
> (SELECT some_col FROM table2
> WHERE table2.col_id = table1.col_id)
> WHERE EXISTS
> (SELECT null FROM table2
> WHERE table2.col_id = table1.col_id)
>
> or:
>
> CREATE VIEW my_view AS SELECT table1.some_col c1, table2.some_col c2
> WHERE table1.col_id = table2.col_id
>
> UPDATE my_view SET c1 = c2

Option 3: the Merge statement.

Otherwise, in no uncertain terms, option 1 will always be better - except when it isn't.

You have provided about '0' information about the characteristics of the environment. Except for 'rules of thumb', appropriately abbreviated ROT, there is no reason to believe one is better than the other (barring Sybrand's assertion that the VIEW is not updateable and that is likely true in this case).

SOme factors that may come into play include relative sizes of tables, whether you are using Cost Based Optimizer and what version you are using.

lol/FGB Received on Tue Feb 08 2005 - 14:53:50 CST