Optimisation de l'Oracle de la requête par la suppression de "existe" et "n'existe pas'

J'ai récemment déménagé un morceau de code en production sur une oracle base de données où l'un des plus développeur expérimenté qui a passé en revue, il mentionné, j'ai eu beaucoup trop de exists et not exists déclarations et qu'il devrait y avoir un moyen de les éliminer, mais il avait été trop longtemps depuis qu'il a eu à l'utiliser et ne me souviens pas de beaucoup sur la façon dont il a travaillé. Actuellement, je vais retourner et de faire le morceau de code plus maintenable que c'est une pièce susceptible d'être modifiée à plusieurs reprises dans les années à venir que de la logique métier et des exigences, de changement, et je voulais aller de l'avant et de l'optimiser tout en le rendant plus facile à gérer.

J'ai essayé de chercher, mais tout ce que je peux trouver est des recommandations sur le remplacement des not in avec not exists et de ne pas revenir sur les résultats réels.

En tant que tel, je me demande ce qui peut être fait pour optimiser hors exists/not exists ou si il y a une façon d'écrire exists/not exists de sorte que oracle permettra d'optimiser en interne (probablement à un peu mieux que je peux).

Par exemple, comment peut-on la suite être optimisé?

UPDATE
    SCOTT.TABLE_N N
SET
    N.VALUE_1 = 'Data!'
WHERE
    N.VALUE_2 = 'Y'
    AND
    EXISTS
    (
        SELECT
            1
        FROM
            SCOTT.TABLE_Q Q
        WHERE
            N.ID = Q.N_ID
    )
    AND
    NOT EXISTS
    (
        SELECT
            1
        FROM
            SCOTT.TABLE_W W
        WHERE
            N.ID = W.N_ID
    )

Par hasard, était le "plus de développeurs expérimentés" ont plus d'expérience avec MySQL? (En MySQL une jointure externe gauche avec un select ... où null fonctionne généralement mieux que l'un n'existe pas de clause, mais dans Oracle et de nombreuses autres bases de données n'existe pas de clause fonctionne généralement mieux.)
À ma connaissance, il est PL/SQL et faisait référence à l'Oracle PL/SQL Programmation par Steven Feuerstein comme la source d'où il a lu au sujet de l'optimisation. Il y a une chance qu'il pourrait avoir juste été mal se souvenir d'une optimisation différente, les humains ont un bug connu où les souvenirs peuvent être partiellement fausses.
Google recherche de livres semble toujours afficher cette page dans le livre que vous avez mentionné: les livres.google.co.royaume-uni/..." - ce qui n'est pas directement pertinente. Cependant, cette page web: dbpedias.com/wiki/Oracle:Coding_Best_Practices_-_Using_EXISTS/... (qui semble avoir été rédigé par M. Feuerstein) en fait essentiellement la même points que d'être ici, maintenant, en réponse, ci-dessous. Enfin, minus peut parfois dépasser le not exists - voir tech.dir.groups.yahoo.com/group/toad/message/... .
Une note de côté, mais il semble que le plsql étiquette a été modifiée pour être un sql tag. Si j'ai utilisé les deux, ou est-il une raison à cela à préférer sql?

OriginalL'auteur Lawtonfogle | 2013-04-25

oracle sql

Votre déclaration semble parfaitement bien pour moi.

Dans toute optimisation de la tâche, ne pense pas que les modèles. Ne pense pas comme, "(not) exists est mauvais et lent, (not) in est super cool et rapide".

Penser comme, combien de travail de la base de données n'sur chaque étape et comment pouvez-vous mesurer?

Un exemple simple:

-- PAS DANS:

23:59:41 HR@sandbox> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.03
23:59:43 HR@sandbox> set autotrace traceonly explain statistics
23:59:49 HR@sandbox> select country_id from countries where country_id not in (select country_id from locations);
11 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 1748518851
------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |     6 |     4   (0)| 00:00:01 |
|*  1 |  FILTER                |                 |       |       |            |          |
|   2 |   NESTED LOOPS ANTI SNA|                 |    11 |    66 |     4  (75)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | COUNTRY_C_ID_PK |    25 |    75 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN    | LOC_COUNTRY_IX  |    13 |    39 |     0   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL    | LOCATIONS       |     1 |     3 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "LOCATIONS" "LOCATIONS" WHERE
"COUNTRY_ID" IS NULL))
4 - access("COUNTRY_ID"="COUNTRY_ID")
5 - filter("COUNTRY_ID" IS NULL)
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
11  consistent gets
8  physical reads
0  redo size
446  bytes sent via SQL*Net to client
363  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
11  rows processed

-- N'EXISTE PAS

23:59:57 HR@sandbox> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.17
00:00:02 HR@sandbox> select country_id from countries c where not exists (select 1 from locations l where l.country_id = c.country_id );
11 rows selected.
Elapsed: 00:00:00.30
Execution Plan
----------------------------------------------------------
Plan hash value: 840074837
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    11 |    66 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI|                 |    11 |    66 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN | COUNTRY_C_ID_PK |    25 |    75 |     1   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| LOC_COUNTRY_IX  |    13 |    39 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")
Statistics
----------------------------------------------------------
0  recursive calls
0  db block gets
5  consistent gets
2  physical reads
0  redo size
446  bytes sent via SQL*Net to client
363  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
11  rows processed

PAS DANS dans cet exemple lit deux fois plus de base de données de blocs et effectue plus compliqué de filtrage - demandez-vous, pourquoi vous avez choisi ce cours n'EXISTE PAS?

+1 Excellente réponse.
Que dois-je faire des temps de chaque de prendre, comme il apparaît not in prend 1/15e le temps not exists prend. Ou suis-je juste une erreur de lecture des données?
tu veux dire, mon exemple, 0,02 vs 0.3? qui est en fait une horloge murale, il inclut le temps écoulé sur l'affichage de sortie dans mon sql*plus de la fenêtre, et il peut varier. Je viens de re-couru à ces requêtes, et il est arrivé à 0,03 pour "n'existe pas" et 0,07 pour "non". si vous voulez un calendrier précis, vous devriez vérifier sql trace ou probablement quelque part dans v$sql_plan_statistics

OriginalL'auteur be here now

2

Il n'y a pas de raison d'éviter l'utilisation EXISTE ou n'EXISTE PAS lorsque c'est ce dont vous avez besoin. Dans l'exemple que vous avez donné, c'est probablement exactement ce que vous voulez utiliser.

Typique dilemme est de savoir si l'utilisation DE/PAS, ou n'EXISTE/n'EXISTE PAS. Ils sont évalués de façon très différente, et on peut être plus ou moins rapidement en fonction de votre situation spécifique.

Voir ici pour plus de détails que vous souhaitez probablement.

OriginalL'auteur Todd Gibson

Je ne sais pas si c'est beaucoup plus rapide, mais ici, c'est une façon de l'écrire sans EXISTS/NOT EXISTS:

MERGE INTO TABLE_N T
USING (
SELECT N.ID, 'Data!' AS NEW_VALUE_1
FROM SCOTT.TABLE_N N
INNER JOIN SCOTT.TABLE_Q Q
ON Q.N_ID = N.ID
LEFT JOIN SCOTT.TABLE_W W
ON W.N_ID = N.ID
WHERE N.VALUE_2 = 'Y'
AND W.ID IS NULL
) X
ON ( T.ID = X.ID )
WHEN MATCHED THEN UPDATE
SET T.VALUE_1 = X.NEW_VALUE_1;

OriginalL'auteur Markus Jarderot

Vous devez vous connecter pour publier un commentaire.