Wie wirkt sich die IN-Klausel auf die Leistung in Oracle aus?

Question

UPDATE table1 
       SET col1 = 'Y'
     WHERE col2 in (select col2 from table2)

In der obigen Abfrage, stellen Sie sich vor, die innere Abfrage gibt 10000 Zeilen zurück. Hat diese Abfrage mit IN-Klausel Auswirkungen auf die Leistung?

Wenn ja, was kann für eine schnellere Ausführung getan werden?

Answer 1

Wenn die Unterabfrage eine große Anzahl von Zeilen im Vergleich zur Anzahl der Zeilen in TABLE1 zurückgibt, wird der Optimierer wahrscheinlich einen Plan wie diesen erstellen:

--------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |        |   300K|    24M|       |  1581   (1)| 00:0
|   1 |  UPDATE             | TABLE1 |       |       |       |            |
|*  2 |   HASH JOIN SEMI    |        |   300K|    24M|  9384K|  1581   (1)| 00:0
|   3 |    TABLE ACCESS FULL| TABLE1 |   300K|  5860K|       |   355   (2)| 00:0
|   4 |    TABLE ACCESS FULL| TABLE2 |   168K|    10M|       |   144   (2)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COL2"="COL2")

Es werden beide Tabellen einmal gescannt und nur die Zeilen in TABLE1 aktualisiert, die beiden Tabellen gemeinsam sind. Dies ist ein sehr effizienter Plan, wenn Sie viele Zeilen aktualisieren müssen.

Manchmal hat die innere Abfrage nur wenige Zeilen im Vergleich zu der Anzahl der Zeilen in TABLE1. Wenn Sie einen Index auf TABLE1(col2) Sie könnten dann einen ähnlichen Plan wie diesen erhalten:

-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |        |    93 |  4557 |   247   (1)| 00:00:03 |
|   1 |  UPDATE              | TABLE1 |       |       |            |          |
|   2 |   NESTED LOOPS       |        |    93 |  4557 |   247   (1)| 00:00:03 |
|   3 |    SORT UNIQUE       |        |    51 |  1326 |   142   (0)| 00:00:02 |
|   4 |     TABLE ACCESS FULL| TABLE2 |    51 |  1326 |   142   (0)| 00:00:02 |
|*  5 |    INDEX RANGE SCAN  | IDX1   |     2 |    46 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."COL2"="T2"."COL2")

In diesem Fall liest Oracle die Zeilen aus TABLE2 und führt für jede (eindeutige) Zeile einen Indexzugriff auf TABLE1 durch.

Welcher Zugriff schneller ist, hängt von der Selektivität der inneren Abfrage und der Clusterung des Index auf TABELLE1 ab (sind die Zeilen mit ähnlichen Werten von col2 in TABLE1 nebeneinander oder zufällig verteilt?). Wenn Sie diese Aktualisierung durchführen müssen, ist diese Abfrage auf jeden Fall eine der schnellsten Möglichkeiten, dies zu tun.

Answer 2

UPDATE table1 outer
   SET col1 = 'Y'
 WHERE EXISTS (select null
                 from table2
                WHERE col2 = outer.col2)

Das könnte besser sein

Um eine Vorstellung davon zu bekommen, was besser ist, sehen Sie sich den Ausführungsplan an.

Answer 3

Von Oracle:

11.5.3.4 Verwendung von EXISTS versus IN für Unterabfragen

I IN anstelle von EXISTS zu verwenden. In Allgemeinen, wenn das selektive Prädikat in der Unterabfrage steht, dann verwenden Sie IN. Wenn die selektive Prädikat in der übergeordneten Abfrage, dann EXISTS verwenden.

Meiner Erfahrung nach habe ich bessere Pläne gesehen, die EXISTS verwenden, wenn die Unterabfrage eine große Anzahl von Zeilen zurückgibt.

Véase aquí für weitere Diskussionen von Oracle