Optimierungsmethode der Oracle-Tabellenverbindungsmethode (mit Beispielen)

In diesem Artikel geht es um die Optimierungsmethode der Oracle-Tabellenverbindung (mit Beispielen). Freunde in Not können sich darauf beziehen.

In der Oracle-Datenbank gibt es vier Tabellenverbindungsmethoden zwischen zwei Tabellen: Sortier-Merge-Join, Nested-Loop-Join, Hash-Join und kartesischer Join

1 🎜>

Sort-Merge-Join ist eine Tabellenverbindungsmethode, die den Sortiervorgang (SORT) und den Zusammenführungsvorgang (MERGE) verwendet, um den Verbindungsergebnissatz zu erhalten, wenn zwei Tabellen verbunden sind Wenn die T2-Tabelle beim Herstellen von Tabellenverbindungen eine Sortier-Zusammenführungs-Verbindung verwendet, führt Oracle die folgenden Schritte nacheinander aus:

a Greifen Sie mit der im Ziel-SQL angegebenen Prädikatbedingung zu und greifen Sie dann auf die Ergebnisse zu Sortiert nach der Verbindungsspalte der t1-Tabelle, und der sortierte Ergebnissatz wird als s1 aufgezeichnet

b Greifen Sie mit der im Ziel-SQL angegebenen Prädikatbedingung auf die t2-Tabelle zu und sortieren Sie dann die Zugriffsergebnisse danach In der Verbindungsspalte der T2-Tabelle wird die sortierte Ergebnismenge als s2 aufgezeichnet Die Vorteile, Nachteile und Anwendung des Sortier-Merge-Verbindungsszenarios:

a. Normalerweise ist der Effekt des Hash-Joins besser als der des Sortier-Merge-Joins. Wenn die Zeilenquelle jedoch sortiert wurde, ist keine erneute Sortierung erforderlich Beim Ausführen eines Sortier-Merge-Joins ist die Leistung des Sortier-Merge-Joins besser als der eines Hash-Joins.

b >1) RBO-Modus

2) Ungleichwert-Join (>,<,>=,<=)

3) Wenn Hash-Join deaktiviert ist (_HASH_JOIN_ENABLED=false)

Beispiel

SQL> select * from scott.emp t1,scott.emp t2 where t1.empno > t2.mgr;

89 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3950110903

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    62 |  4712 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                   |        |    62 |  4712 |     6  (17)| 00:00:01 |
|   2 |   SORT JOIN                   |        |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   532 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  5 |   SORT JOIN                   |        |    14 |   532 |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL          | EMP    |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))
       filter(INTERNAL_FUNCTION("T1"."EMPNO")>INTERNAL_FUNCTION("T2"."MGR"))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       6612  bytes sent via SQL*Net to client
        575  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         89  rows processed

SQL>

2. Nested-Loops-Join (Nested-Loops-Join)

Ein Nested-Loops-Join ist eine Art Zwei-Tabellen-Verbindung, die auf zwei Ebenen verschachtelter Schleifen beruht ( äußere Schleife/innere Schleife) So erhalten Sie die Tabellenverbindungsmethode des Verbindungsergebnissatzes

Wenn die T1-Tabelle und die T2-Tabelle beim Herstellen der Tabellenverbindung eine verschachtelte Schleifenverbindung verwenden, führt Oracle die folgenden Schritte aus Reihenfolge:

a. Zuerst bestimmt der Optimierer, wer in t1 und t2 die treibende Tabelle ist. Die treibende Tabelle wird für die äußere Schleife und die getriebene Tabelle verwendet wird für die Speicherschleife verwendet. Angenommen, t1 ist die treibende Tabelle

b. Greifen Sie mit der im Ziel-SQL angegebenen Prädikatbedingung zu und erhalten Sie die Ergebnismenge s1

c Tabelle t2 gleichzeitig, dh herausnehmen Die Datensätze in s1 werden entsprechend den Verbindungsbedingungen mit der gesteuerten Tabelle t2 abgeglichen. Abschließend wird die Ergebnismenge zurückgegeben

Vorteile, Nachteile und anwendbare Szenarien der Nested-Loop-Verbindung:

a Kann eine schnelle Reaktion erzielen, das heißt, es kann die verbundene Verbindung zurückgeben und erfüllt die Anforderungen beim ersten Mal. Bedingte Datensätze, ohne auf den Abschluss aller Verbindungsvorgänge warten zu müssen, bevor das Verbindungsergebnis zurückgegeben wird

B Die Anzahl der Datensätze im treibenden Ergebnissatz ist gering, und gleichzeitig gibt es in der Verbindung der getriebenen Tabelle einen eindeutigen Index für die Spalte (oder einen nicht eindeutigen Index mit guter Selektivität für die verbundene Spalte der gesteuerte Tabelle)

Beispiel

SQL> select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  dcsf9m1rzzga5, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_nl(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno

Plan hash value: 4192419542

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      32 |
|   1 |  NESTED LOOPS      |      |      1 |     14 |     14 |00:00:00.01 |      32 |
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      4 |      4 |     14 |00:00:00.01 |      25 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
- filter("T1"."DEPTNO"="T2"."DEPTNO")
rows selected.

SQL>

3. Hash-Join

Hash-Join ist eine Tabellenverbindungsmethode, die auf einer Hash-Operation basiert, um das Verbindungsergebnis zu erhalten Wird festgelegt, wenn zwei Tabellen verbunden sind.

Hash-Join funktioniert, indem eine Tabelle (normalerweise eine kleinere Tabelle) gehasht und in einer Hash-Liste gespeichert wird, Datensätze aus einer anderen Tabelle extrahiert und eine Hash-Operation durchgeführt wird , und Suchen des entsprechenden Werts in der Hash-Liste für den Abgleich

Hash-Join gilt nur für CBO und kann nur für Equi-Join-Bedingungen verwendet werden

Hash-Join ist Sehr gut geeignet zum Verbinden kleiner und großer Tabellen, insbesondere wenn die Selektivität der Verknüpfungsspalten in einer kleinen Tabelle sehr gut ist, kann die Ausführungszeit eines Hash-Joins ungefähr als äquivalent mit der Zeit angesehen werden, die für einen vollständigen Tabellenscan von a aufgewendet wird große Tabelle

Haha Bei der Durchführung einer Hash-Verbindung kann die dem Treiberergebnissatz entsprechende Hash-Tabelle vollständig im Speicher (PGA-Arbeitsbereich) untergebracht werden. Zu diesem Zeitpunkt ist die Ausführungseffizienz der Hash-Verbindung sehr hoch

Das Leistungsproblem der Hash-Verbindung kann durch das 10104-Ereignis zur Diagnose gelöst werden. Die entsprechenden Anweisungen lauten wie folgt:

Anzahl der In-Memory-Partitionen (kann sich geändert haben): Hash-Partition
Endgültige Anzahl der Hash-Buckets: Hash-Bucket-Nummer

Buckets insgesamt: Leere Buckets: Nicht leere Buckets: Die Situation leerer Datensätze und nicht leerer Datensätze im Hash-Bucket

Gesamtzahl der Zeilen: Die Anzahl der Datensätze Steuern des Ergebnissatzes

Maximale Anzahl von Zeilen in einem Bucket: Die im Hash-Bucket enthaltenen Datensätze mit der größten Anzahl von Datensätzen. Anzahl von

Deaktivierte Bitmap-Filterung: Ob die Bitmap-Filterung aktiviert werden soll

Beispiel

SQL> select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp t1,scott.dept t2 where t1.deptno = t2.deptno;
SQL> select * from table(dbms_xplan.display_cursor(null,0,'allstats,last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0j83q86ara5u2, child number 0
-------------------------------------
select /*+ gather_plan_statistics use_hash(t1,t2)*/* from scott.emp
t1,scott.dept t2 where t1.deptno = t2.deptno

Plan hash value: 615168685

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     14 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     14 |     14 |00:00:00.01 |      13 |  1321K|  1321K| 1070K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      4 |      4 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     14 |00:00:00.01 |       7 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."DEPTNO"="T2"."DEPTNO")


21 rows selected.

SQL>

[Verwandte Empfehlungen:
SQL-Tutorial
]

Das obige ist der detaillierte Inhalt vonOptimierungsmethode der Oracle-Tabellenverbindungsmethode (mit Beispielen). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!