Blog d'un DBA sur le SGBD Oracle et SQL

03 novembre 2018

La taille allouée à une table et la taille utilisée par ses données : ce n'est pas la même chose!


Introduction
Cet article rejoint celui-ci "Evolution taille table : Insert, Delete, Truncate, Drop, Shrink, Move" de Mars 2017 : http://dbaoraclesql.canalblog.com/archives/2017/03/19/34974508.html. Mais cette fois je vais m'attarder sur la taille de la table versus la taille de ses données. Et ce n'est pas du tout la même chose comme nous allons le voir!


 

Points d'attention
NA.


 

Base de tests
N'importe quelle base Oracle; tests sur une 12.1.

 


 

Exemples
============================================================================================
CREATE TABLE, INSERTs et tailles
============================================================================================
Je crée une table de test toute simple, dans le tablespace USERS. Etant en mode deferred_segment_creation, le CREATE TABLE ne crée aucun segment avant le premier INSERT.
     SQL> CREATE TABLE zztest_taille(id NUMBER, nom VARCHAR2(50 CHAR));
     Table created.
     
     SQL> select tablespace_name from dba_tables where table_name = 'ZZTEST_TAILLE';
     TABLESPACE_NAME
     ------------------------------
     USERS
     
     SQL> select BYTES, BLOCKS from user_segments where segment_name = 'ZZTEST_TAILLE';
     no rows selected
     
     SQL> show parameter deferred
     NAME                            TYPE        VALUE
     ------------------------------------ --------------
     deferred_segment_creation       boolean     TRUE
     
J'insère 10 000 enregistrements.
     SQL> begin
       for i in 1..10000 loop
            insert into zztest_taille values(i, 'DUPOND');
       end loop;
       end;
       /
     PL/SQL procedure successfully completed.
     
     SQL> select count(*) from zztest_taille;
       COUNT(*)
     ----------
          10000
     
     SQL> commit;
     Commit complete.

Taille avec USER_SEGMENTS
La vue USER_SEGMENTS est renseigné automatiquement, sans que l'on ait à générer des stats. La définition des colonnes que j'utilise est la suivante :
     - BYTES : size, in bytes, of the segment.
     - BLOCKS : size, in Oracle blocks, of the segment.
     SQL> select BYTES, BLOCKS from user_segments where segment_name = 'ZZTEST_TAILLE';
          BYTES     BLOCKS
     ---------- ----------
         262144       32
     
Attention, dans USER_SEGMENTS, on a la taille totale allouée par Oracle à une table et non pas la taille des données de cette table :-) En clair, on a seulement la taille des blocs alloués au segment mais sans indication sur la taille des données dans ces blocs; en clair on peut avoir une taille largement supérieure à la réalité des données.
     
Taille avec LENGTHB
Si on calcule plus précisément la taille des données avec la fonction LENGTHB (qui donne en octets la taille du contenu d'une colonne), voilà ce qu'on obtient. Le résultat est juste, ne serait-ce que pour le champ NOM car 10 000 fois DUPOND, qui fait 6 octets, donne bien 60 000 octets. Pour la colonne ID je fais le pari que c'est correct aussi :-)

Nous obtenons une taille pour les données de 98 894 octets et non pas de 262 144 octets, soit presque trois fois moins!
     SQL> select sum(lengthb(id)), sum(lengthb(nom)) from zztest_taille;
     SUM(LENGTHB(ID)) SUM(LENGTHB(NOM))
     ---------------- -----------------
            38894         60000
     
A quoi est due cette différence? Lors des INSERT, Oracle a alloué plus de blocs qu'il n'en fallait exactement; n'oubliez pas qu'Oracle alloue des extents et non pas des blocs à l'unité. On a donc dans le segment des blocs d'un extent qui n'ont pas encore été utilisés mais qui font quand même partie du segment.
     SQL> select BYTES, blocks from user_segments where segment_name = 'ZZTEST_TAILLE';
          BYTES     BLOCKS
     ---------- ----------
         262144       32
     
     SQL> select BLOCK_SIZE from user_tablespaces where tablespace_name = 'USERS';
     BLOCK_SIZE
     ----------
           8192
     
     SQL> select 32 * 8192 from dual;
        32*8192
     ----------
         262144

Taille avec les stats    
Que nous disent les stats si on fait un calcul de celles-ci sur 100% de la table? Que la table occupe 28 blocs au lieu de 32 et que la longueur moyenne des enregistrements est de 11 caractères. Sachant que la table ne contient aucun caractère sur plus de 1 octet, on peut dire que, selon les stats, la table fait 10 000 * 11 = 110 000 octets. Il s'agit d'une approximation mais on se rapproche de la réalité.
La définition des colonnes de USER_TAB_STATISTICS que j'utilise est la suivante : 
     - NUM_ROWS : number of rows in the object.
     - BLOCKS : number of used blocks in the object.
     - EMPTY_BLOCKS : number of empty blocks in the object.
     - AVG_SPACE : average available free space in the object.
     - AVG_ROW_LEN : average row length, including row overhead.
     - SAMPLE_SIZE : sample size used in analyzing the table.

     SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'ZZTEST_TAILLE', estimate_percent=>100 );
     PL/SQL procedure successfully completed.
    
     SQL> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, SAMPLE_SIZE from user_tab_statistics where table_name = 'ZZTEST_TAILLE';
       NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  AVG_ROW_LEN SAMPLE_SIZE
      ---------- ---------- ------------ ---------- ----------- -----------
         10000       28           0            0          11       10000

Taille avec DBMS_SPACE.UNUSED_SPACE     
Maintenant utilisons le package DBMS_SPACE, servant justement à avoir des infos plus précises sur la gestion de l'espace dans un objet comme une table ou un index. En premier nous utiliserons la fonction DBMS_SPACE.UNUSED_SPACE.
J'exécute le code de Tim HALL trouvé ici : https://oracle-base.com/dba/script?category=monitoring&file=unused_space.sql
Contenu du fichier taille.sql
     SET SERVEROUTPUT ON
     SET VERIFY OFF
     DECLARE
       v_total_blocks              NUMBER;
       v_total_bytes               NUMBER;
       v_unused_blocks             NUMBER;
       v_unused_bytes              NUMBER;
       v_last_used_extent_file_id  NUMBER;
       v_last_used_extent_block_id NUMBER;
       v_last_used_block           NUMBER;
     BEGIN
         DBMS_SPACE.UNUSED_SPACE (segment_owner              => UPPER('HR'),
                                  segment_name               => UPPER('ZZTEST_TAILLE'),
                                  segment_type               => UPPER('TABLE'),
                                  total_blocks               => v_total_blocks,
                                  total_bytes                => v_total_bytes,
                                  unused_blocks              => v_unused_blocks,
                                  unused_bytes               => v_unused_bytes,
                                  last_used_extent_file_id   => v_last_used_extent_file_id,
                                  last_used_extent_block_id  => v_last_used_extent_block_id,
                                  last_used_block            => v_last_used_block);
       DBMS_OUTPUT.PUT_LINE('v_total_blocks              :' || v_total_blocks);
       DBMS_OUTPUT.PUT_LINE('v_total_bytes               :' || v_total_bytes);
       DBMS_OUTPUT.PUT_LINE('v_unused_blocks             :' || v_unused_blocks);
       DBMS_OUTPUT.PUT_LINE('v_unused_bytes              :' || v_unused_bytes);
     END;
     /
         
On a les mêmes tailles que dans USER_SEGMENTS!    
     SQL> @taille
     v_total_blocks            :32
     v_total_bytes            :262144
     v_unused_blocks         :0
     v_unused_bytes            :0
     PL/SQL procedure successfully completed.
    
Taille avec DBMS_SPACE.SPACE_USAGE
Nous allons voir maintenant le taux de remplissage des blocs avec une autre fonction du même package, DBMS_SPACE.SPACE_USAGE.
J'utilise le code de Donald BURLESON trouvé ici : http://www.dba-oracle.com/t_packages_dbms_space_usage.htm
Et là encore, nous avons des données différentes... alors qu'on utilise deux fonctions du même package...
Contenu du fichier taille02.sql
     set serveroutput on
     declare
        v_unformatted_blocks number;
        v_unformatted_bytes  number;
        v_fs1_blocks         number;
        v_fs1_bytes          number;
        v_fs2_blocks         number;
        v_fs2_bytes          number;
        v_fs3_blocks         number;
        v_fs3_bytes          number;
        v_fs4_blocks         number;
        v_fs4_bytes          number;
        v_full_blocks        number;
        v_full_bytes         number;
      begin
        dbms_space.space_usage('HR',
                               'ZZTEST_TAILLE',
                               'TABLE',
                               v_unformatted_blocks,
                               v_unformatted_bytes,
                               v_fs1_blocks,
                               v_fs1_bytes,
                               v_fs2_blocks,
                               v_fs2_bytes,
                               v_fs3_blocks,
                               v_fs3_bytes,
                               v_fs4_blocks,
                               v_fs4_bytes,
                               v_full_blocks,
                               v_full_bytes);
        dbms_output.put_line('Unformatted Blocks = ' || v_unformatted_blocks);
        dbms_output.put_line('Unformatted Bytes = ' || v_unformatted_bytes);
        dbms_output.put_line('FS1 Bytes (at least 0 to 25% free space) = ' || v_fs1_bytes);
        dbms_output.put_line('FS1 Blocks(at least 0 to 25% free space) = ' || v_fs1_blocks);
        dbms_output.put_line('FS2 Bytes (at least 25 to 50% free space)= ' || v_fs2_bytes);
        dbms_output.put_line('FS2 Blocks(at least 25 to 50% free space)= ' || v_fs2_blocks);
        dbms_output.put_line('FS3 Bytes (at least 50 to 75% free space) = ' || v_fs3_bytes);
        dbms_output.put_line('FS3 Blocks(at least 50 to 75% free space) = ' || v_fs3_blocks);
        dbms_output.put_line('FS4 Bytes (at least 75 to 100% free space) = ' || v_fs4_bytes);
        dbms_output.put_line('FS4 Blocks(at least 75 to 100% free space)= ' || v_fs4_blocks);
        dbms_output.put_line('Full Blocks in segment = ' || v_full_blocks);
        dbms_output.put_line('Full Bytes in segment  = ' || v_full_bytes);
      end;
     /
     
     SQL> @taille02
     Unformatted Blocks = 0
     Unformatted Bytes = 0
     FS1 Bytes (at least 0 to 25% free space) = 0
     FS1 Blocks(at least 0 to 25% free space) = 0
     FS2 Bytes (at least 25 to 50% free space)= 0
     FS2 Blocks(at least 25 to 50% free space)= 0
     FS3 Bytes (at least 50 to 75% free space) = 0
     FS3 Blocks(at least 50 to 75% free space) = 0
     FS4 Bytes (at least 75 to 100% free space) = 49152
     FS4 Blocks(at least 75 to 100% free space)= 6
     Full Blocks in segment = 22
     Full Bytes in segment  = 180224
     PL/SQL procedure successfully completed.

Taille avec AWR et DBA_HIST_SEG_STAT     
Voyons voir maintenant le contenu d'une vue AWR, la vue DBA_HIST_SEG_STAT. Elle a plus d'infos que la vue USER_SEGMENTS et, surtout, elle renferme deux notions différentes : l'espace alloué et l'espace utilisé! C'est là la clé du sujet : Oracle alloue plus de blocs qu'il n'en utilise pour stocker les données.
     - SPACE_USED_TOTAL : Number of bytes used by user data.
     - SPACE_USED_DELTA : Delta value for space used by user data (in bytes). A negative value indicates the number of bytes deleted in the segment.
     - SPACE_ALLOCATED_TOTAL : The number of bytes that are allocated.
     - SPACE_ALLOCATED_DELTA : Delta value for the space allocated (in bytes). A negative value indicates the number of bytes deallocated to the tablespace.
     SQL> SELECT SEG.SNAP_ID, SNA.BEGIN_INTERVAL_TIME, SPACE_ALLOCATED_TOTAL, SEG.SPACE_USED_TOTAL
     FROM DBA_HIST_SEG_STAT SEG, DBA_HIST_SEG_STAT_OBJ OBJ, DBA_HIST_SNAPSHOT SNA
     WHERE SEG.OBJ# = OBJ.OBJ# AND
     SEG.SNAP_ID = SNA.SNAP_ID AND
     OBJ.OBJECT_NAME = upper('ZZTEST_TAILLE') AND
     OBJ.OWNER = 'HR'
     ORDER BY SEG.SNAP_ID;
     SNAP_ID     BEGIN_INTERVAL_TIME       SPACE_ALLOCATED_TOTAL SPACE_USED_TOTAL 
     ---------- ------------------------- --------------------- -----------------
     70          01-NOV-18 07.00.02.687 AM      262144             146554             

   
============================================================================================
Synthèse des tailles suite à un CREATE TABLE et des INSERTs
============================================================================================
A ce niveau nous avons les sept tailles suivantes, de la plus grande à la plus petite :
Trois résultats avec la même taille :
     - USER_SEGMENTS : 262 144 octets ==> taille réservée par Oracle pour la table et non pas taille des données. Largement sur-estimée par rapport aux données MAIS donne la taille d'un objet utilisé pour un DATAPUMP, une sauvegarde RMAM, un FULL TABLE SCAN...
     - DBMS_SPACE.UNUSED_SPACE : 262 144 octets ==> même taille que dans user_segments.
     - DBA_HIST_SEG_STAT : colonne SPACE_ALLOCATED_TOTAL 262 144 octets ==> même taille que dans user_segments.
     
Quatre résultats avec des tailles diféfrentes :
     - DBMS_SPACE.SPACE_USAGE : 180 224 octets
     - DBA_HIST_SEG_STAT : colonne SPACE_USED_TOTAL 146 554 octets : estimation de la taille des données.
     - USER_TAB_STATISTICS : 110 000 octets ==> taille des données : c'est une estimation, même si on a décidé d'analyser toute la table.
     - Calcul avec la table : 98 894 octets ==> taille des données : c'est le calcul le plus précis mais aussi le plus long selon le nombre de lignes dans la table puisque cela oblige Oracle à faire un FULL TABLE SCAN.    
     

============================================================================================
DELETE et influence sur les tailles
============================================================================================
Poursuivons nos tests en supprimant 90% des données de façon homogène dans les blocs, pour éviter d'avoir des blocs vides et d'autres pleins.
     SQL> delete from zztest_taille where mod(id,10) <> 0;
     9000 rows deleted.
     
     SQL> commit;
     Commit complete.
     
     SQL> select count(*) from zztest_taille;
       COUNT(*)
     ----------
           1000
     
Et maintenant, quels sont les impacts sur les tailles allouées et utilisées?
     SQL> select BYTES, BLOCKS from user_segments where segment_name = 'ZZTEST_TAILLE';
          BYTES     BLOCKS
     ---------- ----------
         262144       32
     
     SQL> select sum(lengthb(id)), sum(lengthb(nom)) from zztest_taille;
     SUM(LENGTHB(ID)) SUM(LENGTHB(NOM))
     ---------------- -----------------
             3893          6000
     
     SQL> exec dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'ZZTEST_TAILLE', estimate_percent=>100 );
     PL/SQL procedure successfully completed.
             
     SQL> select NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, SAMPLE_SIZE from user_tab_statistics where table_name = 'ZZTEST_TAILLE';        
       NUM_ROWS  BLOCKS     EMPTY_BLOCKS  AVG_SPACE  AVG_ROW_LEN SAMPLE_SIZE
     ---------- ---------- ------------ ------------ ----------- -----------
           1000     28        0                0        11            1000
             
     SQL> @taille
     v_total_blocks            :32
     v_total_bytes            :262144
     v_unused_blocks         :0
     v_unused_bytes            :0
     PL/SQL procedure successfully completed.
     
Tiens, un résultat étonnant : 0 bytes sans explication :-)         
     SQL> @taille02       
     Unformatted Blocks = 0
     Unformatted Bytes = 0
     FS1 Bytes (at least 0 to 25% free space) = 0
     FS1 Blocks(at least 0 to 25% free space) = 0
     FS2 Bytes (at least 25 to 50% free space)= 0
     FS2 Blocks(at least 25 to 50% free space)= 0
     FS3 Bytes (at least 50 to 75% free space) = 0
     FS3 Blocks(at least 50 to 75% free space) = 0
     FS4 Bytes (at least 75 to 100% free space) = 229376
     FS4 Blocks(at least 75 to 100% free space)= 28
     Full Blocks in segment = 0
     Full Bytes in segment  = 0
     PL/SQL procedure successfully completed.
     
     SQL> SELECT SEG.SNAP_ID, SNA.BEGIN_INTERVAL_TIME, SPACE_ALLOCATED_TOTAL, SEG.SPACE_USED_TOTAL
     FROM DBA_HIST_SEG_STAT SEG, DBA_HIST_SEG_STAT_OBJ OBJ, DBA_HIST_SNAPSHOT SNA
     WHERE SEG.OBJ# = OBJ.OBJ# AND
     SEG.SNAP_ID = SNA.SNAP_ID AND
     OBJ.OBJECT_NAME = upper('ZZTEST_TAILLE') AND
     OBJ.OWNER = 'HR'
     ORDER BY SEG.SNAP_ID;
        SNAP_ID BEGIN_INTERVAL_TIME      SPACE_ALLOCATED_TOTAL  SPACE_USED_TOTAL
     ---------- ------------------------- --------------------- ----------------
         70 01-NOV-18 07.00.02.687 AM    262144                 146554           


============================================================================================
Synthèse des tailles suite à un DELETE
============================================================================================

Quelle influence a eu ce DELETE massif sur les deux tailles, allouée et utilisée?
Trois tailles identiques : taille allouée par Oracle pour le segment de la table.
     - USER_SEGMENTS : 262 144 octets, la taille de la table n'a pas changé malgré le DELETE! Normal, Oracle a alloué à un instant t N blocs pour un segment, il garde ces blocs pour les futurs INSERTs ou UPDATEs; les seuls moyens de ramener la taille du segment à un niveau plus juste en rapport à la taille des données est de faire un MOVE, un SHRINK, un TRUNCATE (avec éventuellement une sauvegarde des données avant dans une table temporaire puis faire un INSERT dans la table vidée ...).
     - DBMS_SPACE.UNUSED_SPACE : 262 144 octets, la taille n'a pas changé malgré le DELETE!
     - DBA_HIST_SEG_STAT : j'attends d'avoir un snapshot AWR de pris :-)

Quatre tailles différentes pour l'espace alloué aux données.    
     - DBMS_SPACE.SPACE_USAGE : 229 376 octets.
     - DBA_HIST_SEG_STAT : j'attends d'avoir un snapshot AWR de pris :-)
     - USER_TAB_STATISTICS : 11 000 octets, le DELETE est bien pris en compte.
     - Calcul avec la table : 9 893 octets, le DELETE là aussi est bien pris en compte.
 

============================================================================================
Conclusion
============================================================================================
Faites attention quand vous parlez de taille ou d'espace de stockage sous Oracle. Il y a fondamentalement deux concepts : la taille réservée à l'objet via son segment et la taille des données proprement dite dans ces objets (table, index...).

Quelle différence vous me direz? Lorsqu'on fait du Datapump, RMAN ou un FULL TABLE SCAN, Oracle va traiter le nombre de blocs sous le HWM (High Water Mark), dont certains peuvent être vides mais qui ont contenu à un moment donné des enregistrements : cela entraîne de nombreuses lectures sur le disque dur, très coûteuses en temps. En revanche, dans le cas d'un SORT ou du hachage d'une table, Oracle va devoir lire tous les blocs de la requête MAIS la taille réelle des données joue un rôle important dans ce cas : par exemple une table de 100 000 blocs mais avec seulement 50 000 rows aura un SORT (lié à un ORDER BY) qui ira bien plus vite qu'une table de 100 000 blocs mais contenant 1 000 000 de rows :-)

Dans la vraie vie, faisons une comparaison avec deux documents word de 100 pages : l'un avec les pages noircies de mots, l'autre avec juste un mot par page. Dans les deux cas, l'impression des cent pages prendra le même temps (essayez!) MAIS la lecture des pages se fera 100 fois plus vite dans le deuxième cas. Pour Oracle c'est pareil, on lit le nombre de blocs alloués mais on traite ensuite uniquement les données récupérées dans ces blocs.

Vous avez compris le problème? Une table avec 10Go de données qui est vidée par un DELETE continue d'occuper 10Go sur le disque dur alors qu'il n'y a plus un seul enregistrement dedans... Donc pensez-y quand vous regardez la taille des objets les plus volumineux dans DBA_SEGMENTS; cette info peut-être fausse!


Posté par David DBA à 16:04 - - Permalien [#]
Tags : , ,


20 octobre 2018

Index créé par une contrainte d'intégrité ou non : comment les identifier?

Introduction
Est-ce que vous savez comment distinguer les index créés via les contraintes d'intégrité PRIMARY KEY et UNIQUE des index créés à la demande? Non? OK, on va voir ça, en route vers le coeur d'Oracle :-)


 
Points d'attention
Aucun.



Base de tests
N'importe quelle base Oracle.



Exemples
============================================================================================
La table du dictionnaire de données IND$
============================================================================================

Premier point, il n'existe aucun attribut dans DBA_INDEXES disant si un index a été créé par une contrainte d'intégrité ou non. Une fois de plus, il va falloir se tourner vers le vrai dictionnaire de données et pas vers ses vues puisque celles-ci ne récupèrent pas tous les attributs des tables sous-jacentes. Pour notre besoin, cette table s'appelle IND$.

Dans $ORACLE_HOME/rdbms/admin/dcore.bsq, il y a le script de création de cette table SYS.IND$. La colonne PROPERTY est celle qui va nous donner l'info : si la valeur en hexadecimal vaut 0x1000, alors "The index was created by a constraint".

Si bitand(PROPERTY, 4096)=4096 (correspondant au 1000 hexa), ça signifie que l'index a été créé automatiquement donc lié à une contrainte d'intégrité. De plus, si bitand(PROPERTY, 1)=1, il s'agit d'un index unique. Donc si l'index est créé automatiquement et unique (c'est à dire bitand(PROPERTY, 4097)=4097), il sera supprimé par le DROP CONSTRAINT d'une contrainte PRIMARY KEY ou UNIQUE.


C'est confus? Allez, on teste! Extrait du fichier dcore.bsq et composition de notre base.
     create table ind$   /* index table */
     ...
     property      number not null,    /* immutable flags for life of the index */
             /* unique : 0x01 */
             /* partitioned : 0x02 */
             /* reverse : 0x04 */
             /* compressed : 0x08 */
             /* functional : 0x10 */
             /* temporary table index: 0x20 */
             /* session-specific temporary table index: 0x40 */
             /* index on embedded adt: 0x80 */
             /* user said to check max length at runtime: 0x0100 */
             /* domain index on IOT: 0x0200 */
             /* join index : 0x0400 */
             /* system managed domain index : 0x0800 */
             /* The index was created by a constraint : 0x1000 */
             /* The index was created by create MV : 0x2000 */
             /* composite domain index : 0x8000 */


     SQL> select property, count(*) from sys.ind$ group by property order by 1;
     PROPERTY   COUNT(*)
     ---------- ----------
         0        521
         1       1025
         2         65
         3          6
        16         21
        17         11
        32          1
        96          3
        97          3
      4096          1
      4097        667
      4099         44
      4113          2
      4129          4
     16384          1
     16385          1
      16 lignes selectionnees.

============================================================================================
Création des différents types d'index
============================================================================================
 
On créé une table avec une PK et une contrainte UNIQUE nommées; cela va créer deux index qui seront utilisés par Oracle pour valider l'unicité des valeurs de ces colonnes.
     CREATE TABLE zztab
     (
       id number(10) CONSTRAINT zztab_pk_id PRIMARY KEY,
       name varchar2(50 char) CONSTRAINT zztab_uniq_name UNIQUE,
       mail varchar2(100 char)
     );
 
Insertion de données et commit.
     SQL> insert into zztab values(1,'test1', 'mail1');
     SQL> insert into zztab values(2, 'test2', 'mail2');
     SQL> commit;
 
On crée maintenant un index UNIQUE mais non lié à une contrainte : de la sorte nous avons trois index, des trois types qui nous intéressent.
     SQL> CREATE INDEX zztab_uniq_mail ON zztab(mail);
 
Les  index ont été créés.
     SQL> select table_name, INDEX_NAME, INDEX_TYPE, UNIQUENESS from user_indexes order by 1, 2;
     TABLE_NAME       INDEX_NAME           INDEX_TYPE        UNIQUENES
     ------------------------------------------------------------------
     ZZTAB            ZZTAB_PK_ID          NORMAL            UNIQUE
     ZZTAB            ZZTAB_UNIQ_MAIL      NORMAL            NONUNIQUE
     ZZTAB            ZZTAB_UNIQ_NAME      NORMAL            UNIQUE
 
ID de la table et des index.
     SQL> select object_id from dba_objects where owner = 'ZZTEST' and OBJECT_NAME = 'ZZTAB';
     OBJECT_ID
     ----------
     151254
 
     SQL> select object_id, object_name from dba_objects where owner = 'ZZTEST' and OBJECT_NAME in ('ZZTAB_PK_ID', 'ZZTAB_UNIQ_NAME', 'ZZTAB_UNIQ_MAIL') order by 2;               
     OBJECT_ID     OBJECT_NAME
     ---------- ---------------
     151255         ZZTAB_PK_ID
     151257         ZZTAB_UNIQ_MAIL
     151256         ZZTAB_UNIQ_NAME
 
On vérifie maintenant dans IND$ la valeur du champ PROPERTY.
Bingo, il y a bien une  différence entre un index lié à une contrainte (valeur 4096 ou 4097 si index UNIQUE) et un index non lié à une contrainte (valeur <> de 4096 et de 4097).
     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (151255, 151257, 151256) order by O.object_name;
     OBJ#     OBJECT_NAME            PROPERTY
     ---------- -------------------- ----------
     151255     ZZTAB_PK_ID                4097
     151257     ZZTAB_UNIQ_MAIL               0
     151256     ZZTAB_UNIQ_NAME            4097


============================================================================================
Liens entre contraintes et index
============================================================================================
Si on droppe les contraintes PK et UNIQUE : est-ce que les index liés sont supprimés en même temps?
     SQL> alter table zztab drop constraint ZZTAB_PK_ID;
     SQL> alter table zztab drop constraint ZZTAB_UNIQ_NAME;
 
Ces index ont bien été supprimés avec leur contrainte : normal puisque leur existence était liée justement à cette contrainte.
     SQL> select table_name, INDEX_NAME from user_indexes order by 1, 2;
     TABLE_NAME    INDEX_NAME
     ---------------------------
     ZZTAB       ZZTAB_UNIQ_MAIL
 
Maintenant, créons un index unique sur une colonne sans passer par une contrainte.
     SQL> CREATE UNIQUE INDEX ZZTAB_PK_ID_NO_CONS ON zztab(id);
 
     SQL> select table_name, INDEX_NAME from user_indexes order by 1, 2;
     TABLE_NAME   INDEX_NAME
     -------------------------------
     ZZTAB        ZZTAB_PK_ID_NO_CONS
     ZZTAB        ZZTAB_UNIQ_MAIL
 
     SQL> select object_id from dba_objects where owner = 'ZZTEST' and OBJECT_NAME = 'ZZTAB_PK_ID_NO_CONS';          
     OBJECT_ID
     ----------
     151258
 
Vérification dans IND$ la valeur du champ PROPERTY. Tiens, quand je crée un index non UNIQUE, la colonne PROPERTY vaut 0 et quand l'index est UNIQUE, cette colonne vaut 1.
     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (151258);
     OBJ#       OBJECT_NAME            PROPERTY
     ---------- -------------------- ----------
     151258     ZZTAB_PK_ID_NO_CONS           1
 
Maintenant on crée une contrainte PK sur la colonne indexée et on vérifie si c'est cet index existant qui est utilisé et non pas un nouveau créé spécialement par Oracle.
Effectivement, aucun nouvel index n'a été créé par Oracle.
     SQL> ALTER TABLE zztab ADD CONSTRAINT ZZTAB_PK_ID PRIMARY KEY (id);
     SQL> select table_name, INDEX_NAME from user_indexes order by 1, 2;
     TABLE_NAME   INDEX_NAME
     -------------------------------
     ZZTAB        ZZTAB_PK_ID_NO_CONS
     ZZTAB        ZZTAB_UNIQ_MAIL
 
Il n'y a qu'une contrainte dans la table.
     SQL> select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints order by 1;
     TABLE_NAME    CONSTRAINT_NAME      C
     -------------------- --------------------
     ZZTAB         ZZTAB_PK_ID          P

     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (151258, 151257);
     OBJ#       OBJECT_NAME            PROPERTY
     ------- -------------------- ----------
     151257     ZZTAB_UNIQ_MAIL               0
     151258     ZZTAB_PK_ID_NO_CONS           1
 
Suppression de la contrainte d'intégrité PK : l'index associé n'est pas supprimé, à la différence du premier test.
     SQL> ALTER TABLE zztab DROP CONSTRAINT ZZTAB_PK_ID ;
     SQL> select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints order by 1;
     aucune ligne selectionnee
 
C'est OK : la suppression de la contrainte PRIMARY KEY n'a pas supprimé l'index dans ce test-ci.
     SQL> select table_name, INDEX_NAME from user_indexes order by 1, 2;
     TABLE_NAME   INDEX_NAME
     -------------------------------
     ZZTAB        ZZTAB_PK_ID_NO_CONS
     ZZTAB        ZZTAB_UNIQ_MAIL
 
Et ses caractéristiques n'ont pas changé.
     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (151258, 151257);
     OBJ#     OBJECT_NAME            PROPERTY
     ---------- -------------------- ----------
     151257   ZZTAB_UNIQ_MAIL               0
     151258   ZZTAB_PK_ID_NO_CONS           1
 
Allez, on refait un test : ajout d'une colonne avec une contrainte unique.
     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (select object_id from user_objects where OBJECT_NAME in (select index_name from user_indexes where table_name = 'ZZTAB')) order by 2;
     OBJ#       OBJECT_NAME                PROPERTY
     ---------- ----------------------------------------
     151258     ZZTAB_PK_ID_NO_CONS        1
     151257     ZZTAB_UNIQ_MAIL            0
 
     SQL> ALTER TABLE zztab ADD id02 NUMBER(10);
     SQL> ALTER TABLE zztab MODIFY id02 CONSTRAINT uniq_id02 UNIQUE;
 
Voilà, on a tous les types d'index : PRIMARY KEY, UNIQUE, dépendant ou non d'une contrainte.
     SQL> SELECT I.obj#, O.object_name, I.property
     FROM sys.ind$ I, dba_objects O
     WHERE I.obj#=O.object_id and I.obj# in
         (select object_id
          from user_objects
          where OBJECT_NAME in
            (    select index_name
                from user_indexes
                where table_name = 'ZZTAB'))
     order by 2;
     OBJ#       OBJECT_NAME                   PROPERTY
     ---------- --------------------------------------
     151268     UNIQ_ID02                     4097
     151258     ZZTAB_PK_ID_NO_CONS              1
     151257     ZZTAB_UNIQ_MAIL                  0


============================================================================================
Les ordres SQL pour identifier les index créés ou non par des contraintes
============================================================================================
Voici l'ordre pour avoir les index de ma table dépendant d'une contrainte d'intégrité PRIMARY KEY ou UNIQUE.
     SQL> SELECT I.obj#, O.object_name, I.property
     FROM sys.ind$ I, dba_objects O
     WHERE I.obj#=O.object_id and I.obj# in
    (select object_id
     from user_objects
     where OBJECT_NAME in
            (select index_name
             from user_indexes
             where table_name = 'ZZTAB'))
     and property = 4097
     order by 2;
     OBJ#       OBJECT_NAME        PROPERTY
     ---------- ----------------------------
     151268     UNIQ_ID02          4097
 
Voici l'ordre pour avoir les index de ma table ne dépendant pas d'une contrainte d'intégrité PRIMARY KEY ou UNIQUE.
     SQL> SELECT I.obj#, O.object_name, I.property
     FROM sys.ind$ I, dba_objects O
     WHERE I.obj#=O.object_id and I.obj# in
         (select object_id
          from user_objects
          where OBJECT_NAME in
            (select index_name
             from user_indexes
             where table_name = 'ZZTAB'))
     and property <> 4097
     order by 2;

     OBJ#       OBJECT_NAME                 PROPERTY
     ---------- -----------------------------------------
     151258     ZZTAB_PK_ID_NO_CONS         1
     151257     ZZTAB_UNIQ_MAIL             0


Posté par David DBA à 12:14 - - Permalien [#]
Tags : ,