2
votes

Oracle - Problème de performance de la clause where conditionnelle

Considérant deux tables, jointes de cette manière:

explain plan for
select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id
where    (:desired_id = table_1.desired_id or :p3070100_all = 1);

Plan hash value: 94704160

----------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |               |    79M|    24G|       |  1441K  (1)| 00:00:57 |       |       |
|*  1 |  FILTER                |               |       |       |       |            |          |       |       |
|*  2 |   HASH JOIN RIGHT OUTER|               |    79M|    24G|   484M|  1441K  (1)| 00:00:57 |       |       |
|   3 |    TABLE ACCESS FULL   | TABLE_1       |  3238K|   447M|       | 19152   (1)| 00:00:01 |       |       |
|   4 |    PARTITION RANGE ALL |               |    79M|    13G|       |   668K  (1)| 00:00:27 |     1 |1048575|
|   5 |     TABLE ACCESS FULL  | TABLE_2       |    79M|    13G|       |   668K  (1)| 00:00:27 |     1 |1048575|
----------------------------------------------------------------------------------------------------------------

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

   1 - filter("TABLE_1"."DESIRED_ID"=:DESIRED_ID OR TO_NUMBER(:P3070100_ALL)=1)
   2 - access("TABLE_2"."LNK_ID"="TABLE_1"."LNK_ID"(+))

Il y a un index sur "lnk_id" sur table_1, et sur table_2. Cela renvoie 80 millions de lignes.

J'utilise la clause where conditionnelle, avec des variables définies par mon frontal (APEX):

: all: If = 1, devrait retourner toutes les lignes .

: désiré_id: L'objet que je veux retourner. Peut être une valeur nulle, auquel cas je souhaite renvoyer uniquement des lignes de valeur nulle.

J'ai codé ceci en premier:

select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id
where  (:desired_id = table_1.desired_id or 0 = 1);


Plan hash value: 1995399472

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |   129 | 42183 |    45   (0)| 00:00:01 |       |       |
|   1 |  NESTED LOOPS                         |                  |   129 | 42183 |    45   (0)| 00:00:01 |       |       |
|   2 |   NESTED LOOPS                        |                  |   138 | 42183 |    45   (0)| 00:00:01 |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_1          |     3 |   435 |     7   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN                  | TABLE_1_I1       |     3 |       |     3   (0)| 00:00:01 |       |       |
|*  5 |    INDEX RANGE SCAN                   | TABLE_2_I2       |    46 |       |     3   (0)| 00:00:01 |       |       |
|   6 |   TABLE ACCESS BY GLOBAL INDEX ROWID  | TABLE_2          |    40 |  7280 |    21   (0)| 00:00:01 | ROWID | ROWID |
--------------------------------------------------------------------------------------------------------------------------



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

   4 - access("TABLE_1"."DESIRED_ID"=:DESIRED_ID)
   5 - access("TABLE_2"."LNK_ID"="TABLE_1"."LNK_ID")

Étant donné: tout = 0 et: désiré_id = une valeur non nulle pour sélectionner les lignes que l'utilisateur désire, j'éprouve des performances terribles.

J'ai appris que je devais éviter "case" dans la clause "where", donc adaptée à: p>

where  (:desired_id = table_2.desired_id or :all = 1);

Aucune chance, c'est aussi lent que la solution du "cas".

J'ai réalisé ceci:

where  (:desired_id = table_2.desired_id or 0 = 1);

-> 0.047s - Super rapide

where  (:desired_id = table_2.desired_id);

-> 0.062s - Super rapide

where (
        :all = 1
        or (:desired_id is null and table_2.desired_id is null)
        or :desired_id = table_2.desired_id
      )

- -> 235s - Super lent

Je peux donc certainement trouver l'objet souhaité sur mes 80 millions de lignes en un rien de temps, avec le where (... = ... ou. .. = 1) construction: l'optimiseur doit prendre la mauvaise décision lorsque j'utilise: all.

Quelqu'un pourrait-il guider?

Je préfère éviter le build-a- solution de requête dynamique si possible car elle la rend beaucoup plus complexe à implémenter et à gérer je crois et cela ressemble vraiment à ... cela devrait fonctionner avec du SQL brut.

- Modifier pour ajouter les plans -

Bon plan

select *
from   table_1
left   join table_2
on     table_1.some_id = table_2.some_id

where  (
         case when :all = 1
              then 1
              when :desired_id is null and table_2.desired_id is null
              then 1
              when :desired_id = table_2.desired_id
              then 1
              else 0
              end = 1
       )

Mauvais plan

select *
from   table_1
left   join table_2
on     table_1.lnk_id = table_2.lnk_id
;

Merci, David

oracle database-performance where

2 commentaires

Veuillez utiliser la commande EXPLAIN PLAN pour générer des plans pour tous vos questions, puis veuillez ajouter les plans à la question. Exécutez simplement EXPLAIN PLAN FOR your_query. 1 .. , puis exécutez SELECT * FROM table (DBMS_XPlan.display) et copiez le résultat de la dernière requête (sous forme de texte - pas bitmap !!!), et ajoutez-le t la question. Et puis répétez ce processus pour chaque requête.

Merci pour vos commentaires, je vais certainement essayer d'obtenir le plan, mais aussi ridicule que cela puisse paraître, j'ai besoin de le demander ... je ne l'ai pas encore reçu ...

3 Réponses :

1
votes

Lorsque vous ajoutez ou à votre clause where et que la partie de cette condition est un paramètre ou une fonction, la base de données ne peut pas simplement utiliser un index. C'est parce qu'il y a "une autre option" et que l'optimiseur basé sur les coûts (CBO) choisit souvent de "FULL TABLE SCAN". Vous pouvez simplifier votre requête pour CBO - par exemple:

au niveau de l'application, choisissez de créer une clause where différente basée sur: toute la valeur,
utilisez quelques astuces pour rendre la condition simple - comme au lieu d'utiliser: tous les paramètres utilisent uniquement: désiré_id et pour obtenir tous les résultats, passez simplement "null" comme valeur, vous pouvez alors faire quelque chose comme ça: p >
```
create index idx_table_2_desired_id on table_2(desired_id, 1);
```

S'il y a un index sur table_2.desired_id, CBO devrait choisir "scan de plage" ou "scan unique" (pour un index unique).

Vous devez toujours générer un plan d'explication pour vos requêtes et recherchez des "scans complets", des "boucles imbriquées" et des "jointures cartésiennes" avec de grandes tables - ce sont des choses que vous devriez éviter.

Mise à jour (2019-02-01)

Il existe une troisième option, lorsque vous souhaitez avoir "tout en une seule requête", donc sans logique supplémentaire au niveau de l'application (choix entre 2 requêtes) ou en utilisant SQL dynamique. Il existe une option pour faire 2 requêtes en une avec union all et les écrire de manière à ce qu'une seule partie soit exécutée par base de données.

Voici la version améliorée de la requête proposé par JPG:

create index idx_table_2_desired_id on table_2(desired_id);

Tenez compte du fait qu'expliquer plain pourrait "montrer" plus de travail pour cela que pour les autres plans, mais la base de données devrait en exécution résolue l'un des condition comme "toujours faux" et interroger une seule partie.

- Deuxième mise à jour -

Ok, j'ai maintenant lu l'un de vos commentaires où vous avez écrit:

"All" renvoie chaque ligne, "None" renvoie toutes les lignes avec des valeurs nulles pour cette colonne, "specific value" renvoie les lignes correspondant à cette valeur spécifique ...

Disons que tout peut prendre 3 valeurs:

0 - ce qui signifie une ligne spécifique
1 - ce qui signifie toutes les lignes
2 - ce qui signifie toutes les lignes avec NULL dans table_2.desired_id

Ensuite, la requête sera:

-- Specific row
select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id
where  :all = 0
  and  :desired_id = table_2.desired_id
union all
-- All rows with null
select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id
where  :all = 2
  and  table_2.desired_id is NULL
union all
-- All rows
select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id
where  :desired_id = table_2.desired_id
  and    :all = 1
;

Mais vous devez savoir qu'un simple index ne fonctionne pas avec des NULL. Donc, si vous avez un index sur table_2.desired_id:

select *
from table_2
left join table_1 on table_2.lnk_id = table_1.lnk_id
where nvl(:all,2) != 1
  and :desired_id = table_2.desired_id
union all
select *
from table_2
left join table_1 on table_2.lnk_id = table_1.lnk_id
where :desired_id = table_2.desired_id
  and :all = 1
;

Cela ne fonctionnera pas, mais cela pourrait être complexe, par exemple.

where table_2.desired_id = nvl(:desired_id, table_2.desired_id)

4 commentaires

Merci pour vos commentaires très appréciés. Je vais attacher les plans xplain dans une seconde, je viens de les avoir.

J'étudie également la possibilité d'utiliser votre suggestion avec nvl (), mais utiliser "null" pour coder "All" ne fonctionne pas immédiatement, en effet j'utilise "null" comme: désiré_id pour renvoyer des lignes où ... désiré_id est nul: Je dois être en mesure de renvoyer toutes les lignes où la valeur filtrée demandée est nulle. J'ai 3 cas à traiter, tels que traduits par la clause where primaire ci-dessus: "All" renvoie chaque ligne, "None" renvoie toutes les lignes avec des valeurs nulles pour cette colonne, "specific value" renvoie les lignes correspondant à ce spécifique valeur...

J'ai essayé ce qui - fonctionnellement - devrait faire l'affaire: où 1 = nvl (: ALL, 1) ou table_2.desired_id = nvl (: desire_id, table_2.desired_id); Mais le plan est le même. Chaque pièce fonctionne et génère le bon plan indépendamment (donc pas de condition "ou")

J'ai amélioré ma réponse. Vous voudrez peut-être le lire une fois de plus pour obtenir des indices supplémentaires.

0
votes

Un coup d'œil sur le mauvais plan explique la cause du problème, qui est ici:

select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id

L'optimiseur ne peut pas utiliser l'index TABLE_1_I1 à cause de cela OU état.

N'utilisez pas du tout cette variable de liaison TO_NUMBER (: P3070100_ALL) = 1 dans la requête, utilisez du SQL dynamique et deux versions de la requête en fonction de la valeur de : P3070100 à la place.

Si : P3070100 1 utilisez cette requête, qui utilisera l'index et sera rapide:

select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id
where    :desired_id = table_1.desired_id ;

et quand : P3070100 = 1 utilisez cette requête, qui sera lente (car elle joint toutes les lignes des deux tables):

1 - filter("TABLE_1"."DESIRED_ID"=:DESIRED_ID OR TO_NUMBER(:P3070100_ALL)=1)

1 commentaires

Hum ... merci pour vos retours, c'est très apprécié. J'ai pensé au SQL dynamique, mais je voulais l'éviter pour être honnête. J'ai plusieurs de ces "clause where" et j'ai pensé que le SQL clair était plus simple. J'y arriverai finalement ça sonne ... Y a-t-il un moyen de forcer l'optimiseur à utiliser l'index? Avec un indice? En forçant un plan? Je voulais étudier cela ensuite. Merci de votre aide !

1
votes

Comme l'a écrit robertus, vous devriez éviter d'utiliser "OU" dans une requête qui travaille sur des colonnes indexées Je propose de remplacer la requête suivante par "OR"

select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id
where  :desired_id = table_2.desired_id
  union
select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id
where  :desired_id <> table_2.desired_id
and    :all = 1
;

par une solution beaucoup plus puissante en termes de coût.

select *
from   table_2
left   join table_1
on     table_2.lnk_id = table_1.lnk_id
where  (:desired_id = table_2.desired_id or :all = 1);

0 commentaires