Que choisir entre "FOR row IN (SELECT. . ." et "FOR row IN cursor" et "FETCH cursor"

Bonjour à tous,

Je souhaiterai savoir quelles sont les différences entre :

1)
Code:

1 2 FOR row IN (SELECT CAR_NAME FROM CARS) ...
2)
Code:

1 2 3 4 CURSOR curseur IS (SELECT CAR_NAME FROM CARS); ... FOR row IN curseur ...
et 3)
Code:

1 2 3 4 5 6 7 8 9 CURSOR curseur IS (SELECT CAR_NAME FROM CARS); ... OPEN curseur; LOOP BEGIN FETCH curseur INTO car_name; EXIT WHEN curseur%NOTFOUND; ...
Il y a une véritable différence de performance entre chaque choix ? Que doit-on au mieux utiliser ?

Merci beaucoup ;)
Flo

Bonjour,

Entre 1 et 2 pas de différence de performance, tu choisis de déclarer ton curseur avant (par exemple pour le réutiliser) ou non
3) sera moins performant, car sur les dernières version d'Oracle le FOR a automatiquement un fetch size de 100 alors que pour le FETCH, du devra gérer toi le bulk collect.

Si tu sais utiliser sql_trace et tkprof, to peux comparer les 3, sur table avec quelques centaines d'enregistrements.

Je dirais que tu les as mis dans le bon ordre:
Si 1) te convient, c'est le plus simple et le plus performant.

Cordialement,
Franck.

Comme Pachot.
Une différence entre 1 et 2, c'est que le 1 tu ne peux pas faire un select for update.

Ça mérite une petite discussion. Et pour commencer voilà un exemple pour chaque cas.
1) Curseur explicite géré par le programmeur
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Declare cursor crs_emp Is Select * From hr.employees; -- emp_rec hr.employees%rowtype; Begin Open crs_emp; Loop Fetch crs_emp Into emp_rec; Exit When crs_emp%NOTFOUND; -- Dbms_Output.put_line(emp_rec.first_name||' '||emp_rec.last_name); -- End Loop; Close crs_emp; End; /
2) Curseur explicite géré dans une boucle for
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 Declare cursor crs_emp Is Select * From hr.employees; Begin For emp_rec In crs_emp Loop -- Dbms_Output.put_line(emp_rec.first_name||' '||emp_rec.last_name); -- End Loop; End; /
3) Curseur implicite géré dans une boucle for
Code:

1 2 3 4 5 6 7 8 9 10 11 12 Begin For emp_rec In (Select * From hr.employees ) Loop -- Dbms_Output.put_line(emp_rec.first_name||' '||emp_rec.last_name); -- End Loop; End; /
Dans le trois cases le résultat est identique. De plus, dans chaque cas, le même boulot et fait: un curseur est déclaré en mode explicite 1), 2) ou implicite 3), une variable de type enregistrements est déclaré en mode explicite dans 1) ou implicite dans 2) ou 3). Ensuite le curseur est ouvert ce qui entraine le passage de la requête associé au moteur SQL pour le parsing, élaboration du plan d'exécution, exécution de la requête et les résultats sont transmises au moteur PL/SQL dans la structure déclarée auparavant via le FETCH. Ensuite il y a toujours un test de fin de la boucle basé sur la détection du fin des enregistrements et le curseur est fermé soit d'une manière explicite 1) soit d'une manière implicite 2 et 3.

Donc la première différence est donnée en fait par qui fait le boulot: dans le cas 1) c'est le programmeur: déclaration du curseur, des variable , gestion du curseur, etc. dans le cas 2) et 3) c'est le compilateur PL/SQL qui prends en charge une bonne partie du boulot. Notez que dans le cas 3) la partie Declare du bloque PL/SQL n'est plus du tout présente.

Des autres différence sont données par les autres caractéristiques: telle que la possibilité d'utiliser des paramètres avec le curseur, flexibilité, contrôle du curseur, etc. En termes de performance du code PL/SQL c'est la même chose (ou presque ...).

Dans tous les cases la même requête SQL est exécutée par le moteur SQL à la demande du moteur PL/SQL. Donc du ce point de vue il ne peut y avoir aucune différence en termes de performance entre les 3 cases analysés.

Bon, maintenant d'où vient alors le gain de performance indique par @pachot pour le cas 3) ? Chaque passage d'information entre les deux moteurs concernés : SQL et PL/SQL implique un changement de contexte, c'est à dire une transformation de la même information entre deux structure de données différentes. Et à terme, quand beaucoup des enregistrements sont concernées par le traitement la pénalité de ces changement des contextes peut commencer à peser. Et comme la règle numéro 1 de l'optimisation est de moins faire si possible, le nombre des changements de contexte peut être diminué en utilisant l'array fetching (ramèner en tableau). C'est exactement ce qui se passe à partir d'Oracle 10g si on emploie la méthode 3) ou 2).

Mais ne tirez pas de conclusions trop vite. On peut faire pareille sinon mieux en utilisant le travail par lot via le BULK COLLECT et la méthode 1). En fait dans l'article «*Doing SQL from PL/SQL: Best and Worst Practices*» Bryn Llewellyn, PL/SQL Product Manager, recomande les suivantes

Si la requête ramène un seul enregistrements d'utiliser toujours Select … Into
Si la requête ramène un nombre limité des enregistrements d'utiliser toujours Select … Bulk Collect Into …
Si la requête ramène un nombre non-limité des enregistrements d'utiliser un curseur explicite dans une boucle for et le Fetch Bulk Collect Into … Limit TailleMaxDuLot