關於UPDATE中關聯查詢的執行時間考慮

update  testj3
   set object_id = (select USER_ID
                      from testj2
                     where testj3.owner = testj2.username)


考慮如上的語句，如何估算其時間，實際上這個語句不管怎麼樣都會更新所有的行，
匹配的行更新為相應的值，不匹配的則更新為NULL。
同時其中包含了內聯子查詢，其執行時間受到查詢時間的影響有著巨大的差別。其
方法類似於NEST LOOP，如下：
---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE            | TESTJ3 |      1 |        |      0 |00:00:00.01 |      92 |
|   2 |   TABLE ACCESS FULL| TESTJ3 |      1 |     29 |     29 |00:00:00.01 |       3 |
|*  3 |   TABLE ACCESS FULL| TESTJ2 |     29 |      1 |     28 |00:00:00.01 |      87 |
---------------------------------------------------------------------------------------
其中這裡我的我的TESTJ3中有29個不同的值，所以這裡被驅動表TESTJ2被驅動了29次。
注意這裡29是TESTJ3中OBJECT_ID不同的值。而不是行數
如果TESTJ4表中有192條記錄但是不同的值只有3個會怎麼樣呢？
SQL> select count(*) from testj4;
 
  COUNT(*)
----------
       192 
SQL> select distinct(object_id) from testj4;
 
 OBJECT_ID
----------
        63
        58
        60
執行計劃將會如下：
SQL_ID  chyutr057uqv8, child number 0
-------------------------------------
update /*+  gather_plan_statistics */ testj4    set object_id = (select
USER_ID                       from testj2                      where
testj4.owner = testj2.username)


Plan hash value: 1040199981


---------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   1 |  UPDATE            | TESTJ4 |      1 |        |      0 |00:00:00.01 |      20 |
|   2 |   TABLE ACCESS FULL| TESTJ4 |      1 |    192 |    192 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| TESTJ2 |      3 |      1 |      3 |00:00:00.01 |       9 |
---------------------------------------------------------------------------------------
可以看到實際只是驅動了3次被驅動表而已，試想如果TESTJ2表巨大，同時TEST4中有著很多的不同值
那麼效率可想而知。
當然類似NEST LOOP其被驅動表中如果加入索引也就是這裡的TESTJ2表的username欄位那麼效率當然會
有很大的提高。
 如下：
 PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  40nt9qhbr7jau, child number 0
-------------------------------------
update /*+  gather_plan_statistics */ testj4    set object_id = (select USER_ID
            from testj2                      where testj4.owner = testj2.username)


Plan hash value: 877285848


--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   1 |  UPDATE                      | TESTJ4  |      1 |        |      0 |00:00:00.01 |      18 |


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS FULL          | TESTJ4  |      1 |    192 |    192 |00:00:00.01 |       7 |
|   3 |   TABLE ACCESS BY INDEX ROWID| TESTJ2  |      3 |      1 |      3 |00:00:00.01 |       5 |
|*  4 |    INDEX RANGE SCAN          | TEST_JI |      3 |      1 |      3 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------
總結一下，
類似
update  testj3
   set object_id = (select USER_ID
                      from testj2
                     where testj3.owner = testj2.username)
這樣的UPDATE，需要考慮到UPDATE本身的時間，同時查詢時間將會是需要考慮的另一個重點，
關於內層表被驅動的次數和驅動表中關聯欄位的DISTINCT值密切相關，同時建議內層表關聯欄位
最好使用索引。
其實這樣的列子還有很多。比如INSERT INTO SELECT 需要考慮的時間同樣如此。