Vérifier si le paramètre est NULL dans la clause where

Im de la difficulté avec une Procédure Stockée qui prend environ une éternité à exécuter. Il est assez grand et je peux comprendre que les Mauvais de prendre un certain temps, mais celui-ci se poursuit pendant près de 20 minutes.

Après un peu de débogage et de la recherche, j'ai remarqué que le remplacement de cette partie de la WHERE clause;

((p_DrumNo IS NULL) OR T_ORDER.ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY))

fait une ÉNORME différence. Si la Procédure fonctionne très bien aussi longtemps que p_DrumNo est NUL ou que je modifie le ci-dessus de ne pas cocher si p_DrumNo est NULLE;

(T_ORDER.ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY))

L'objectif avec ce WHERE clause est de filtrer le résultat sur p_DrumNo si son passé à la Procédure Stockée. Le WHERE clause continue ensuite avec d'autres conditions, mais ce un point d'arrêt à la requête.

ORDERDELIVERY est juste un ~table temporaire contenant ORDER_IDs concerne le paramètre p_DrumNo.

Comment cela peut-il simple EST NULL-vérifier provoquer un tel grand impact? C'est probablement lié à l'utilisation de OR en collaboration avec la sous-requête mais je ne comprends pas pourquoi que la sous-requête elle-même fonctionne très bien.

Merci d'avance!

Mise à JOUR [2011-09-23 10:13]

Ive décompose le problème dans cette petite requête qui montrent le même comportement;

Exemple Un

Requête SQL

SELECT * FROM T_ORDER WHERE
('290427' IS NULL OR ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '290427%') );

Plan d'exécution

OPERATION   OBJECT_NAME     OPTIONS     COST
------------------------------------------------------------
SELECT STATEMENT                    97
FILTER
TABLE ACCESS    T_ORDER         FULL        95
TABLE ACCESS    T_ORDER         BY INDEX ROWID  2
INDEX       PK_ORDER        UNIQUE SCAN 1

Exemple B

Requête SQL

SELECT * FROM T_ORDER WHERE
( ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '290427%') );

Plan d'exécution

OPERATION   OBJECT_NAME     OPTIONS     COST
------------------------------------------------------------
SELECT STATEMENT                    4
NESTED LOOPS                        4
TABLE ACCESS    T_ORDER         BY INDEX ROWID  3
INDEX       IX_T_ORDER_ORDERNO  RANGE SCAN   2  
TABLE ACCESS    T_ORDER         BY INDEX ROWID  1  
INDEX       PK_ORDER        UNIQUE SCAN 0

Comme vous pouvez le voir, la première requête (exemple A) une analyse de la table. Des idées sur comment je peux l'éviter?

Vous pouvez poster l'instruction SQL complète et le plan de requête dans les deux cas? Est OrderDelivery une table temporaire globale? Ou une table permanente où les données sont transitoires? Comment sélective est ce prédicat?
OrderDelivery est une table temporaire dans un curseur. Je préfère ne pas inclure l'ensemble de la procédure mais j'ai mis à jour ma question avec un exemple. Merci

OriginalL'auteur Stefan | 2011-09-22

  1. 4

    Au lieu d'évaluer votre procédure du paramètre de l'état dans l'instruction SQL lui-même, se déplacer qu'à évaluer le contenant PL/SQL bloc de sorte qu'il est exécuté qu'une seule fois avant de l'idéal de l'instruction SQL est soumis. Par exemple:

    CREATE OR REPLACE PROCEDURE my_sp (p_DrumNo VARCHAR2)
IS
BEGIN
    IF p_DrumNo IS NULL THEN
        SELECT ...
        INTO ... -- Assumed
        FROM ...
        WHERE my_column = p_DrumNo;
    ELSE
        SELECT ...
        INTO ... -- Assumed
        FROM ...
        WHERE ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY);
    END;
END;

    J'ai aussi eu un certain succès dans le réglage des instructions SQL avec un OR par la rupture de la déclaration en deux mutuellement exclusives les états de l'UNION de TOUS:

    SELECT ...
FROM ...
WHERE p_DrumNo IS NULL
AND ORDER_ID IN (SELECT ORDER_ID FROM ORDERDELIVERY)
UNION ALL
SELECT ...
FROM ...
WHERE p_DrumNo IS NOT NULL
AND my_column = p_DrumNo;

    OriginalL'auteur Wolf

  2. 3

    Que vous avez rencontré un tel problème en raison du fait de votre index ne fonctionne pas si vous incluez OR à votre requête. Pour obtenir la même info, je préfère le faire faire l'index de travail (en se basant sur les mises à jour de la requête):

    SELECT * FROM T_ORDER WHERE '290427' IS NULL
UNION ALL
SELECT * FROM T_ORDER WHERE ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '290427%'));

    Il sera de retour le même résultat depuis 290427 semble être une variable et il tend à être nulle ou non nulle à ce moment.

    Mais aussi, vous pouvez essayer d'utiliser sql dynamique à l'intérieur de vous une procédure stockée à de telles fins:

    %begin_of_the_procedure%

query_ := 'SELECT * FROM T_ORDER WHERE 1=1';
if var_ is not null then
  query_ := query_||' AND ORDER_ID IN (SELECT ORDER_ID FROM T_ORDER WHERE ORDERNO LIKE '''||to_char(var_)||'%'')';
end if;
open cursor_ query_;

%fetching cursor loop%
%end_of_the_procedure%

    Et je voulais dire que je ne vois pas le sens de cette IN, il avait tout à fait le même:

    SELECT * FROM T_ORDER WHERE ('290427' IS NULL OR ORDERNO LIKE '290427%');

    OriginalL'auteur ZZa