Méthode d'optimisation de la méthode de connexion aux tables Oracle (avec exemples)

Ce que cet article vous apporte concerne la méthode d'optimisation de la connexion aux tables Oracle (avec des exemples). Il a une certaine valeur de référence. Les amis dans le besoin peuvent s'y référer.

Dans la base de données Oracle, il existe quatre méthodes de connexion de table entre deux tables : jointure par fusion de tri, jointure par boucle imbriquée, jointure par hachage et jointure cartésienne

1. 🎜>

La jointure par fusion de tri est une méthode de connexion de table qui utilise l'opération de tri (SORT) et l'opération de fusion (MERGE) pour obtenir l'ensemble de résultats de connexion lorsque deux tables sont connectées

Si la table t1 et la. La table t2 utilise une connexion de tri-fusion lors de l'établissement de connexions de table, Oracle effectuera les étapes suivantes dans l'ordre :

a. Accédez à la table t1 avec la condition de prédicat spécifiée dans le SQL cible, puis accédez aux résultats. trié en fonction de la colonne de connexion de la table t1, et l'ensemble de résultats triés est enregistré comme s1

b. Accédez à la table t2 en fonction des conditions de prédicat spécifiées dans le SQL cible, puis triez les résultats d'accès en fonction. à la colonne de connexion de la table t2, l'ensemble de résultats triés est enregistré comme s2

c Effectuez une opération de fusion sur s1 et s2 et supprimez les enregistrements correspondants comme ensemble de résultats final

Les avantages, les inconvénients et l'application de la connexion par fusion de tri Scénario :

a. Habituellement, l'effet de la jointure par hachage est meilleur que la jointure par fusion de tri. Cependant, si la source de la ligne a été triée, il n'est pas nécessaire de trier. à nouveau lors de l'exécution de la jointure par fusion de tri. Dans ce cas, la jointure par fusion de tri sera meilleure que celle de la jointure par hachage

b Normalement, la jointure par fusion de tri ne sera utilisée que lorsque les situations suivantes se produisent :

<.>1) Mode RBO

2) Jointure de valeurs inégales (>,<,>=,<=)

3) Lorsque la jointure par hachage est désactivée (_HASH_JOIN_ENABLED=false)

Exemple

2. Jointure de boucles imbriquées (jointure de boucles imbriquées)

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>

Une jointure de boucles imbriquées est une sorte de connexion de table entre deux tables qui repose sur deux niveaux. de boucles imbriquées (boucle externe/boucle interne) Boucle) pour obtenir la méthode de connexion de table pour connecter l'ensemble de résultats

Si la table t1 et la table t2 utilisent une connexion en boucle imbriquée lors de la connexion à la table, alors Oracle effectuera les étapes suivantes dans l'ordre :

a. Tout d'abord, l'optimiseur déterminera qui est la table de pilotage et qui est la table pilotée en t1 et t2 selon certaines règles. La table de pilotage est utilisée pour l'extérieur. boucle, et la table pilotée est utilisée pour la boucle mémoire. Supposons que t1 est la table pilote

b. Accédez à la table pilote t1 avec la condition de prédicat spécifiée dans le SQL cible, et obtenez l'ensemble de résultats s1

c. table t2 en même temps, c'est-à-dire retirer Les enregistrements dans s1 correspondent à la table pilotée t2 selon les conditions de connexion. Enfin, l'ensemble de résultats sera renvoyé

Avantages, inconvénients et scénarios applicables de connexion en boucle imbriquée :

a Peut obtenir une réponse rapide, c'est-à-dire qu'il peut renvoyer la connexion qui a été connectée. et répond aux exigences du premier coup Enregistrements conditionnels, sans avoir à attendre que toutes les opérations de connexion soient terminées avant de renvoyer le résultat de la connexion

b Il convient à l'ensemble de résultats de conduite correspondant à la table de conduite. le nombre d'enregistrements dans l'ensemble de résultats de pilotage est faible, et en même temps, dans la connexion de la table pilotée, il existe un index unique sur la colonne (ou un index non unique avec une bonne sélectivité sur la colonne connectée de la table pilotée )

Exemple

3. Hash join

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>

Hash join est une méthode de connexion de table qui repose sur une opération de hachage pour obtenir le résultat de la connexion. défini lorsque deux tables sont connectées. Il a été introduit après Oracle 7.3

Hash join fonctionne en hachant une table (généralement une table plus petite) et en la stockant dans une liste de hachage, en extrayant les enregistrements d'une autre table et en effectuant une opération de hachage. , et en trouvant l'enregistrement correspondant dans la valeur de hachage, faites la correspondance

La jointure par hachage ne s'applique qu'au CBO et ne peut être utilisée que pour des conditions de jointure équivalentes

La jointure par hachage est. très approprié pour joindre de petites tables et de grandes tables, en particulier lorsque la sélectivité de la colonne de jointure d'une petite table est très bonne, alors le temps d'exécution de la jointure par hachage peut être approximativement considéré comme équivalent au temps passé sur une analyse complète de la table de la grande table

Lors de la connexion de hachage, la table de hachage correspondant à l'ensemble de résultats du pilote peut être entièrement hébergée dans la mémoire (espace de travail PGA. À ce stade, l'efficacité d'exécution de la connexion de hachage est très élevée

Problèmes de performances de connexion de hachage Il peut être diagnostiqué via l'événement 10104. Les instructions pertinentes sont les suivantes : buckets : buckets non vides : la situation des enregistrements vides et des enregistrements non vides dans le bucket de hachage

Nombre total. de lignes : le nombre d'enregistrements dans l'ensemble de résultats de pilotage

Nombre maximum de lignes dans un bucket : le bucket de hachage contenant le plus grand nombre d'enregistrements Contient le nombre d'enregistrements

Filtrage bitmap désactivé : s'il faut activer le filtrage bitmap

Exemple



[Recommandations associées :
Tutoriel SQL

】

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!