Discussion :

[liouan]

Bonjour,

J'ai un souci de design. Je vous expose le problème ainsi que les différentes solutions envisagées.

Le problème
Je possède une arborescence (table ARBO) d'environ 1 millions d'éléments. L'utilisateur peut sélectionner 0,1 ou plusieurs éléments dans cette arborescence (noeuds ou feuilles).

Plus tard, il peut extraire des données (contenues dans une table DATA).

Il peut notamment extraire des données sur l'ensemble des feuilles des noeuds sélectionnés (donc potentiellement la moitié des éléments de l'arbre...)

Solution 1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT * 
FROM DATA 
WHERE ID IN
(SELECT ID_ELEMENT FROM ARBO START WITH ... CONNECT BY PRIOR ...)

Cette solution fonctionne mais répond en 1 minute, ce qui est inacceptable.

Solution 2
Lorsque l'utilisateur a effectué sa sélection, conserver l'ensemble des feuilles de la sélection dans un CLOB et réexploiter ce CLOB plus tard sur la table DATA (SELECT * FROM WHERE ID IN "le CLOB") quand l'utilisateur demandera des données.

En pratique, je ne sais pas trop comment rentrer proprement le résultat d'un SELECT dans CLOB et puis quelque chose me dit que cette solution est hideuse.

Solution 3
Utiliser une table temporaire, mais j'ai l'impression que je vais devoir créer une table temporaire par utilisateur (dynamiquement?).

Avez-vous une solution plus élégante?

Merci d'avance

[Pomalaix]

Utiliser une table temporaire, mais j'ai l'impression que je vais devoir créer une table temporaire par utilisateur (dynamiquement?)

Je ne sais pas ce qui vous fait dire ça, mais il est peut-être utile de rappeler que :
- une table temporaire peut être accédée par plusieurs utilisateurs (sous réserve que chacun ait les droits sur cette table)
- dans une table temporaire, les données sont privées : chaque session ne peut voir que les données qu'elles a placées elle-même dans cette table. Autrement dit, quand on fait un "SELECT * FROM T", c'est comme s'il y avait un WHERE implicite qui filtre les données de la session.

Je parle bien sûr des tables créées par la commande CREATE GLOBAL TEMPORARY TABLE ....

[skuatamad]

Plus tard, il peut extraire des données (contenues dans une table DATA).

Le plus tard est problématique pour la version table temporaire si l'application est stateless (appli web), car l'utilisateur se reconnectera avec une nouvelle session. En statefull ça semble être une bonne approche simple à mettre en place.

(donc potentiellement la moitié des éléments de l'arbre...)

Comment est géré l'affichage des informations parce qu'afficher 500 000 lignes ça consomme des I/Os et du réseau et au final c'est plutôt inutilisable pour l'utilisateur, une pagination n'améliorerait elle pas les perfs ?

conserver l'ensemble des feuilles de la sélection dans un CLOB

Pourquoi dans un CLOB ? Si c'est la requête avec CONNECT BY qui est lente, déjà s'assurer qu'elle est correctement indexée, ensuite il est préférable de stocker l'information dans une table relationnelle de paramétrage (id_user, id_element, date_time...)

[liouan]

Je parle bien sûr des tables créées par la commande CREATE GLOBAL TEMPORARY TABLE ....

J'ai en fait découvert ce type de tables en réfléchissant au problème, merci pour ces clarifications

si l'application est stateless (appli web)

Oui, c'est le cas. Appli web en Java/J2EE.

Comment est géré l'affichage des informations parce qu'afficher 500 000 lignes[...]

Pour ces informations, les données affichées ne pourront être que des données agrégées donc ça ne devrait pas poser de problèmes.

il est préférable de stocker l'information dans une table relationnelle de paramétrage

Je vais tenter cette solution.
Il serait d'ailleurs pratique de partitionner cette table par id_user mais je n'ai que la version standard d'Oracle. j'ai donc 2 possibilités:

• Indexer par id_user et voir si ça répond dans des temps corrects.
• Créér une table "temporaire" pour chaque utilisateur, gérée manuellement (suppression de toutes les tables temporaires au démarrage de l'appli, création à l'ouverture de session et destruction à la fermeture de session (session HTTP)).

[liouan]

J'ai tenté la solution consistant à stocker le résultat de la sélection dans des tables intermédiaires.
Le problème est que la sélection des éléments pour remplir la table prend 1 min.
C'est une avancée car ça permet de ne faire ce calcul qu'une fois (alors que j'exploite le résultat plusieurs fois) mais ça reste globalement pénible pour l'utilisateur.

Alors j'ai eu une autre idée en réorganisant la table ARBO.
Avant, elle contenait une colonne ID (PK et donc indexée) et une colonne ID_PARENT
Je l'ai recréée en 2 colonnes:
une colonne ID (PK) et une colonne PATH, contenant le chemin de l'élément dans l'arborescence, stocké dans un VARCHAR2.

Ainsi, la recherche de tous les feuilles d'un noeud se résume à une recherche par expression régulière sur le path. Et ça répond en < 0.1s

[skuatamad]

Et la colonne id_parent était elle indexée ?

[liouan]

Et la colonne id_parent était elle indexée ?

Oui, elle était indexée.

Bizarrement, aujourd'hui, la requête avec 'CONNECT BY' ne prend plus 1 minute mais juste une poignée de secondes (même après un 'alter system flush buffer_cache'). Je parierais sur un changement de plan suite à une réévaluation des statistiques...