[9i] Columns <=> VARRAY [Résolu]

[jlinho2]

Bonjour à tous,

soit une table ENTREE au format imposé:

Code :

ENTREE: CLE, VAL1,VAL2,...,VALn

Soit un ensemble d'algorithmes F que je dois appliquer à chaque ligne de signature type:

Code :

F(VAL1,VAL2,...,VALN) RETURNS VAL1res,VAL2res,...,VALnRes

Comme ces algorithmes sont nombreux et doivent itérer sur la liste des (VAL1,...,VALn) parfois de manière
compliquée je réalise ces algos en PL/SQL avec des fonctions type:

Code :

F(VARRAY) RETURNS VARRAYres

sinon le code serait difficilement maintenable.

Mon code ressemble donc à un truc du style:

Code :

1
2
3
4
5
6
7
8
CREATE TABLE SORTIE AS 
SELECT cle, 
nth_element(res,1) AS VAL1res,
nth_element(res,2) AS VAL2res,
nth_element(res,n) AS VALnRes
FROM (
    SELECT cle, F(to_varray(val1,val2,...,valn) ) AS res FROM ENTREE)temp
);

où: nth_element(res,n) est une fonction PL renvoyant le Nème élément du VARRAY et to_varray(val1,val2,...,valn) est une fonction PL renvoyant le varray [val1,val2,...,valn].

Ce qui m'ennuie le plus c'est la requête englobante - avec les nth_element(res,1) - car elle force Oracle à re-parcourir le jeu de résultats de la sous-requête temp. La table entrée peut être volumineuse (1 500 000 lignes).

Peut-on éviter cela ? Ou quelqu'un a t'il une meilleure idée ? (j aimerai éviter les curseurs) .



Merci d'avance pour votre aide,

[jlinho2]

Pour

Code :

1
2
3
4
5
6
7
8
CREATE TABLE SORTIE AS 
SELECT cle, 
nth_element(res,1) AS VAL1res,
nth_element(res,2) AS VAL2res,
nth_element(res,n) AS VALnRes
FROM (
    SELECT cle, F(to_varray(val1,val2,...,valn) ) AS res FROM ENTREE)temp
);

Comme la requête englobante n'a pas de clause WHERE, est-ce que l'opération de "select cle,nth_element ..." est fait par Oracle dans le même balayage que le balayage de ENTREE fait par la sous-requête?

Le plan d'exécution est:

Code :

1
2
3
4
5
6
 
Plan
SELECT STATEMENT  HINT: ALL_ROWSCost: 18,982  Bytes: 116.948.360  Cardinality: 823,58  			
	3 VIEW TEST. Cost: 18,982  Bytes: 116.948.360  Cardinality: 823,58  		
		2 WINDOW SORT  Cost: 18,982  Bytes: 118.595.520  Cardinality: 823,58  	
			1 TABLE ACCESS FULL TEST.ENTREE Cost: 971  Bytes: 118.595.520  Cardinality: 823,58

[mnitu]

Pense tu que c'est vraiment une bonne idée de packager les valeurs dans une collection pour les dépackager ensuite ?
Si tu as besoin d'une requête dynamique utilise le SQL dynamique!

[jlinho2]

Envoyé par mnitu

Pense tu que c'est vraiment une bonne idée de packager les valeurs dans une collection pour les dépackager ensuite ?
Si tu as besoin d'une requête dynamique utilise le SQL dynamique!

A vrai dire... je me vois pas écrire une fonction qui itère sur des noms de colonnes directement non...

Il me semble que d'un point de vue pérénité du code, c'est plus chouette d'avoir un tableau - sur lequel une fonction itère une à plusieurs fois pour faire ces opérations (genre opération mathématique sur un vecteur) , non ?

Malheureusement j'ai l'impression qu'il n'y a pas de bonne solution avec SQL et PL/SQL...

Les appels successifs à nth_element( ) coutent très cher étant donné les piètres performances du PL/SQL...

Les temps:
pour passer d'une table avec VARRAY à la table équivalente avec le tableau éclaté sur chaque colonne (en appelant la fonction nth_element 10 fois pour les 10 éléments du tableau). Temps = 2 minutes pour 1 200 000 lignes.

Une requête qui éclate un objet équivalent (object composé de 10 Number) en SQL via la fonction TREAT(.. as obj).n1 est 4 fois plus rapide...

Dommage qu'une chose aussi basique comme tableau(i) ne soit pas là en SQL mais que en PL/SQL...

[mnitu]

Envoyé par jlinho2

A vrai dire... je me vois pas écrire une fonction qui itère sur des noms de colonnes directement non...

Et t'a regardé le Sql Dynamyque en utilisant le package DBMS_SQL ?

[jlinho2]

Envoyé par mnitu

Et t'a regardé le Sql Dynamyque en utilisant le package DBMS_SQL ?

Bonjour et Merci,

étant assez débutant je n'ai jamais utilisé ce package... j'y jette un oeil...
j'imagine que tu parles de dbms_sql.describe_columns(c, col_cnt, rec_tab); par exemple...

Celà me force t'il a faire du curseur ?

[mnitu]

Je pensait plutôt a DBMS_SQL.DEFINE_COLUMN.
Voilà un exemple de procédure PL/SQL qui utilise le package DBMS_SQL pour exécuter des requêtes SQL sans connaître combien des colonnes elle contient (l'algorithme est générique).
Je ne comprends pas bien ta question sur le curseur: quand t'exécute un select il y a un curseur implicite qui est crée.

[jlinho2]

Envoyé par mnitu

Je pensait plutôt a DBMS_SQL.DEFINE_COLUMN.
Voilà un exemple de procédure PL/SQL qui utilise le package DBMS_SQL pour exécuter des requêtes SQL sans connaître combien des colonnes elle contient (l'algorithme est générique).
Je ne comprends pas bien ta question sur le curseur: quand t'exécute un select il y a un curseur implicite qui est crée.

Merci, maintenant j'ai capté le truc... Encore merci pour cette super idée...

Quand je parlais de curseur au dessus je parlais du fait de coder explicitement la boucle de parcours des lignes ..

Niveau performance je sais pas ce que ça donnera par rapport au create as select... mais par contre je passe plus par des varray intermédiaires. Je testerai tout ça demain

[jlinho2]

Celà fonctionne mais à l'utilisation c'est pas si pratique que ça...


Merci à toi mnitu.

Je note le sujet en résolu