PL/SQL - conditions Facultatives dans la clause where- - sans le sql dynamique?

J'ai une question lorsque toutes les conditions sont nécessaires. Voici un exemple de ce à quoi ça ressemble quand toutes les conditions sont utilisées:

select num
from (select distinct q.num
       from cqqv q
       where q.bcode = '1234567' --this is variable
             and q.lb = 'AXCT' --this is variable
             and q.type = 'privt' --this is variable
             and q.edate > sysdate - 30 --this is variable
       order by dbms_random.value()) subq
where rownum <= 10; --this is variable

Les parties marquées comme --this is variable sont les parties qui, ainsi, de varier! Si une condition n'est PAS spécifié, alors il n'y a pas de valeur par défaut. Par exemple, si l'entrée spécifie "*" pour q.type (mais laisse tout de même), alors la requête doit correspondre à tout pour type, et exécuter en tant que:

select num
from (select distinct q.num
       from cqqv q
       where q.bcode = '1234567' --this is variable
             and q.lb = 'AXCT' --this is variable
             --and q.type = 'privt' --this condition ignored because of "type=*" in input
             and q.edate > sysdate - 30 --this is variable
       order by dbms_random.value()) subq
where rownum <= 10; --this is variable

Je sais qu'il est possible d'utiliser le sql dynamique pour construire cette requête à la volée, mais je me demande quelle sorte de problèmes de performances, cela pourrait causer, et si il ya une meilleure façon de le faire.

Version Oracle 10g.

OriginalL'auteur FrustratedWithFormsDesigner | 2009-11-11

11

Alors que vous pourriez faire cela...
```
select num
from (select distinct q.num
       from cqqv q
       where 1=1
             and (:bcode is null or q.bcode = :bcode)
             and (:lb is null or q.lb = :lb)
             and (:type is null or q.type = :type)
             and (:edate is null or q.edate > :edate - 30)
       order by dbms_random.value()) subq
where rownum <= :numrows
```
... la performance à l'aide de SQL dynamique sera généralement mieux, car il va générer une plus ciblée plan de requête. Dans la requête ci-dessus, Oracle ne peut pas dire si l'utilisation d'un index sur bcode ou lb ou type ou edate, et sera probablement effectuer un full table scan à chaque fois.

Bien sûr, vous doit utilisez bind variables dans votre requête dynamique, pas concaténer les valeurs littérales dans la chaîne, sinon les performances (et d'évolutivité et de sécurité) sera très mauvais.

Pour être clair, la dynamique de la version que j'ai à l'esprit serait de travailler comme ceci:
```
declare
    rc sys_refcursor;
    q long;
begin
    q := 'select num
    from (select distinct q.num
           from cqqv q
           where 1=1';

    if p_bcode is not null then
        q := q || 'and q.bcode = :bcode';
    else
        q := q || 'and (1=1 or :bcode is null)';
    end if;

    if p_lb is not null then
        q := q || 'and q.lb = :lb';
    else
        q := q || 'and (1=1 or :lb is null)';
    end if;

    if p_type is not null then
        q := q || 'and q.type = :type';
    else
        q := q || 'and (1=1 or :type is null)';
    end if;

    if p_edate is not null then
        q := q || 'and q.edate = :edate';
    else
        q := q || 'and (1=1 or :edate is null)';
    end if;

    q := q || ' order by dbms_random.value()) subq
    where rownum <= :numrows';

    open rc for q using p_bcode, p_lb, p_type, p_edate, p_numrows;
    return rc;
end;
```
Cela signifie que le résultat de la requête sera être "sargable" (un mot nouveau pour moi, je dois l'admettre!) depuis l'résultant de l'exécution de la requête (par exemple):
```
select num
from (select distinct q.num
       from cqqv q
       where 1=1
             and q.bcode = :bcode
             and q.lb = :lb
             and (1=1 or :type is null)
             and (1=1 or :edate is null)
       order by dbms_random.value()) subq
where rownum <= :numrows
```
Toutefois, j'accepte que cela pourrait prendre jusqu'à 16 dur analyse dans cet exemple. "Et :bv est null" clauses sont requis lors de l'utilisation natif SQL dynamique, mais peut être évité en utilisant DBMS_SQL.

Remarque: l'utilisation de (1=1 or :bindvar is null) quand l'affectation de la variable est null a été suggéré dans un commentaire par Michal Pravda, car il permet à l'optimiseur d'éliminer la clause.

J'ai l'habitude de le faire de cette façon, à moins que des problèmes de performances de me forcer à le faire d'une autre manière. En général, je pense que cela devrait être le préféré de chemin à faire, parce que toujours à l'aide de SQL dynamique signifie beaucoup de dur analyse, qui encombrent la piscine commune et d'autres problèmes. Et aussi: Rend le programme plus long et plus difficile à lire. Programme pour les humains, sauf si indiqué autrement.
Point intéressant, j'ai regardé comment utiliser des variables de liaison dans le SQL dynamique (que je ne connaissais pas ce truc!). Je vais devoir donner que d'essayer. Merci!
Bien sûr, là où j'ai dit "est null" ci-dessus, j'aurais dit "= '*'", mais vous voyez l'idée...
c'est vrai qu'il sera plus dur de l'analyse avec le SQL dynamique, mais seulement un petit nombre un pour chaque variante du SQL, c'est à dire 16 requêtes différentes dans cet exemple. Pesé à l'encontre de ce est le coût de la réalisation d'un full table scan à chaque fois par rapport à être en mesure d'utiliser des index avec les requêtes dynamiques. Si la table est petite et que la requête est simple, la statique de la requête peut être meilleur. Pour les plus grands ensembles de données et des requêtes plus complexes, j'irais pour la version dynamique.
Il est un moyen d'améliorer cette solution: N'utilisez pas juste <code>ET :edate EST NULL </code>. Laissez l'optimiseur de l'éliminer de la requête au début de parse/phase d'analyse. Cela peut être fait par des <code> (1=1 ou :edate EST NULL))</code>. De cette façon, vous avez l'avantage de OMG Poneys' variables de contexte' (avoir dans la clause where ne "significative" conditions) sans leur complexité et de la nécessité de la création d'un contexte. Dans certains cas, pour des requêtes de longue durée, il serait plus performant pour éliminer les contraignant à tous. Mais c'est rare et que vous êtes vulnérable aux injections SQL si u n'êtes pas prudent

OriginalL'auteur Tony Andrews
5

Alors que je suis d'accord avec Tony que la performance de l'aide de SQL dynamique est meilleure, les variables de contexte est une meilleure approche que l'utilisation de variables de liaison.

À l'aide de IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLE n'est pas idéal pour la manutention des valeurs optionnelles. Chaque fois qu'une requête est présentée, Oracle vérifie d'abord dans sa piscine partagée à voir si la déclaration a été soumise avant. Si il a, le plan d'exécution de la requête est récupéré et le SQL est exécutée. Si la déclaration ne peut être trouvé dans la piscine commune, Oracle doit passer par le processus de l'analyse de la déclaration, de travail différents chemins d'exécution et de proposer un plan d'accès (AKA “meilleur chemin”) avant de pouvoir être exécuté. Ce processus est connu comme un “difficile à analyser”, et peut prendre plus de temps que la requête elle-même. Lire plus sur le hard/soft analyser dans Oracle ici, et AskTom ici.

En bref - ce:
```
and (:bcode is null or q.bcode = :bcode)
```
...exécutera la même dynamique, ou autrement. Il n'y a aucun avantage à l'utilisation de bind variables dans le SQL dynamique pour les paramètres facultatifs. Le programme d'installation détruit SARGability...

Paramètres de contexte sont une fonctionnalité qui a été introduite dans Oracle 9i. Ils sont liés à un forfait, et peut être utilisé pour définir les valeurs d'attribut (uniquement pour les utilisateurs avec l'autorisation d'EXÉCUTION sur le paquet, et vous aurez à la subvention de CRÉER un CONTEXTE pour le schéma). Les variables de contexte peuvent être utilisés pour adapter le SQL dynamique de sorte qu'il ne comprend que ce qui est nécessaire pour la requête basée sur le filtre/critères de recherche. En comparaison, les variables de liaison (également pris en charge dans SQL dynamique) exiger qu'une valeur est spécifiée qui peut entraîner IN_VARIABLE IS NULL OR table.fieldx = IN_VARIABLE tests dans la requête de recherche. Dans la pratique, un contexte distinct de la variable doit être utilisé pour chaque procédure ou de la fonction pour éliminer le risque de valeur de la contamination.

Voici votre requête à l'aide de variables de contexte:
```
L_CURSOR SYS_REFCURSOR;
L_QUERY  VARCHAR2(5000) DEFAULT 'SELECT num
                                   FROM (SELECT DISTINCT q.num
                                           FROM CQQV q
                                          WHERE 1 = 1 ';
BEGIN

    IF IN_BCODE IS NOT NULL THEN
      DBMS_SESSION.SET_CONTEXT('THE_CTX',
                               'BCODE',
                               IN_BCODE);
      L_QUERY := L_QUERY || ' AND q.bcode = SYS_CONTEXT(''THE_CTX'', ''BCODE'') ';
    END IF;

    IF IN_LB IS NOT NULL THEN
      DBMS_SESSION.SET_CONTEXT('THE_CTX',
                               'LB',
                               IN_LB);
      L_QUERY := L_QUERY || ' AND q.lb = SYS_CONTEXT(''THE_CTX'', ''LB'') ';
    END IF;

    IF IN_TYPE IS NOT NULL THEN
      DBMS_SESSION.SET_CONTEXT('THE_CTX',
                               'TYPE',
                               IN_TYPE);
      L_QUERY := L_QUERY || ' AND q.type = SYS_CONTEXT(''THE_CTX'', ''TYPE'') ';
    END IF;

    IF IN_EDATE IS NOT NULL THEN
      DBMS_SESSION.SET_CONTEXT('THE_CTX',
                               'EDATE',
                               IN_EDATE);
      L_QUERY := L_QUERY || ' AND q.edate = SYS_CONTEXT(''THE_CTX'', ''EDATE'') - 30 ';
    END IF;

    L_QUERY := L_QUERY || ' ORDER BY dbms_random.value()) subq
           WHERE rownum <= :numrows ';

    FOR I IN 0 .. (TRUNC(LENGTH(L_QUERY) / 255)) LOOP
      DBMS_OUTPUT.PUT_LINE(SUBSTR(L_QUERY, I * 255 + 1, 255));
    END LOOP;

    OPEN L_CURSOR FOR L_QUERY USING IN_ROWNUM;
    RETURN L_CURSOR;

END;
```
L'exemple utilise toujours une liaison variable pour le rownum, parce que la valeur est pas facultatif.
```
DBMS_SESSION.SET_CONTEXT('THE_CTX', 'LB', IN_LB);
```
La SET_CONTEXT paramètres sont comme suit:
1. Le contexte nom de la variable. Il n'y a pas de création d'instances impliquées
2. La variable à l'intérieur de la variable de contexte. Une variable de contexte, c'est comme une variable de session, en supposant que la familiarité avec les applications web & objets de session.
3. La valeur de la variable définie dans le paramètre #2.
Lier vs Contexte

Lier les variables moyens Oracle attend une variable de référence pour remplir - c'est une erreur ORA autrement. Par exemple:
```
... L_QUERY USING IN_EXAMPLE_VALUE
```
...attend qu'il y est un seul lier des variables de référence à être peuplée. Si IN_EXAMPLE_VALUE est null, il a être :variable dans la requête. C'est à dire: AND :variable IS NULL

À l'aide d'une variable de contexte signifie ne pas avoir à inclure les étrangers/redondante de la logique, de vérifier si une valeur est nulle.

IMPORTANT: Lier les variables sont traitées dans leur ordre d'apparition (connu sous le nom ordinale), PAS par son nom. Vous remarquerez qu'il y a pas de type de données de la déclaration dans la USING clause. Les ordinaux ne sont pas l'idéal - si vous les modifiez dans la requête sans la mise à jour de la USING clause, il va se casser la requête jusqu'à ce qu'il soit corrigé.

Je n'avais pas encore entendu parler de variables de contexte, et je pense qu'ils peuvent être utiles pour d'autres trucs, je travaille sur (merci pour le saumurage à mon attention!), mais je ne suis toujours pas pourquoi ils sont mieux que de lier les variables dans ce cas. Comme simple exemple, quelle serait la différence si ma requête ont été: select * from cqqv q où q.bcode = sys_context('ctx1','bcode') par rapport select * from cqqv q où q.bcode = :bcode Quels sont les avantages de la variable dans le contexte de la méthode sur les lier des variables?
La différence est que lorsque vous utilisez des variables de liaison, vous toujours avoir la liaison de la variable de référence dans votre requête. C'est à dire: si la valeur est null, vous pour avoir AND :bcode IS NULL. À l'aide d'une variable de contexte signifie ne pas avoir à inclure des étrangers/redondante WHERE clauses
AHA! Je le vois maintenant! Le code pour ouvrir le curseur dépend du nombre de lier les variables sont dans le curseur, mais il n'a pas d'importance si les variables de contexte sont utilisés! 🙂
Hmm maintenant j'ai besoin de parler à l'administrateur de me donner la permission d'exécuter dbms_session.set_context...
Y - les infos que j'ai fourni & les deux liens travaillé pour moi dans le passé 🙂

OriginalL'auteur OMG Ponies
3

La solution, j'ai installé est celui qui génère une dynamique de requête SQL qui peut ressembler à ceci:
```
select num
from (select distinct q.NUM
       from cqqv q 
       where  (q.bcode = :bcode) 
                  and  (1=1 or :lb is null) 
                  and  (1=1 or :type is null) 
                  and  (q.edate> :edate) 
                order by dbms_random.value()) subq 
where rownum <= :numrows
```
(dans cet exemple, le bcode et edate conditions n'étaient PAS en option, mais le lb et le type ont été)

Je pense que c'est (ou très similaire à) ce que Michal Pravda a suggéré, et notre DBA ici préfère cette solution plutôt que la variable de contexte solution. Merci pour tous qui ont aidé et donné des conseils!

Un lien de notre DBA trouver les détails de cette solution est ici:

Demander à Tom: Sur la Popularité et la Sélection Naturelle

OriginalL'auteur FrustratedWithFormsDesigner

Je voudrais juste faire ce

select num
from (select distinct q.num
       from cqqv q
       where q.bcode = '1234567' --this is variable
             and q.lb = 'AXCT' --this is variable
             and q.type = nvl(<variable-type>, q.type)  --this condition ignored because of "type=*" in input
             and q.edate > sysdate - 30 --this is variable
       order by dbms_random.value()) subq
where rownum <= 10; --this is variable

On n'a qu'à garantir que la variable est de type null lorsque le q.TYPE de filtrage est d'être ignoré.

OriginalL'auteur Pedro Mendes

0

où ( columnA = passedValue ou passedValue = -1 )

lorsque la valeur passée en sql est -1, columnA peut être n'importe quoi..

OriginalL'auteur user6104564

Vous devez vous connecter pour publier un commentaire.

Lier vs Contexte