Petites tables ou grandes tables. . . Quelles conséquences sur les performances ?

[skuatamad]

Envoyé par CinePhil

Beurk ! Pas de VARCHAR dans les clés ! Contre-performant ! En plus, je ne connais pas Oracle, mais si j'interprète correctement ce que je lis dans la doc, NUMBER(38) peut occuper beaucoup plus d'octets qu'un entier donc pas à choisir comme type pour une clé non plus.

Non en terme de performance tout dépénd du SGBD et sur Oracle ce genre d'affiramtion est fausse ! (même si le choix d'un number(38) peut être discuté) Je vous invite à lire les contributions de Pacman dans Vitesse exécution WHERE = selon type de donnée ainsi que le lien qu'il a posté pour ceux qui connaisse déjà Oracle.

Envoyé par Waldar

Enfin sur la performance d'un index en fonction de son type (littéral ou numérique), chez Oracle c'est similaire, chez SQL-Server c'est très différent, il n'y a pas de bonne réponse universelle.

Merci Waldar pour cette contribution plus pragmatique.

Envoyé par CinePhil

Sauf que le ROWID est en fait une chaîne de caractères donc un index moins performant pour une clé étrangère.

Non le ROWID est LA méthode d'accès à une table via un index sur Oracle.

Le ROWID identifie un enregistrement d'une table dans la base de données, à partir de l'adresse physique du bloc et du numéro d'enregistrement dans le bloc. Il est utilisé principalement dans les indexes pour pointer sur l'enregistrement de la table, et dans les tables pour les pointeurs des chained rows. C'est le moyen le plus direct car il permet d'aller directement sur le bloc qui contient l'enregistrement.

Exemple avec la célèbre table table emp de scott :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE "SCOTT"."EMP"
  (
    "EMPNO" NUMBER(4,0),
    "ENAME" VARCHAR2(10 BYTE),
    "JOB"   VARCHAR2(9 BYTE),
    "MGR"   NUMBER(4,0),
    "HIREDATE" DATE,
    "SAL"    NUMBER(7,2),
    "COMM"   NUMBER(7,2),
    "DEPTNO" NUMBER(2,0),
    CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
    CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
  )

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> select * from scott.emp where empno = 7839;


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

C'est évidemment un identifiant interne à Oracle qu'un développeur n'utilisera que très rarement (voir jamais) dans son code.

Je suis bien évidemment totalement en accord avec le fond de l'article même si l'exemple des numéros de téléphones semblera quelque peu extremiste pour la plus part des lecteurs. Comme d'habitude tout dépend des besoins !

PS : Tiré d'un autre post que vous avez peut être vu :

Envoyé par StringBuilder

Je travaille sur un ERP, qui s'appelle Generix.

Donc pas la peine de le basher sur "son" modèle alors qu'il n'y est pour rien (même s'il semble convaincu par le modèle de l'ERP en question alors que moi même après une très rapide lecture je le suis beaucoup moins...)

[Waldar]

Envoyé par skuatamad

Non le ROWID est LA méthode d'accès à une table via un index sur Oracle.

Pour être un peu plus précis, le rowid est une pseudo-colonne qui contient l'adresse physique d'une ligne. Les index enregistrent donc les rowid, mais on peut directement accéder à la table si on connait ces derniers :

Code :

SELECT * FROM emp WHERE rowid = '';

C'est très rapide ainsi !
Attention toutefois, le rowid n'étant pas une valeur immuable, il est interdit d'en coder "en dur" !

[CinePhil]

Envoyé par Waldar

Attention toutefois, le rowid n'étant pas une valeur immuable, il est interdit d'en coder "en dur" !

Par "pas immuable", je suppose que tu veux dire que sa valeur peut changer ?

Dans ce cas, ce n'est pas une bonne clé !

[Waldar]

Tout à fait, c'est pour ça que c'est une pseudocolonne.

Oracle ne laisse pas le choix, elle existe pour toutes les tables !

[StringBuilder]

Envoyé par Waldar

Tout à fait, c'est pour ça que c'est une pseudocolonne.

Oracle ne laisse pas le choix, elle existe pour toutes les tables !

Et elle remplace la clé pourrie auto-incrément dénuée de sens qu'on crée habituellement sur les autres SGBD pour "optimiser" la base.

L'intérêt de cette pseudo colonne, c'est qu'on peut utiliser des clés composites en varchar sans problème comme PK, sans que les performances soient réduites (puisque c'est le ROWID qui est utilisé au final)

[skuatamad]

Envoyé par StringBuilder

Et elle remplace la clé pourrie auto-incrément dénuée de sens qu'on crée habituellement sur les autres SGBD pour "optimiser" la base.

Pas du tout une clé technique sert à palier la faiblesse des clés fonctionnelles sujettes à modification.
Une clé fonctionnelle peut être immuable, elle peut alors être une PK.
Mais si la clé fonctionnelle immuable est composée de multiples colonnes et également FK dans d'autres tables, alors utiliser une séquence pour auto-incrémentée une clé allège l'écriture des jointures ainsi que la quantité de données à stocker.

Le rowid n'a strictement rien à voir avec les clés.

[iberserk]

Envoyé par skuatamad

Pas du tout une clé technique sert à palier la faiblesse des clés fonctionnelles sujettes à modification.
Une clé fonctionnelle peut être immuable, elle peut alors être une PK.
Mais si la clé fonctionnelle immuable est composée de multiples colonnes et également FK dans d'autres tables, alors utiliser une séquence pour auto-incrémentée une clé allège l'écriture des jointures ainsi que la quantité de données à stocker.

Le rowid n'a strictement rien à voir avec les clés.

+1

[Luc Orient]

Envoyé par skuatamad

...
Non le ROWID est LA méthode d'accès à une table via un index sur Oracle.

Idem en DB2 for z/OS (on parle de RID) :

Index pages that point directly to the data in tables are called leaf pages and are said to be on level 0. In addition to data pointers, leaf pages contain the key and record-ID (RID).

Source :
DB2 Administration Guide


Sauf erreur de ma part, il semble que SQL Server ait fait le choix curieux de stocker la clé primaire comme référence à la ligne dans les index secondaires ...

[SQLpro]

Pour le ROWID...
Normal à l'origine Oracle et DB2 (héritier de system R) sont basés sur le même moteur.
Il existe dans SQL Server mais n'est pas accessible (combinaison de n° de fichier, n° de page dans le fichier et n) de slot de ligne dans la page).

il semble que SQL Server ait fait le choix curieux de stocker la clé primaire comme référence à la ligne dans les index secondaires ...

Ce choix n'est pas curieux il est même très intéressant, c'est la notion d'index cluster...
Effectivement tous les index non cluster reposent sur la valeur de la clef de l'index cluster pour retrouver la ligne originale.
Cela permet beaucoup de choses, comme par exemple en cas de défragmentation de la table, ne pas toucher aux index !

A +

[pacmann]

Et cela permet donc de devoir toujours faire deux accès index au lieu d'un quand tu ne recherches pas sur la PK ?
Cool !

[CinePhil]

C'est quoi le problème ?

Code :

1
2
3
4
SELECT j.prj_nom
FROM projet j
INNER JOIN personne p ON p.prs_id = j.prj_id_chef
WHERE p.prs_nom = 'pacmann'

Je cherche 'pacmann' dans l'index sur prs_nom et je récupère l'identifiant prs_id qui me permet de faire la jointure directement avec la table des projets.

On s'en fout de savoir que 'pacmann' est dans le fichier 12 page 24 ligne 5 !

[Waldar]

Disons qu'avec un index cluster, la table est dans la PK, l'accès à l'un ou l'autre est équivalent.

Oracle propose la même fonctionnalité depuis la 8i avec les IOT, fonctionnalité malheureusement sous-utilisée, là où SQL-Server le propose quasiment par défaut.

[pacmann]

Non Cinephil, tu n'as pas compris !
Faudrait que t'arrêtes vraiment de dire non simplement parce que je ne dis pas "SQLPro, c'est super ton truc".

Code :

1
2
3
4
 
SELECT *
FROM person
WHERE p.prs_nom = 'pacmann'

Chercher 'pacmann' dans IDX_NOM : récupère <pk_pacmann>.

Cherche pk_pacmann dans IDX_PK_PERSON : récupère pointeur sur la table.

Accède à la ligne grâce au pointeur.

L'étape 2 est, si j'ai compris, l'ajout induit par le système décrit.

Sinon Waldar, Ok c'est dont une IOT ?

Mais ça ne change pas grand chose.
Si on regarde ça :
http://msdn.microsoft.com/fr-fr/library/ms177443.aspx

La dernière étape qui accède à la page de données, c'est comme si tu accédais par rowid... l'étape d'au-dessus dans notre cas de recherche : c'est comme si tu avais deux étages d'index, et le troisième étage pour la table.

Donc tu as parcouru un arbre pour ton premier index, tu reparcours l'arbre pour choper une adresse que tu pourrais déjà avoir.

Après je dis pas, peut-être que ça s'utilise sur des tables petites en tailles, avec un petit b-tree level...

[EDIT]
Et donc Waldar, sur une IOT, les indexes supplémentaires pointent sur la PK au lieu du ROWID ?

[Waldar]

Il n'y a plus de rowid avec une IOT car il n'y a plus de table, tout est dans l'index !
La méthode d'accès c'est donc par la PK directement !

Une petite comparaison toute simple :

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
CREATE TABLE pacmann_heap
(
    pacmann_id   integer      NOT NULL
  , pacmann_name varchar2(10) NOT NULL
  , constraint pk_pacmann_heap
      PRIMARY KEY (pacmann_id)
)
organization heap;
 
CREATE INDEX ix_pacmann_heap
ON pacmann_heap (pacmann_name);
 
CREATE TABLE pacmann_iot
(
    pacmann_id   integer      NOT NULL
  , pacmann_name varchar2(10) NOT NULL
  , constraint pk_pacmann_iot
      PRIMARY KEY (pacmann_id)
)
organization INDEX;
 
CREATE INDEX ix_pacmann_iot
ON pacmann_iot (pacmann_name);
 
 
INSERT INTO pacmann_heap (pacmann_id, pacmann_name) VALUES (1, 'Pacmann');
INSERT INTO pacmann_heap (pacmann_id, pacmann_name) VALUES (2, 'Régis');
 
INSERT INTO pacmann_iot (pacmann_id, pacmann_name)
SELECT pacmann_id, pacmann_name FROM pacmann_heap;
 
commit;
 
begin
  dbms_stats.gather_table_stats(user, 'PACMANN_HEAP');
  dbms_stats.gather_table_stats(user, 'PACMANN_IOT');
end;

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
SET linesize 125;
 
SELECT /*+ gather_plan_statistics */  pacmann_id, pacmann_name
  FROM pacmann_heap
 WHERE pacmann_name = 'Régis';
 
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST')); 
 
SELECT /*+ gather_plan_statistics */  pacmann_id, pacmann_name
  FROM pacmann_iot
 WHERE pacmann_name = 'Régis';
 
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));  
 
 
SELECT /*+ gather_plan_statistics */  pacmann_id, pacmann_name
  FROM pacmann_heap
 WHERE pacmann_id = 1;
 
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST')); 
 
SELECT /*+ gather_plan_statistics */  pacmann_id, pacmann_name
  FROM pacmann_iot
 WHERE pacmann_id = 1;
 
SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------------------                    
| Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                    
---------------------------------------------------------------------------------------------------------                    
|   0 | SELECT STATEMENT            |                 |      1 |        |      1 |00:00:00.01 |       2 |                    
|   1 |  TABLE ACCESS BY INDEX ROWID| PACMANN_HEAP    |      1 |      1 |      1 |00:00:00.01 |       2 |                    
|*  2 |   INDEX RANGE SCAN          | IX_PACMANN_HEAP |      1 |      1 |      1 |00:00:00.01 |       1 |                    
---------------------------------------------------------------------------------------------------------   

---------------------------------------------------------------------------------------------                                
| Id  | Operation        | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                
---------------------------------------------------------------------------------------------                                
|   0 | SELECT STATEMENT |                |      1 |        |      1 |00:00:00.01 |       1 |                                
|*  1 |  INDEX RANGE SCAN| IX_PACMANN_IOT |      1 |      1 |      1 |00:00:00.01 |       1 |                                
---------------------------------------------------------------------------------------------   

---------------------------------------------------------------------------------------------------------                    
| Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                    
---------------------------------------------------------------------------------------------------------                    
|   0 | SELECT STATEMENT            |                 |      1 |        |      1 |00:00:00.01 |       2 |                    
|   1 |  TABLE ACCESS BY INDEX ROWID| PACMANN_HEAP    |      1 |      1 |      1 |00:00:00.01 |       2 |                    
|*  2 |   INDEX UNIQUE SCAN         | PK_PACMANN_HEAP |      1 |      1 |      1 |00:00:00.01 |       1 |                    
---------------------------------------------------------------------------------------------------------    

----------------------------------------------------------------------------------------------                               
| Id  | Operation         | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                               
----------------------------------------------------------------------------------------------                               
|   0 | SELECT STATEMENT  |                |      1 |        |      1 |00:00:00.01 |       1 |                               
|*  1 |  INDEX UNIQUE SCAN| PK_PACMANN_IOT |      1 |      1 |      1 |00:00:00.01 |       1 |                               
----------------------------------------------------------------------------------------------    

Et où est la table IOT :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT segment_name, segment_type
  FROM user_segments
 WHERE segment_name LIKE '%PACMANN%';
 
 
SEGMENT_NAME         SEGMENT_TYPE        
-------------------- --------------------
PACMANN_HEAP         TABLE               
PK_PACMANN_HEAP      INDEX               
IX_PACMANN_HEAP      INDEX               
PK_PACMANN_IOT       INDEX               
IX_PACMANN_IOT       INDEX

Nulle part !

[pacmann]

Ok, je comprends.
Pour montrer ce que je voulais dire, je change un peu ton exemple :

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
 
SQL> CREATE TABLE pacmann_iot
  2  (
  3      pacmann_id   integer      NOT NULL
  4    , pacmann_name varchar2(10) NOT NULL
  5    , pacmann_name2 varchar2(10) NOT NULL
  6    , constraint pk_pacmann_iot
  7        PRIMARY KEY (pacmann_id)
  8  )
  9  organization INDEX;
 
TABLE crÚÚe.
 
EcoulÚ : 00 :00 :00.02
SQL> CREATE INDEX ix_pacmann_iot
  2  ON pacmann_iot (pacmann_name);
 
INDEX crÚÚ.
 
SQL> INSERT INTO pacmann_iot (pacmann_id, pacmann_name, pacmann_name2) VALUES (1, 'Pacmann', 'XXXXX');
 
1 ligne crÚÚe.
 
EcoulÚ : 00 :00 :00.00
SQL>
SQL> INSERT INTO pacmann_iot (pacmann_id, pacmann_name, pacmann_name2) VALUES (2, 'Régis', 'XXXXX');
 
1 ligne crÚÚe.

Test :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SQL> EXPLAIN PLAN FOR
  2  SELECT /*+index(a ix_pacmann_iot)*/ *
  3    FROM pacmann_iot a
  4   WHERE pacmann_name = 'Régis';
 
ExplicitÚ.
 
EcoulÚ : 00 :00 :00.00
SQL> SELECT * FROM TABLE(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
 
-------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |     1 |    27 |     1   (0)|
|*  1 |  INDEX UNIQUE SCAN| PK_PACMANN_IOT |     1 |    27 |     1   (0)|
|*  2 |   INDEX RANGE SCAN| IX_PACMANN_IOT |     1 |       |     1   (0)|
-------------------------------------------------------------------------

Quand tu fais une recherche par index, tu dois en faire une deuxième dans la foulée.
La différence avec un accès à une table par row_id ?
C'est que tu reparcours une arborescence.

Ton scan de PK_PACMANN_IOT s'il est sur 3 étage, on pourrait faire le parallèle avec une table normale et une PK normale, où ton index n'est que sur 2 étage, où tu choppes ton ROWID, et tu as une étape supplémentaire qui te conduit au bloc de données.
Et il faut faire le test, mais à mon avis quand tu prends une table qui des "grosses" lignes et que tu la mets sous IOT, tu peux te retrouver avec b-tree à pleins d'étages, non ?

Sous Oracle, on n'utilise pas tout le temps des IOT... parce que ce n'est toujours optimal je suppose
(Même si les principales raisons sont peut être ailleurs ? Sur les FTS ?)

[pacmann]

Juste une petite précision que j'ai lue en googlant, s'il n'y a pas accès par ROWID, c'est certes parce qu'il n'y a pas de table.

C'est surtout parce que l'index est soumis à trop "d'opérations de maintenance" dans la mesure où c'est un balanced b-tree index, et qu'il faudrait maintenir la modification de ce rowid...

Du coup il y a un urowid immuable, mais qui ne permet pas d'accès direct.

[Waldar]

Ton exemple est meilleur que le mien, tu as raison de rajouter une colonne.
J'aurai du y penser en voyant le plan.

Tu as raison, si le B*Tree de la PK comporte beaucoup d'étage ça augmente le nombre de scans...

Et on revient directement en relation avec l'article de SQLPro : beaucoup de petites tables limitent justement cet effet !

[CinePhil]

Keep cool (pac)man !

Envoyé par pacmann

Non Cinephil, tu n'as pas compris !
Faudrait que t'arrêtes vraiment de dire non simplement parce que je ne dis pas "SQLPro, c'est super ton truc".

J'ai pas dit non, j'ai dit :

C'est quoi le problème ?

Ensuite j'ai tenté, sans doute maladroitement et sans développer assez, d'expliquer comment je pensais que ça se passait.

Je n'ai pas tout compris à vos explications mais apparemment je n'ai pas encore assimilé tout le mécanisme interne des index. Il est vrai que je n'ai jamais eu besoin de le faire.

Enfin l'essentiel, dans le cadre de cette discussion, est là :

Envoyé par Waldar

Et on revient directement en relation avec l'article de SQLPro : beaucoup de petites tables limitent justement cet effet !

[Luc Orient]

Envoyé par Waldar

... Et on revient directement en relation avec l'article de SQLPro : beaucoup de petites tables limitent justement cet effet !

Et c'est encore pire avec SQL Server ... Seulement, DB2 (for z/OS et les autres sans doute ... ) ne travaillent pas comme cela et donc l'argument sur les performances (sans être totalement irrecevable) est moins percutant ...

[pacmann]

Ok, désolé, j'ai peut-être un peu sur-réagi notamment sur :

On s'en fout de savoir que 'pacmann' est dans le fichier 12 page 24 ligne 5 !

... que j'ai considéré comme un ton que tu n'emploies pas quand tu poses vraiment une question, mais plus une autre formulation de "tu dis n'importe quoi".

Bref, je suis de toutes façons d'accord sur les bienfaits possibles de "petites" lignes (après à vue d'oeil, je ne serais pas non plus catégorique et accepterais qu'il puisse y avoir des configuration où la "dénormalisation" puisse se justifier).

Par contre, je maintiens que c'est quand mieux de pouvoir choisir si ta table est index organised ou non
(Je vais faire quelques tests sur des tables pas trop grosse mais quand même un peu)

[EDIT] tout à fait d'accord Luc, ça fait un peu l'oeuf et la poule sur le coup