Discussion :

[crazykangourou]

C'est encore moi.

Toujours en quête du temps de réponse le plus court...

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select count(*) from
(SELECT code  FROM table1
   WHERE 
   table1.col1 IN
    (SELECT table2.col1
     FROM table2
     WHERE  table2.col2= 'mavariable')
     GROUP BY 'mavariable')
     and 
   substr (table1.col2,1,2) IN
    (SELECT table3.col1
     FROM table3
     WHERE table3.col2 = 'var1'
     AND table3.col3 = 'var2')
)

J'ai trituré ca dans tous les sens et c'est sous cette forme que j'ai le temps de réponse le plus rapide 8 sec, qui reste bien trop long.

Code :

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

select count(*) from (select * from table1,table2,table3 where ...)

Je reconnais que c'est des requetes tordues, mais comment faire?

Avez vous des suggestions, je suis dans l'impasse?
Merci a vous.

[plaineR]

Déjà tu peux faire :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT count(*) 
FROM table1
WHERE 
   table1.col1 IN
    (SELECT table2.col1
     FROM table2
     WHERE  table2.col2= 'mavariable')
AND 
   substr (table1.col2,1,2) IN
    (SELECT table3.col1
     FROM table3
     WHERE table3.col2 = 'var1'
     AND table3.col3 = 'var2')

Ensuite tu peux essayer de regarder si cela n'est pas plus rapide avec des clauses exists :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT count(*) 
FROM table1
WHERE EXISTS
    (SELECT null FROM table2
     WHERE  table2.col2= 'mavariable'
     AND table2.col1 = table1.col1)
AND EXISTS
    (SELECT null
     FROM table3
     WHERE table3.col2 = 'var1'
     AND table3.col3 = 'var2'
     AND table3.col1 = substr (table1.col2,1,2))

[remi4444]

La 2ieme requete de plaineR me semble la plus pure. Il faut s'assurer qu'il y a bien les index adéquat. Pour ça, il faut que tu saches quelles sont les clauses les plus discriminantes (s'il y en a).

[crazykangourou]

Résultat des courses :
en effet la requête avec exists me fait gagner 1 sec sur 8 sec (c'est pas mal)

Le problème c'est que ce n'est pas assez concluant.
J'ai donc été obligé de créer une colonne indexée pour appuyer ma recherche et la rendre plus rapide.
Je passe à 2,5 sec.

Le truc, c'est que je ne peux pas faire ca pour toutes mes tables.

Si vous avez d'autres suggestions...

[Yorglaa]

Essaye peut-être de "cibler" tes requêtes sur les colonnes de clefs primaires, au lieu d'utiliser Count(*) et Select *

[nuke_y]

Tu as quoi comme volumétrie de tables ? Et comme plan d'exécution ?

[remi4444]

Petites questions:
- combien de lignes ont tes tables (en gros) ?
- quel est le resultat de ton comptage (ordre de grandeur)?
- dans le cas ou le comptage est petit pour un grand nombre de lignes au départ, quel sont les critères qui ont été les plus "filtrants" ?

Petite remarque:
Je ne vois pas pourquoi tu es rétissent à la création d'index, les index sont au coeur de toutes bases de données. sans index, aucune chance d'avoir un accés rapide à quoi que ce soit du moment que tu as du volume... imgagine une grosse librairie qui ne te fournie pas les cartons ou les livres sont classés par titre et par auteur... tu va forcément y passer l'après midi pour trouver ce que tu cherche....

[crazykangourou]

table1 : 180 milles lignes
table2 : 3 milles lignes
table3 : 8 milles lignes

resultat du comptage : 20 milles lignes

critere le plus filtrant : peut être table2.col1 mais uniquement avec le predicat mavariable, si mavariable et var1 et var2 change, le critere filtrant peut basculer dans la 2eme sous requete

Je ne suis pas rétissant aux index, au contraire! Ce que je disais, c'est que j'ai du ajouter une colonne a ma table et que ensuite j'ai indexé ma colonne.

Sur mon plan d'execution, aucun table n'est en full scan, elles sont toutes range_scan ou en rowid, d'ailleurs c'est quoi la différence, même si je m'en doute un peu?

En fait je ne peut pas faire autrement que le count(*) et le select * car je passe par un parseur maison qui "sécurise l'affichage des résultats" imaginez que là j'affiche mes 20 milles lignes dans mon application PHP, ca exploserait tout.

[remi4444]

Je ne comprends pas pourquoi tu as du rajouter une colonne, il me semble qu'un index sur table3.col1 et un autre sur table2.col1 devraient être suffisant.
Ensuite une amélioration possible est de faire un index qui couvre tes 2 colonnes de la table table1 (col1,col2) . Puis au lieux de faire count(*), fait count(table1.col1) (normalement oracle fait tout seul la transformation mais on sait jamais...)
Sans oublier évidement de passer les stats sur ta base.

En faisant des index couvrant, on évite à oracle de faire de passages par les blocs de données puisque toutes ses infos sont déja contenues dans l'index.

Cependant, il ne faut pas rêver, compter 20 000 trucs sur 180 000 machins, ça fait du boulot.... tu n'aura jamais de réponse instantanée. Si tu veux améliorer, alors il faut se trourner du coté de l'applicatifs qui devrait maintenir des tables de compteurs au fur et à mesure qu'il fait des modifs.

[orafrance]

Code :

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

select count(*) from (select * from table1,table2,table3 where ...)

on pourrait voir la requête complète ?

[crazykangourou]

Requete complete :

Code :

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

1
2
3
SELECT count(*) FROM (SELECT * FROM table1,table2,table3 WHERE  
table1.col1=table2.col1 and table2.col2='mavarialbe' 
and substr(table1.col2,0,2)=table3.col1  and table3.col2 = 'var1'  AND table3.col3 = 'var2')

[orafrance]

je pense que le problème vient de là :

Code :

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

substr(table1.col2,0,2)=table3.col1

si col2 != 0 alors col1=NULL

1°) ça ne marche pas critère toujours faux
2°) ça génère du produit cartésien, tu dois d'ailleur le voir dans le plan d'exécution

[McM]

Fred, T'as pas confondu substr et decode ?

[orafrance]

tu as raison

Il faudrait le plan d'exécution avant et après alors

[remi4444]

.....
pas faire autrement que le count(*) et le select * car je passe par un parseur maison qui "sécurise l'affichage des résultats" imaginez que là j'affiche mes 20 milles lignes dans mon application PHP, ca exploserait tout.

ça me rapelle une polémique que j'avais eu avec un éditeur de gestion documentaire... il fournissait une interface pseudo-sql qu'il re-parsait derrière pour la gestion des droits. Mais pour un comptage très gros mélangé à un paramétrage de droits complexe, ça partait en vrille question temps de réponse. La seule solution que j'avais trouvé pour limiter les dégats était de faire des indexs couvrant pour limiter au maximum les io de la requête de comptage.

Est-ce que tu es sur que la requête finale est bien la tienne ou est-ce qu'elle est ré-écrite par ton "parseur maison" ??

[crazykangourou]

Ce que je vous ai donné, ce sont les requetes parsés.
Je vais allé voir du coté des index couvrant, je ne sais pas ce que c'est.
Merci pour les pistes...

PS : remi44444, en fait c'est la même problématique, le parseur maison fait tout ce que tu viens de dire, il injecte le comptage plus les droits table1/table3

[remi4444]

Le principe est de couvrir un ensemble de colonnes pour éviter à oracle de passer par les blocs de données. Imagine que tu ais un index uniquement sur table2.col1. Pour effectuer le critère "table1.col1=table2.col1 AND table2.col2='mavarialbe'", oracle va faire le chemin suivant:

Bloc-Données-Table1 (col1) -> index-table2 (col1) -> bloc-données-Table2 (col2)

si tu fais un index de ta table 2 qui couvre les 2 colonnes (col1,col2) , oracle va faire le parcours suivant:

Bloc-Données-Table1 (col1) -> index-table2 (col1,col2)

Oracle évite la dernière étape car l'information Table2.col2 est déja dans l'index.

Cette méthode est d'autan plus efficace qu'elle s'applique sur des tables avec beaucoup de colonnes.


Pour ton cas, je vois bien comme index:

table1(col1,col2)

table2(col1,col2)

table3(col1,col2,col3)

Remarques:
Si tu as déja un index sur une des premières colonnes, par exemple table1(col1), alors ne crée pas un nouvel index, mais elargi ton ancien d'index.

Autre chose, si par hasard tu as une table, disons à 3 colonnes, et que ces trois colonnes sont amenées à faire partie du même index, à ce moment là, ce n'est même plus la peine de faire des blocs de données, et tu fais une table organisée en index (ie: y'a plus de table proprement dite, y'a plus qu'un gros index...)

[nuke_y]

Certes remi, mais est-ce qu'au niveau architecture il y a des préférences pour une table complètement couverte par un index ? Je veux dire, ça reste une table hein ? Il n'y a pas d'option particulière à lui préciser ? (à part l'index forcément)

[crazykangourou]

Merci pour vos précisions.

Effectivement, j'ai une tres grosse table 20 colonnes, j'y peu rien, y'a pas eu de modélisation de la BD quand je suis arrivé ca existé déjà comme ca.
Si je fais une table index, c'est a dire couvrant toutes les colonnes, je suppose que les performances seront réduites d'un autre coté, lesquel, insert? update ?

Merci

[crazykangourou]

Encore une question.

Par exemple, si j'ai un index sur table1(col1,col2,col3,col4)
et un autre sur table1(col1,col3)
est ce que ce sont des index redondants?

Sur ma requete :

Code :

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

1
2
3
4
5
SELECT count(*) FROM 
 (SELECT code FROM table1 WHERE table1.col1 IN  (SELECT table2.col1 FROM  
 table2 WHERE table2.col2= 'mavariable')  GROUP BY 'mavariable') 
 AND substr (table1.col2,1,2) IN  (SELECT table3.col1 FROM table3 WHERE  
 table3.col2 = 'var1'  AND table3.col3 = 'var2') )

avec
index table1(col1,col2),

si je rajoute un index sur table1 tel que :
table1(col1)
J'ai des diminutions de performances. Que se passe t il ???????????

En vue de réorganiser mes index, y' a t il une requete qui permet de supprimer TOUS les index d'UNE table donnée?

Merci pour vos explications