Clé Primaire VARCHAR2(18) VS Integer?

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? :lol:).

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?

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

Code:

---

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%')

---

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:

---

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.

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:

---

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.

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

Code:

---

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

---

Cette requête utilise l'index.

Code:

---

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

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.

Je comprend bien pourquoi un index fonctionne bien avec un like 'BS%'. Cela est dû à la structure B-Tree qui classe les valeurs dans un arbre, et il parcours l'index du noeud parent vers les sous-noeuds.

Toutefois, j'ai compris quelques choses entre temps, Oracle utilise également cet index pour les requête like '%BS%' car ma colonne est unique. Si je désactive la contrainte, il fait un full scan, et ca va plus vite!

A ce propos, lorsqu'on crée un contrainte unique, Oracle crée un index implicite, mais est-ce exactement le même type d'index que si on crée un index unique?

Code:

---

1 2 3 4 5 6 7

ALTER TABLE NUMSERIE ADD CONSTRAINT NUMSERIE_UK1 UNIQUE ( NUMSERIE ) ENABLE;

---

Est-ce exactement la même chose que ca?

Code:

---

1 2	CREATE UNIQUE INDEX NUMSERIE_INDEX1 ON GWTNUMSERIE (NUMSERIE);

---

Du même type que :

Code:

---

1 2	/*+ index(<nom de table> <nom d'index>)*/

---

Existe-t-il de forcer un full scan au lieu d'utiliser un index?


Les données de ma base correspondent à un Dump de la production. Les statistiques n'étant pas bonne au départ, j'avais fait sur les tables:

Code:

---

1 2	analyze table NUMSERIE compute statistics;

---

Est-ce suffisant pour avoir des statistiques corrects?


Merci pour la réponse.

Citation:

---

Envoyé par ttropardy

...Toutefois, j'ai compris quelques choses entre temps, Oracle utilise également cet index pour les requête like '%BS%' car ma colonne est unique.
...

---

Montrez-le!

Citation:

---

Envoyé par ttropardy

...
Si je désactive la contrainte, il fait un full scan, et ca va plus vite!

---

Mauvais statiqtiques!

Citation:

---

Envoyé par ttropardy

...
A ce propos, lorsqu'on crée un contrainte unique, Oracle crée un index implicite, mais est-ce exactement le même type d'index que si on crée un index unique?

Code:

---

1 2 3 4 5 6 7

ALTER TABLE NUMSERIE ADD CONSTRAINT NUMSERIE_UK1 UNIQUE ( NUMSERIE ) ENABLE;

---

Est-ce exactement la même chose que ca?

Code:

---

1 2	CREATE UNIQUE INDEX NUMSERIE_INDEX1 ON GWTNUMSERIE (NUMSERIE);

---

---

Fait un test par vous même.

Citation:

---

Envoyé par ttropardy

Du même type que :

Code:

---

1 2	/*+ index(<nom de table> <nom d'index>)*/

---

Existe-t-il de forcer un full scan au lieu d'utiliser un index?

---

Oui mais, pour l'instant oubliez l'existence des Hints.

Citation:

---

Envoyé par ttropardy

...
Les données de ma base correspondent à un Dump de la production. Les statistiques n'étant pas bonne au départ, j'avais fait sur les tables:

Code:

---

1 2	analyze table NUMSERIE compute statistics;

---

Est-ce suffisant pour avoir des statistiques corrects?


...

---

Utilisez toujours DBMS_STATS. Quelle est la version d'Oracle ?

Citation:

---

Envoyé par ttropardy

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:

---

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 (:aie:) 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.

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:

---

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.

Bonjour,

Peut on avoir les plans ?

Les plans d'éxecution sont dans les 2 fichiers txt joint précédemment ;)

Pour l’instant laissé à coté les contraintes et autres modifications de la requête.
Commencez par calculer les statistiques sur toutes les tables et indexes de la requête.
Dans l’analyse de la requête on constate que vous sélectionnez que depuis la table Produit, et encore un peu en trop parce que vous utilisez distinct. Par contre tous vos critères sont ailleurs sur les autres tables. Le temps de réponse de ce type de requête dépende beaucoup de la capacité de filtrage dans les premières étapes du plan d’exécution.
Quand vous ajoutez la contrainte d’unicité il y a création d’un index unique. Cet index n’a pas des statistiques à jour par défaut. Il est nécessaire toujours dans ces conditions de calculer les statistiques pour l’index en question sinon les informations transmises à l’optimiseur sont incomplètes ce qui peut conduire à un mauvais plan.

Citation:

---

Envoyé par mnitu

Quand vous ajoutez la contrainte d’unicité il y a création d’un index unique. Cet index n’a pas des statistiques à jour par défaut. Il est nécessaire toujours dans ces conditions de calculer les statistiques pour l’index en question sinon les informations transmises à l’optimiseur sont incomplètes ce qui peut conduire à un mauvais plan.

---

Je ne suis pas sûr de bien comprendre ton propos, mais en 10g, tout index, implicite ou non, subit un calcul des statistiques à sa création.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> create table t as select * from dba_objects; TABLE T créé   Table créée.   SQL> alter table T add constraint TUQ unique(object_id); INDEX TUQ créé   Table modifiée.   SQL> select index_name, last_analyzed from user_indexes where index_name='TUQ';   INDEX_NAME LAST_ANALYZED ------------------------------ ------------------- TUQ 22/02/2011 17:33:28

---

Si la table et l'index ont étés créés avant l'insertion des données il serait bon de recalculer les stats sur les 2 objets.

D'autre part, il semblerait que le calcul simultané de statistiques par dbms_stats et analyze détraquerait l'optimiseur. Il faudrait donc se contenter d'utiliser une méthode (de préfrence dbms_stats).

L'insertion des données a été faite à la création de l'index. On m'a confirmé qu'un refresh dbms_stats avait lieu toutes les nuits. Mes calculs étaient donc peut-être faussé. Je vais donc attendre pour en savoir plus. Je vous tiens au courant.

Citation:

---

Envoyé par mnitu

Quand vous ajoutez la contrainte d’unicité il y a création d’un index unique.

---

Pas nécessairement.
Si un index existe déjà à la création de la contrainte, Oracle ne va pas en créer un nouveau.
De plus, dans le cas d'une contrainte d'unicité déferrable, l'index créé ne sera pas unique non plus.

Un index est un objet physique qui est là pour épauler la base de données, une contrainte est un élément fonctionnellement restrictif.
La contrainte d'unicité s'appuie toujours d'un index, mais ce dernier n'est pas forcément unique.

Que d'heure perdu pour des mauvaises statistiques :?

J'ai mis un schéma (ultra light) pour que ce soit plus claire. Pièce jointe 73525

A partir de GroupeTestNumSerie, il existe 2 liens vers produit (Direct et par groupeTest), mais c'est normal. L'un est le lien administratif, et l'autre réel.

Constatant dans tout les cas de meilleurs performances, j'ai préféré dé-normaliser un peu mes données pour éviter une jointure sur 2 tables importante.

J'ai donc mis le numéro de série dans GroupeTestNumSerie avec un index non unique (possibilité de doublon dans cette table).

Voici la nouvelle requête:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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.bancName LIKE '%BANC%') JOIN GROUPETESTNUMSERIE groupeTestNumSerie ON groupeTestNumSerie.groupeTestId=groupeTest.groupeTestId AND groupeTestNumSerie.produitId =produit.produitId AND groupeTestNumSerie.siteId =1 AND groupeTestNumSerie.numSerie LIKE 'BS%' WHERE 1=1

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 77 | 854 (3)| 00:00:11 | | 1 | SORT GROUP BY | | 1 | 77 | | | |* 2 | HASH JOIN | | 17 | 1309 | 854 (3)| 00:00:11 | | 3 | NESTED LOOPS | | 822 | 41922 | 12 (9)| 00:00:01 | |* 4 | HASH JOIN | | 822 | 37812 | 11 (10)| 00:00:01 | |* 5 | TABLE ACCESS FULL| BANC | 19 | 665 | 3 (0)| 00:00:01 | | 6 | TABLE ACCESS FULL| GROUPETEST | 6611 | 72721 | 7 (0)| 00:00:01 | |* 7 | INDEX UNIQUE SCAN | PRODUIT_PK | 1 | 5 | 1 (0)| 00:00:01 | |* 8 | TABLE ACCESS FULL | GROUPETESTNUMSERIE | 319K| 8115K| 838 (3)| 00:00:11 | -----------------------------------------------------------------------------------------------

---

Contrairement a hier, Oracle disposant des bonnes statistiques, il préfère bien le full scan à l'utilisation de l'index qui est plus lent. Et les temps de réponse sont similaire entre LIKE '%BS%' et LIKE 'BS%'.


En revanche, je re-testé l'ancienne requête en allant jusqu'à la table numSerie. Les temps de réponse sont toujours très lent

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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.bancName 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%' WHERE 1=1;

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 86 | 868 (3)| 00:00:11 | | 1 | SORT GROUP BY | | 1 | 86 | | | | 2 | NESTED LOOPS | | 2 | 172 | 868 (3)| 00:00:11 | |* 3 | HASH JOIN | | 20 | 1400 | 848 (3)| 00:00:11 | | 4 | NESTED LOOPS | | 822 | 41922 | 12 (9)| 00:00:01 | |* 5 | HASH JOIN | | 822 | 37812 | 11 (10)| 00:00:01 | |* 6 | TABLE ACCESS FULL | BANC | 19 | 665 | 3 (0)| 00:00:01 | | 7 | TABLE ACCESS FULL | GROUPETEST | 6611 | 72721 | 7 (0)| 00:00:01 | |* 8 | INDEX UNIQUE SCAN | PRODUIT_PK | 1 | 5 | 1 (0)| 00:00:01 | |* 9 | TABLE ACCESS FULL | GROUPETESTNUMSERIE | 372K| 6909K| 831 (2)| 00:00:10 | |* 10 | TABLE ACCESS BY INDEX ROWID| NUMSERIE | 1 | 16 | 1 (0)| 00:00:01 | |* 11 | INDEX UNIQUE SCAN | NUMSERIE_PK | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------

---

Que veut dire exactement TABLE ACCESS BY INDEX ROWID?

Citation:

---

Dans l’analyse de la requête on constate que vous sélectionnez que depuis la table Produit, et encore un peu en trop parce que vous utilisez distinct. Par contre tous vos critères sont ailleurs sur les autres tables. Le temps de réponse de ce type de requête dépende beaucoup de la capacité de filtrage dans les premières étapes du plan d’exécution.

---

Il existe un moyen de filtrer les distincts plus en amont dans ma requête?

Citation:

---

Envoyé par Pomalaix

Je ne suis pas sûr de bien comprendre ton propos, mais en 10g, tout index, implicite ou non, subit un calcul des statistiques à sa création.
...

---

@pomalaix
C'est vrai. je viens de faire un upgrade (ce n'était pas pareil en version 9)
Merci

Citation:

---

Envoyé par Waldar

Pas nécessairement.
Si un index existe déjà à la création de la contrainte, Oracle ne va pas en créer un nouveau.
De plus, dans le cas d'une contrainte d'unicité déferrable, l'index créé ne sera pas unique non plus.

Un index est un objet physique qui est là pour épauler la base de données, une contrainte est un élément fonctionnellement restrictif.
La contrainte d'unicité s'appuie toujours d'un index, mais ce dernier n'est pas forcément unique.

---

J'avais supposé le non-existence d'un index pour la colonne en question. Plus précisément, que le plan change due à la création de l'index avec des statistiques incomplètes. Ce qui est faux (voir la remarque du @pomalaix)
Mais, néanmoins ma remarque est incorrecte pour le cas général.

Citation:

---

Envoyé par w3blogfr

Que veut dire exactement TABLE ACCESS BY INDEX ROWID?

---

Ça veut dire qu'on a lu le bloc qui contient la ligne après avoir trouvé son adresse dans l'index.

Citation:

---

Envoyé par w3blogfr

Que d'heure perdu pour des mauvaises statistiques :?

---

Ca arrive souvent.

Citation:

---

Envoyé par w3blogfr

...
Constatant dans tout les cas de meilleurs performances, j'ai préféré dé-normaliser un peu mes données pour éviter une jointure sur 2 tables importante.

---

Vous renoncez un peu vite, non ?

Citation:

---

Envoyé par w3blogfr

Contrairement a hier, Oracle disposant des bonnes statistiques, il préfère bien le full scan à l'utilisation de l'index qui est plus lent. Et les temps de réponse sont similaire entre LIKE '%BS%' et LIKE 'BS%'.

---

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

Citation:

---

Envoyé par w3blogfr

...
En revanche, je re-testé l'ancienne requête en allant jusqu'à la table numSerie. Les temps de réponse sont toujours très lent

---

Hier ça avait l'air d'être bien ou je me trompe ? Cf. "Deuxième Requête avec like '%BS%'. Environ 1 secondes"

Citation:

---

Envoyé par w3blogfr

...
Que veut dire exactement TABLE ACCESS BY INDEX ROWID?

---

Qu'un index a été utilisé pour localiser les enregistrements et qu'ensuite le Rowid récupéré de l'index est utilisé pour accèder à la table.

Citation:

---

Envoyé par w3blogfr

...
Il existe un moyen de filtrer les distincts plus en amont dans ma requête?

---

Essayez d'écrire votre requête de la façon suivante

Code:

---

1 2 3 4 5 6

SELECT COUNT(produit.ProduitId) FROM Produit produit Where Exists (Select ... ) ...

---

Bonjour,

Juste un petit mot là dessus:

Citation:

---

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.

Citation:

---

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.

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

Citation:

---

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 ;).

Citation:

---

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?

Citation:

---

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.

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

Code:

---

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%' ) ) )

---

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

Code:

---

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.