Discussion :

[w3blogfr]

Bonjour à tous,

Je vais essayer d'être assez précis pour que vous puissiez m'aider.

J'ai actuellement une table qui contient des numéros de séries ( 436239 lignes). Le numéro de série est unique sur 18 caractères. J'ai donc mis un index unique dessus.
Habitué à normaliser mes bases, j'utilise une clé primaire numérique.
J'ai 3 tables qui font référence à la clé primaire numérique (dont 1 contenant 373914 lignes)

Sur beaucoup de mes requêtes qui dépendent du numéro de série, j'ai des temps de réponse lent, pouvant attendre 11 secondes, ce qui n'est pas acceptable vu la faible quantité de données (Qu'est qu'un demi-million pour Oracle? ).

Pour info, le client veut impérativement faire ses recherches en mode "contient" et non en mode "commence par". La plupart de mes requêtes ont donc un like "%<NUM_SERIE_LETTER>%'.

J'ai testé différent réglage et essayer sur un base local pour voir si j'avais moyen d'optimiser tout ca. En dupliquant mon champ numéro de série sur les 2 tables référentes, ce qui m'évite une jointure sur la table NumSerie, j'obtiens des temps de réponses de 2 à 3 secondes (en mettant un index), ce qui pourrait paraitre acceptable.

Mais alors dans ce cas, vu que le numéro de série est dupliqué dans 2 de mes tables référentes, ne serait-il pas mieux d'utiliser mon VARCHAR2(18) de numéro de série comme clé primaire plutôt qu'un numérique?

Qu'en pensez-vous?

[mnitu]

Like "%<NUM_SERIE_LETTER>%' fait sauter n'importe quel index.

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
SQL> explain plan for
  2  Select *
  from hr.employees t
 where t.email = 'SKING';
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------
Plan hash value: 563061882
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    68 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |     1 |    68 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMAIL_UK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------
---------------------------------------------------
 
   2 - access("T"."EMAIL"='SKING')
 
SQL> explain plan for
  2  Select *
  from hr.employees t
 where t.email like '%SKING%';
 
Explicité.
 
SQL>  select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------
Plan hash value: 1342275408
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     5 |   340 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     5 |   340 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------
 
   1 - filter("T"."EMAIL" LIKE '%SKING%')

[ojo77]

Comme le précise mnitu, le fait de rechercer en mode "contient" force un parcours complet de la table les statistiques oracle étant insuffisantes pour associer une sélectivité à un contenu.

Si vous êtes absolument certain qu'un passage par index sera plus rapide, vous pouvez indiquer à l'optimiseur d'utliser un parcours par index au moyen d'un "hint" au niveau de la requête.

Code sql :

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

1
2
3
4
 
Select /*+ index(<nom de table> <nom d'index>)*/ ...
from <tables>
Where <conditions>

Mais avant toutes choses :

• Pourquoi le client veut une requête en mode "contient" ?
• Qui est le client (MOE ou MOA) ?
• Que souhaite vraiment la MOA ?
• La MOA peut elle faire évoluer son besoin ? Genre un "commence par" plutôt qu'un "contient" ?

Bref, si le besoin est effectivement une requête en mode "contient" et que chaque requête doit au final remonter un grand nombre de lignes, de mauvaises performances sont à prévoir sans qu'on puisse y faire grand chose.

[w3blogfr]

Justement, je pensais aussi qu'Oracle (10g au passage) ne pouvait pas utiliser d'index avec like '%<FILTRE>%', mais mon EXPLAIN est formel. Il utilise l'index créé par l'unique key.

Code :

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

1
2
 
|*  7 |   INDEX RANGE SCAN  | NUMSERIE_UK1  |    12 |   |  2   (0)| 00:00:01 |

Un autre fait étonnant. Ma table numéro de série contient une forte majorité de numéro de série commencant 'SN' suivi d'une série de chiffre(voir de lettre) que je stocke unique sur 18 caractères, je dirais que les cas 'SN' représente environ 80 % de la table.

L'information remontée par la requête est 5 niveaux plus haut (5 jointures, ça fait beaucoup, je sais).
Un critère like 'SN%' est relativement lent (11 secondes). Le EXPLAIN m'indique un coût de 36. C'est assez normal, mon critère ne filtre finalement pas grand chose, et il retourner 80% de la table.

Mais ce qui est étrange, c'est que pour la même requête, le critère LIKE '%SN%' est plus rapide (2 secondes) alors que le coût du EXPLAIN est de 800. Les donnés stockées font que les 2 requêtes retournent les mêmes résultats.

Les 2 requêtes m'indiquent utiliser l'index unique NUMSERIE_UK1. C'est étrange non? J'arrive pas à comprendre comment LIKE '%SN%' peut être plus performant.


En revanche, Le like '<LETTRE_FILTER>%' devient plus performant à partir de 6 lettres saisies.


Pour la justification du besoin client.
Le numéro de série est constitué de lettres et chiffres qui peuvent correspondre parfois à des dates ou des modèles. C'est pourquoi l'utilisateur peut saisir directement '1001' pour avoir les numéros de séries de Janvier 2010, mais ce n'est pas la majorité des utilisateurs.

Au début persuadé d'un gain de performance en mode "commence par", j'avais testé une modif de l'interface pour laisser le choix à l'utilisateur entre les 2 modes, libre à l'utilisateur de passer en mode "contient" et d'avoir des requêtes plus longues. Ce qui aurait satisfait la fonctionnalité et/ou la performance. Mais les performances ne sont pas si visible comme expliqué ci-dessus.

[w3blogfr]

mnitu, j'obtiens bien le même explain que toi sur ma table avec un select simple sans jointure.

Code :

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

1
2
 
SELECT * FROM NUMSERIE numSerie WHERE numSerie.numSerie LIKE 'BS1001%'

Cette requête utilise l'index.

Code :

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

1
2
 
SELECT * FROM NUMSERIE numSerie WHERE numSerie.numSerie LIKE '%BS1001%'

Cette requête fait un full scan.


Dans tous les cas, vu que numéro de série est unique sur 18 caractères, me conseiller vu de l'utiliser comme primary key au détriment de mon id numérique?

Ca me fait bizarre d'utiliser un varchar en clé primaire, mais c'est surement parce que je suis habitué à MySQL

[mnitu]

La requête de type LIKE 'BS1001%' peut utiliser un index parce que vous avez le début de la chaîne des caractères et vous pouvez la comparer d'une manière efficace avec le contenu de l'index, exemple: BA10000, BA10010, ... BS10000, BS10010, BS100100, BS1001000, ... BT1000, ...
Mais comment voulez vous comparer efficacement le contenu de l'index ci dessus avec la condition LIKE '%10%' ?
Parfois la seule utilisation de l'index dans ces conditions pourrait être de l'utiliser comme une table en full scan, donc de faire un index full scan. Mais cette possibilité est assez restrictive: la colonne doit être not null, le résultat doit pouvoir être obtenu que sur l'index.
L’idée d’utiliser un hint me semble complètement erronée dans ce cas : en présence des bonnes statistiques l’optimiseur fait assez bien son boulot.
Cinq table en jointure c'est rien, le base des données sont là pour faire des jointures.
Mais pour avancer il faut déjà les requêtes ainsi que des autres informations.

[Rams7s]

Justement, je pensais aussi qu'Oracle (10g au passage) ne pouvait pas utiliser d'index avec like '%<FILTRE>%', mais mon EXPLAIN est formel. Il utilise l'index créé par l'unique key.

Code :

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

1
2
 
|*  7 |   INDEX RANGE SCAN  | NUMSERIE_UK1  |    12 |   |  2   (0)| 00:00:01 |

Un autre fait étonnant. Ma table numéro de série contient une forte majorité de numéro de série commencant 'SN' suivi d'une série de chiffre(voir de lettre) que je stocke unique sur 18 caractères, je dirais que les cas 'SN' représente environ 80 % de la table.

L'information remontée par la requête est 5 niveaux plus haut (5 jointures, ça fait beaucoup, je sais).
Un critère like 'SN%' est relativement lent (11 secondes). Le EXPLAIN m'indique un coût de 36. C'est assez normal, mon critère ne filtre finalement pas grand chose, et il retourner 80% de la table.

Mais ce qui est étrange, c'est que pour la même requête, le critère LIKE '%SN%' est plus rapide (2 secondes) alors que le coût du EXPLAIN est de 800. Les donnés stockées font que les 2 requêtes retournent les mêmes résultats.

Bonjour,

Concernant ce point, si j'ai compris, pour le SN% il utilise l'index. Et avec le message de mnitu #6, il apparait que pour %SN% il n'utilise pas l'index.

Maintenant, si c'est bien ca () je vous conseillerai les histogrammes.
http://asktom.oracle.com/pls/asktom/...D:707586567563
Dans le cas ou vous recupererez 80% de la table, c'est plus rapide de faire un full table scan que d'utiliser un index. Maintenant, si le CBO ne sait pas qu'il y a beaucoup beaucoup de SN%, il y a peu de chance qu'il le devine.

http://books.google.ch/books?id=TGSd...page&q&f=false
Page suivante, la 179.

Ca ne resout rien, mais ca peut expliquer un peu.

[w3blogfr]

J'ai dû effectivement me mélanger les pinceaux, je n'arrive plus à voir dans un explain que oracle utilise l'index pour les like '%BS%'.

Pour la différence entre la contrainte unique et l'index, je sais bien qu'il n'y a pas de différence dans un explain, je me posais juste la question de savoir pourquoi les 2 syntaxes était possible?


Je n'ai pas les privilèges suffisant pour DBMS_STATS, j'ai fais une demande pour avoir le privilège, on verra bien.


Voici la requête :

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
16
17
18
 
SELECT COUNT(DISTINCT produit.ProduitId)
FROM Produit produit
JOIN GROUPETEST groupeTest
ON produit.ProduitId=groupeTest.ProduitId
JOIN BANC banc
ON banc.bancId=groupeTest.BancId
AND (banc.bancReference
  ||banc.bancNumSerie LIKE '%BANC%'
OR banc.bancName
  ||banc.bancNumSerie LIKE '%BANC%')
JOIN GROUPETESTNUMSERIE groupeTestNumSerie
ON groupeTestNumSerie.groupeTestId=groupeTest.groupeTestId
AND groupeTestNumSerie.produitId  =produit.produitId
AND groupeTestNumSerie.siteId     =1
JOIN NUMSERIE numSerie
ON numSerie.numSerieId=groupeTestNumSerie.numSerieId
AND numSerie.numSerie LIKE 'BS%'

Avec ou sans index, la recherche est toujours plus longue en mode "Commence par". cela s'inverse qu'à partir de 6 caractères saisies.


La version est la Oracle Database 10g.

[pachot]

Bonjour,

Juste un petit mot là dessus:

Habitué à normaliser mes bases, j'utilise une clé primaire numérique.

Au contraire: tu as une clé, il faut l'utiliser. Créer une clé supplémentaire, c'est le contraire de la normalisation: tu rajoute de la redondance.
Et en plus, pour des raisons de performances, tu vas te retrouver à mettre cette redondance dans les autres tables pour éviter des jointures.

Quel probléme y a-t-il à ce que la clé ne soit pas numérique ? au contraire tu peux alors:
- avoir un histogramme pour gérer la distribution SN%
- avoir envie de partitionner: les SN d'un côté par exemple.

Si tu veux normaliser, alors peut-être avoir des colonnes pour spécifiques (type de produit, date,...) pour que l'utilisateur n'aille pas deviner une date ou un code dans le numéro de série.

Ca me fait bizarre d'utiliser un varchar en clé primaire, mais c'est surement parce que je suis habitué à MySQL

Pour quelle raison un number est mieux qu'un varchar sous MySQL ?

Cordialement,
Franck.

[w3blogfr]

Très étrange, je n'ai pas reçu d'alerte mail pour les nouvelles réponses.

Vous renoncez un peu vite, non ?

Je pense pas avoir renoncer trop vite à la normalisation, j'ai déjà passer 2 semaines sur ce même problème .

LIKE '%BS%' et LIKE 'BS%'.
Pour quoi voulez-vous qu'il y a une différence si l'index n'est pas utilisé ?

Non non, vu me qu’oracle fait un full scan, je comprend très bien pourquoi les temps sont similaire.


Je vais adapter ma requête avec la méthode EXIST, je vous tiendrais au courant.

Si j'utilise mon VARCHAR comme clé primaire de la table NUMSERIE, et donc comme clé étrangère également dans les autres tables. Je vais certainement ne pas faire les jointures jusqu'à NUMSERIE, et m'arrêter avant. Ne devrais-je pas alors partitionner le numéro de série dans les tables sous-jacente?

Pour quelle raison un number est mieux qu'un varchar sous MySQL ?

Non, je dis jusque qu'avec MySQL , on met un peu trop rapidement des clé primaire auto-incrémenté et que les varchar ne sont pas aussi bien géré que pour Oracle.

[mnitu]

Voilà une requête équivalente.
Pourriez-vous la tester et me dire quelle est le résultat ?

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
16
17
18
19
20
21
22
23
24
 
SELECT COUNT(produit.ProduitId)
  FROM Produit produit
 Where Exists (Select Null
                 From GROUPETEST groupeTest
                Where produit.ProduitId = groupeTest.ProduitId
                  And Exists(Select Null
                               From BANC banc
                              Where banc.bancId = groupeTest.BancId 
                                And (banc.bancReference || banc.bancNumSerie LIKE '%BANC%' OR
                                     banc.bancName || banc.bancNumSerie LIKE '%BANC%')
                            )                 
                  And Exists(Select Null
                               From GROUPETESTNUMSERIE groupeTestNumSerie
                              Where groupeTestNumSerie.groupeTestId = groupeTest.groupeTestId
                                And groupeTestNumSerie.produitId =  groupeTest.produitId
                                And groupeTestNumSerie.siteId = 1
                                And Exists(Select Null
                                             From NUMSERIE numSerie
                                            Where numSerie.numSerieId = groupeTestNumSerie.numSerieId
                                              And numSerie.numSerie LIKE 'BS%'
                                           )
                             )                  
              )

[w3blogfr]

Je passe d'environ 9 secondes (Requete avec JOIN) à 6 secondes (Requête avec EXISTS). Cela pour les requêtes utilisant LIKE 'BS%'.

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                               
| Id  | Operation                         | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                               
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                               
|   0 | SELECT STATEMENT                  |                          |     1 |    18 |   583   (3)| 00:00:07 |                                                                                                                                                                                               
|   1 |  SORT AGGREGATE                   |                          |     1 |    18 |            |          |                                                                                                                                                                                               
|   2 |   NESTED LOOPS                    |                          |     1 |    18 |   583   (3)| 00:00:07 |                                                                                                                                                                                               
|   3 |    VIEW                           | VW_SQ_1                  |     1 |    13 |   581   (3)| 00:00:07 |                                                                                                                                                                                               
|   4 |     HASH UNIQUE                   |                          |     1 |    81 |            |          |                                                                                                                                                                                               
|   5 |      NESTED LOOPS SEMI            |                          |     1 |    81 |   581   (3)| 00:00:07 |                                                                                                                                                                                               
|*  6 |       HASH JOIN                   |                          |     1 |    46 |   580   (3)| 00:00:07 |                                                                                                                                                                                               
|   7 |        TABLE ACCESS BY INDEX ROWID| GROUPETESTNUMSERIE    |     1 |    19 |     2   (0)| 00:00:01 |                                                                                                                                                                                               
|   8 |         NESTED LOOPS              |                          |    17 |   595 |   573   (3)| 00:00:07 |                                                                                                                                                                                               
|   9 |          SORT UNIQUE              |                          |    12 |   192 |   560   (3)| 00:00:07 |                                                                                                                                                                                               
|* 10 |           TABLE ACCESS FULL       | NUMSERIE              |    12 |   192 |   560   (3)| 00:00:07 |                                                                                                                                                                                               
|* 11 |          INDEX RANGE SCAN         | GROUPETESTNUMSERIE_PK |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                               
|  12 |        TABLE ACCESS FULL          | GROUPETEST            |  6611 | 72721 |     7   (0)| 00:00:01 |                                                                                                                                                                                               
|* 13 |       TABLE ACCESS BY INDEX ROWID | BANC                  |     2 |    70 |     1   (0)| 00:00:01 |                                                                                                                                                                                               
|* 14 |        INDEX UNIQUE SCAN          | PK_BANC               |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                               
|* 15 |    INDEX UNIQUE SCAN              | PRODUIT_PK            |     1 |     5 |     1   (0)| 00:00:01 |                                                                                                                                                                                               
--------------------------------------------------------------------------------------------------------------                                                                                                                                                                                               
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                          
---------------------------------------------------                                                                                                                                                                                                                                                          
 
   6 - access("GROUPETESTNUMSERIE"."GROUPETESTID"="GROUPETEST"."GROUPETESTID" AND                                                                                                                                                                                                                            
              "GROUPETESTNUMSERIE"."PRODUITID"="GROUPETEST"."PRODUITID")                                                                                                                                                                                                                                     
  10 - filter("NUMSERIE"."NUMSERIE" LIKE 'BS%' AND UPPER("NUMSERIE") LIKE 'BS%')                                                                                                                                                                                                                             
  11 - access("NUMSERIE"."NUMSERIEID"="GROUPETESTNUMSERIE"."NUMSERIEID" AND                                                                                                                                                                                                                                  
              "GROUPETESTNUMSERIE"."SITEID"=1)                                                                                                                                                                                                                                                               
       filter("GROUPETESTNUMSERIE"."SITEID"=1)                                                                                                                                                                                                                                                               
  13 - filter("BANC"."BANCREFERENCE"||"BANC"."BANCNUMSERIE" LIKE '%BANC%' OR                                                                                                                                                                                                                                   
              "BANC"."BANCNAME"||"BANC"."BANCNUMSERIE" LIKE '%BANC%')                                                                                                                                                                                                                                          
  14 - access("BANC"."BANCID"="GROUPETEST"."BANCID")                                                                                                                                                                                                                                                         
  15 - access("PRODUIT"."PRODUITID"="PRODUITID")

j'ai essayé avec les requêtes LIKE '%BS%' où il fait un full scan de la table du coup. La requête avec les JOIN met environ 0,5 seconde contre tps>1 seconde avec les EXISTS.