Discussion :

[Ikebukuro]

Bonjour,

J'ai lu dans le livre de Thomas Kyte "Expert oracle Database Architecture" le fait que un nombre comme 1000000000 correspondait à 2 octets en "représentation interne Oracle" que l'on peut voir avec la fonction DUMP et pas à 10 comme je le pensais avec LENGTHB. Cela m'a intrigué et j'ai voulu comprendre un peu mieux ce concept en faisant des tests mais, pauvre de moi, j'y vois encore moins clair

J'ai créé deux tables avec les valeurs 1000000000 et 1999999999 en 100 000 exemplaires.

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
SQL> create table TEST(id01 NUMBER);
Table created.
 
SQL> insert into TEST (select 1000000000 FROM DUAL CONNECT BY LEVEL < 100001);
100000 rows created.
 
SQL> commit;
 
SQL> create table TEST02(ID02 NUMBER);
Table created.
 
SQL> insert into TEST02 (select 1999999999 FROM DUAL CONNECT BY LEVEL < 100001);
100000 rows created.
 
SQL> commit;

On voit que la fonction LENGTHB renvoit 10 octets mais VSIZE et DUMP ne renvoient que 2 octets si la valeur vaut 1000000000 et 6 pour la valeur 1999999999 --> sur le disque dur, il y a quoi, 10 octets ou bien 2 ou 6?

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
SQL> select MAX(ID01),MIN(ID01), MIN(LENGTH(ID01)), MAX(LENGTH(ID01)), MIN(LENGTHB(ID01)), MAX(LENGTHB(ID01)), MIN(VSIZE(ID01)), MAX(VSIZE(ID01)) from TEST group by ID01;
MAX(ID01)  MIN(ID01) MIN(LENGTH(ID01)) MAX(LENGTH(ID01)) MIN(LENGTHB(ID01)) MAX(LENGTHB(ID01)) MIN(VSIZE(ID01)) MAX(VSIZE(ID01))
---------- ---------- ----------------- ----------------- ------------------ ------------------ ---------------- ----------------
1000000000 1000000000		     10 	       10		  10		     10 	       2		2

SQL> select ID01, dump(ID01) from test where rownum = 1;
      ID01 DUMP(ID01)
---------- ------------------------------
1000000000 Typ=2 Len=2: 197,11


SQL> select MAX(ID02),MIN(ID02), MIN(LENGTH(ID02)), MAX(LENGTH(ID02)), MIN(LENGTHB(ID02)), MAX(LENGTHB(ID02)), MIN(VSIZE(ID02)), MAX(VSIZE(ID02)) from TEST02 group by ID02;
 MAX(ID02)  MIN(ID02) MIN(LENGTH(ID02)) MAX(LENGTH(ID02)) MIN(LENGTHB(ID02)) MAX(LENGTHB(ID02)) MIN(VSIZE(ID02)) MAX(VSIZE(ID02))
---------- ---------- ----------------- ----------------- ------------------ ------------------ ---------------- ----------------
1999999999 1999999999		     10 	       10		  10		     10 	       6		6

SQL> select ID02, dump(ID02) from test02 where rownum = 1	 
      ID02 DUMP(ID02)
---------- --------------------------------------------------
1999999999 Typ=2 Len=6: 197,20,100,100,100,100

Je regarde dans dba_segments et, surprise, je tombe sur 21 Ko par enregistrement, au lieu de 2 ou 6 ou 10, quelle que soit la valeur enregistrée.
D'un côté ça me rassure de voir que chaque enregistrement a la même taille mais pourquoi 21 au lieu de 10 (chiffre renvoyé par LENGTHB)? Il y a le DATA block header, un pctfree de 10% par défaut mais cet écart est énorme, non?

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
SQL> select sum(BYTES) from dba_segments where segment_name = 'TEST';
SUM(BYTES)
----------
   2097152
 
SQL> select sum(BYTES)/100000 from dba_segments where segment_name = 'TEST'; 
SUM(BYTES)/100000
-----------------
	 20.97152
 
SQL> select sum(BYTES) from dba_segments where segment_name = 'TEST02'
SUM(BYTES)
----------
   2097152
 
SQL> select sum(BYTES)/100000 from dba_segments where segment_name = 'TEST02'
SUM(BYTES)/100000
-----------------
	 20.97152

Si vous pouviez éclairer ma lanterne, par avance merci.

[Pomalaix]

Il y a une erreur de logique dans vos tests.

1) Comme LENGTHB est conçu pour s'appliquer à une chaîne de caractères, on va avoir une conversion implicite du numérique vers sa représentation en caractères avant d'en calculer la longueur.

On ne peut pas tester LENGTHB sur un numérique, mais on peut jouer avec VSIZE :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SQL> select vsize(to_char(1000000)) from dual;
 
VSIZE(TO_CHAR(1000000))
-----------------------
                      7
 
SQL> select vsize(1000000) from dual;
 
VSIZE(1000000)
--------------
             2

2) Diviser la taille du segment par le nombre de lignes est très approximatif, car en plus de l'en-tête des blocs et le PCTFREE, il y a aussi les blocs alloués au segment mais non utilisés.
Il sera déjà nettement plus juste de calculer les statistiques sur la table et de regarder AVG_ROW_LEN (longueur moyenne d'une ligne).

Code :

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

1
2
3
4
5
6
7
SQL> exec dbms_stats.gather_table_stats(user, 'TEST');
 
SQL> select avg_row_len from dba_tables where table_name='TEST' and owner=USER;
 
AVG_ROW_LEN
-----------
          3

Si on veut creuser l'espace inutilisé au sein de la table :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SQL> analyze table test compute statistics;
 
SQL> select blocks, empty_blocks, avg_space, avg_row_len 
from dba_tables 
where table_name='TEST' and owner=USER;
 
    BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN
---------- ------------ ---------- -----------
       152          103        841           6

Dans mon cas j'ai 103 blocs vides contre 152 d'utilisés, et par ailleurs j'ai 841 octets inutilisés par bloc (AVG_SPACE), ce qui correspond essentiellement au PCTFREE ici.
Par contre je ne sais pas expliquer pourquoi la longueur moyenne des lignes est désormais, soi-disant, de 6 octets après "analyze table compute statistics", alors qu'avec dbms_stats elle n'était que de 3.

[mnitu]

Comme précisé par Pomalaix vous "beneficiez" d'une coercition automatique, la fonction lengthb n'étant pas conçue pour travailler avec le type number.

...

On voit que la fonction LENGTHB renvoit 10 octets mais VSIZE et DUMP ne renvoient que 2 octets si la valeur vaut 1000000000 et 6 pour la valeur 1999999999 --> sur le disque dur, il y a quoi, 10 octets ou bien 2 ou 6?
...

Pour voir ce que se trouve sur les disque faite un dump du block

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
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> create table test(a number)
  2  /
Table created
 
SQL> insert into test(a) values (1000000000);
1 row inserted
 
SQL> insert into test(a) values(1999999999);
1 row inserted
 
SQL> commit;
Commit complete
 
SQL> 
SQL> Select file_id, relative_fno, extent_id, block_id, blocks
  2    from dba_extents
  3   where segment_name = 'TEST'
  4     And owner = 'MNI';
   FILE_ID RELATIVE_FNO  EXTENT_ID   BLOCK_ID     BLOCKS
---------- ------------ ---------- ---------- ----------
         4            4          0    1029224          8
 
SQL> alter system dump datafile 4 block min 1029231 block max 1029231;
System altered

Puis dans le fichier dump du block

Code :

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

1
2
3
4
5
6
7
8
9
 
block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c5 0b
tab 0, row 1, @0x1f88
tl: 10 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 6]  c5 14 64 64 64 64
end_of_block_dump

[Ikebukuro]

Je vous remercie pour vos réponses, je m'étais effectivement trompé avec l'utilisation de la fonction LENGTHB.

[Ikebukuro]

OK, j'ai eu le temps de refaire des tests et je suis toujours aussi perplexe

Tout d'abord, pourquoi est-ce que je me prends la tête sur ce problème? C'est que j'avais l'impression qu'entre un nombre stocké sous forme de VARCHAR2 ou de NUMBER, l'espace disque occupé pouvait varier du tout au tout. On le voit bien avec 1000000 dans une colonne VARCHAR2 (LENGTHB renvoit 7 octets) et une colonne NUMBER (VSIZE renvoit 2 octets).

Donc si je suis chez un client et que je constate que des nombres sont stockés dans des colonnes de type CHAR ou VARCHAR2, est-il pertinent de changer le type de ces colonnes pour gagner de l'espace disque, rendre les full table scans plus rapides... (indépendamment des programmes existants à modifier)?

La doc d'Oracle est un peu ambigüe : "VSIZE(expr) : VSIZE returns the number of bytes in the internal representation of expr." Est-ce que cette représentation interne a un impact sur le stockage ou bien aucun? J'ai fait d'autres tests, avec des champs VARCHAR2 et NUMBER, j'ai inséré 1000000 et 1999999 avec 100000 occurences et les tables occupent le même espace disque! A quoi sert alors VSIZE? A quoi ça me sert de savoir que 1000000 est stocké sur 2 octets (en représentation interne) et pas sur 7?

Merci pour vos réponses, j'ai hâte de clore cette discussion car elle m'embrouille les esprits

[mnitu]

Le non respecte de type des données a toujours des impacts négatifs sur le bon fonctionnement de la base: penser au statistiques, tri, validation des données, etc. Une base des données est supposé stockés des "vérités" ce qui permet via les operateurs relationnels d'en déduire des autres "vérités" et non pas être un dépotoir des données. Cela suppose entre autre que les données stockées sont valides ce qui reste toujours difficile d'assurer quand l'unique type de données connu est varchar2! Cela étant dit je pense que vous vous acharnez sur le mauvais chat quand il y en a des autres à fouetter! Je ne pense pas que vous allez trouver beaucoup des clients qui stockent le montant de leur investissement en format varchar! Et si vous trouvez un qui a stocké ses montants en format char alors un conseil: laissez le tranquille et ne perdez pas le temps avec lui; il existe certainement un dieux des "innocents" et c'est à lui de s'en occuper.

Puis le deuxième facteur que vous devez prendre en compte c'est que l'espace chez Oracle n'est pas alloué pour chaque valeur individuel d'une colonne dans un enregistrement. Donc vous créez une table et vous insérez un seul enregistrement: bingo vous occupez N fois l'espace nécessaire sur le disc parce que Oracle manipule des blocs d'une taille prédéfini. C'est à l'intérieur de ce bloc des données que votre valeur occupé 2 ou 7 octets. Et on parle pas non plus d'espace mis à coté pour les modifications des enregistrements etc. Avez-vous rencontrez beaucoup des clients qui essaye encore de garder 2 octets de plus en stockant que les deux dernières chiffre d'un millésime ? L'espace ne coût pas grand chose aujourd'hui! Puis vous arrivez à épargner 50 octets et vous consommez 1000 via un index inutile, etc. Et encore une fois regarder plutôt l'inflation de l'espace qui se produit quand vous modifiez la valeur 1000000000 en déduisant 1: vous passez de 2 à 6 octets.

[Ikebukuro]

Merci pour tes conseils mnitu, je me doutais bien que je cherchais des poux dans la tête d'Oracle mais bon, quand je suis obsédé par une idée, il me faut m'en débarrasser

OK, je vais laisser tomber MAIS je ne sais toujours pas quelle est l'utilité de VSIZE et de ce que Oracle appelle "représentation interne".

[mnitu]

Comme la documentation le dit vsize retourne le nombre d'octets en représentation interne.
Peut être que l'exemple suivante pourrait nous faire avancer

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
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> 
SQL> select vsize(cast(1 as number(10))) vnum,
  2         vsize(cast(1 as binary_double)) vdbl,
  3         vsize(cast(1 as char(10))) vchr,
  4         vsize(cast(1 as varchar2(10))) vvchr,
  5         vsize(cast(1 as nvarchar2(10))) nnum
  6    from dual
  7  /
      VNUM       VDBL       VCHR      VVCHR       NNUM
---------- ---------- ---------- ---------- ----------
         2          8         10          1          2
 
SQL>

Comme vous pouvez constater la "même valeur" en fonction du type de données a besoin de plus ou moins d'octets pour la "représenter". Finalement cela vous le connaissez déjà : pour n'importe quel autre langage de programmation le nombre d'octets utilisé pour stocker une valeur diffère avec le type de données. C'est la même chose avec Oracle: vsize vous dit
- si vous voulez stocker 1 comme type number je vais utiliser 2 octets
- si vous voulez le stocker comme varchar2 1 octets
- si c'est un double alors ça va être 10
etc.
Et cela signifie que 1 comme number n'est pas stocké comme 1 mais comme une autre combinaison des deux octets.

[SQLpro]

Le non respect des types des données a toujours des impacts négatifs sur le bon fonctionnement de la base : penser aux statistiques, tri, validation des données, etc. Une base des données est supposée stocker des "vérités" ce qui permet via les opérateurs relationnels d'en déduire d'autres "vérités" et non pas d'être un dépotoir des données.

Je dois dire que j'aime beaucoup et je crois que je vais re-citer cela avec permission et mention de l'auteur !!!

(avec mes petites corrections)

A +

[Ikebukuro]

J'avoue que moi aussi j'aime beaucoup l'expression "dépotoir de données".