Sybase IQ, procédures stockées de contrôle de structure (index, cardinalités...). sp_dba_helpcolumn

Introduction

Cette note présente une procédure stockée sp_dba_helpcolumn permettant d'auditer rapidement la nature du contenu d'une colonne. Elle intègre également une fonction de génération de commandes pour corriger des structures fragmentées et/ou de créer des index LF en cas de besoin.

La procédure sp_dba_helpcolumn est directement inspirée de la procédure système sp_iqcardinality_analysis , procédure qui retourne des informations et conseils relatifs à la cardinalité ou au typage d'une colonne. L'idée ici n'a pas été de la refaire mais de la compléter quelque peu en y ajoutant des informations comme le typage, l'indication d'unicité ou la possibilité de travailler sur un jeu restreint d'enregistrements.

Rappels

sp_dba_helpcolumn remet en forme des commandes existantes, les voici récapitulées. Dans les exemples ci-dessous, le schéma utilisé est IDB_ISLF et la table EXCHANGE_RATES.

sp_iqrowdensity

sp_iqrowdensity mesure la fragmentation des index FP. La densité est le ratio entre le nombre de pages théorique et le nombre de pages utilisées. Une densité de 1 est optimale.

La fragmentation survient lors de suppression de données. Des pages sont allouées puis partiellement vidées par une instruction DELETE.

execute sp_iqrowdensity 'table IDB_ISLF.EXCHANGE_RATES';
Tablename               Column Name     IndexType      Density
---------------------------------------------------------------
IDB_ISLF.EXCHANGE_RATES CURRENCY_ID     One Byte FP    1.0
IDB_ISLF.EXCHANGE_RATES FX_RATE         Three Byte FP  1.0
IDB_ISLF.EXCHANGE_RATES FX_UPDATE_DATE  Two Byte FP    1.0
IDB_ISLF.EXCHANGE_RATES CREATED_BY      One Byte FP    1.0
IDB_ISLF.EXCHANGE_RATES CREATED_ON      Two Byte FP    1.0
IDB_ISLF.EXCHANGE_RATES UPDATED_BY      One Byte FP    1.0
IDB_ISLF.EXCHANGE_RATES UPDATED_ON      Two Byte FP    1.0
IDB_ISLF.EXCHANGE_RATES TIMESTAMP       Flat style FP  1.0
IDB_ISLF.EXCHANGE_RATES SOURCE_ID       One Byte FP    1.0
IDB_ISLF.EXCHANGE_RATES FX_RATE_DATE    Two Byte FP    1.0

sp_iqcolumn

sp_iqcolumn retourne la nature de chaque colonne d'une table, son typage, sa cardinalité (nombre de valeurs distinctes) et sa localisation.

execute sp_iqcolumn 'EXCHANGE_RATES', 'IDB_ISLF';
table_name     owner    column_name    domain_name width sca nul def cardinality est_cardinality loc   ...
----------------------------------------------------------------------------------------------------------
EXCHANGE_RATES IDB_ISLF CURRENCY_ID    char           3    0 N   -             0             255  Main ...
EXCHANGE_RATES IDB_ISLF FX_RATE_DATE   timestamp      8    0 N   -             0             255  Main ...
EXCHANGE_RATES IDB_ISLF SOURCE_ID      varchar       12    0 N   -             0             255  Main ...
EXCHANGE_RATES IDB_ISLF FX_RATE        double         8    0 Y   -             0             255  Main ...
EXCHANGE_RATES IDB_ISLF FX_UPDATE_DATE timestamp      8    0 Y   -             0             255  Main ...
EXCHANGE_RATESI DB_ISLF CREATED_BY     varchar       20    0 N   -             0             255  Main ...
EXCHANGE_RATES IDB_ISLF CREATED_ON     timestamp      8    0 N   -             0             255  Main ...
EXCHANGE_RATES IDB_ISLF UPDATED_BY     varchar       20    0 Y   -             0             255  Main ...
EXCHANGE_RATES IDB_ISLF UPDATED_ON     timestamp      8    0 Y   -             0             255  Main ...
EXCHANGE_RATES IDB_ISLF TIMESTAMP      varbinary      8    0 Y   -             0             255  Main ...

sp_iqindex_alt (sp_iqindex)

sp_iqindex_alt indique pour chaque colonne d'une table le nom de son index FP et son unicité déclarée. sp_iqindex ajoute le nom et l'id du dbspace de stockage par rapport à sp_iqindex_alt.

execute sp_iqindex_alt 'EXCHANGE_RATES', null, 'IDB_ISLF'
table_name     owner    column_name    typ index_name           unique_index remarks
------------------------------------------------------------------------------------
EXCHANGE_RATES IDB_ISLF CURRENCY_ID    FP  ASIQ_IDX_T935_C1_FP  N
EXCHANGE_RATES IDB_ISLF FX_RATE_DATE   FP  ASIQ_IDX_T935_C2_FP  N
EXCHANGE_RATES IDB_ISLF SOURCE_ID      FP  ASIQ_IDX_T935_C3_FP  N
EXCHANGE_RATES IDB_ISLF FX_RATE        FP  ASIQ_IDX_T935_C4_FP  N
EXCHANGE_RATES IDB_ISLF FX_UPDATE_DATE FP  ASIQ_IDX_T935_C5_FP  N
EXCHANGE_RATES IDB_ISLF CREATED_BY     FP  ASIQ_IDX_T935_C6_FP  N
EXCHANGE_RATES IDB_ISLF CREATED_ON     FP  ASIQ_IDX_T935_C7_FP  N
EXCHANGE_RATES IDB_ISLF UPDATED_BY     FP  ASIQ_IDX_T935_C8_FP  N
EXCHANGE_RATES IDB_ISLF UPDATED_ON     FP  ASIQ_IDX_T935_C9_FP  N
EXCHANGE_RATES IDB_ISLF TIMESTAMP      FP  ASIQ_IDX_T935_C10_FP N

sp_iqindexinfo

sp_iqindexinfo indique pour chaque index d'une table sa localisation, sa taille et son poids dans le dbspace .

execute sp_iqindexinfo 'table IDB_ISLF.EXCHANGE_RATES'
Object                                       DbspaceName ObjSize DBSpPct
------------------------------------------------------------------------
IDB_ISLF.EXCHANGE_RATES                      IQ_MAIN        208K       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C10_FP IQ_MAIN       7.49M       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C1_FP  IQ_MAIN        680K       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C2_FP  IQ_MAIN        2.5M       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C3_FP  IQ_MAIN        680K       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C4_FP  IQ_MAIN        7.5M       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C5_FP  IQ_MAIN        1.6M       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C6_FP  IQ_MAIN        784K       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C7_FP  IQ_MAIN       2.82M       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C8_FP  IQ_MAIN          1M       1
IDB_ISLF.EXCHANGE_RATES.ASIQ_IDX_T935_C9_FP  IQ_MAIN       1.97M       1

sp_iqcardinality_analysis

sp_iqcardinality_analysis génère des instructions pour optimiser le stockage ou l'interrogation des données d'une table.

Les bonnes pratiques sont :

  • La cardinalité (Valeurs distinctes) ne correpond pas au stockage optimal : si < 255 alors 'One Byte FP', si < 65536 alors 'Two Byte FP' ...
  • Absence d'index LF sur une colonne dont la cardinalité est très faible ( < 255 ).
  • Absence d'index HG sur une colonne dont la cardinalité est moyenne ( > 1500 ).
  • Absence d'index DT ou DTM sur des colonnes de type date ou datetime.

Ces instructions ne sont que des conseils, pas forcément à appliquer à la lettre. La prise en compte de la nature de la table et de son activité reste essentielle.

execute sp_iqcardinality_analysis 'EXCHANGE_RATES', 'IDB_ISLF', 'script'
Index Recommendation

--Column EXCHANGE_RATES.CURRENCY_ID has no LF index and cardinality is less than 1500. 
--LF index can be created using CREATE INDEX statement: 
CREATE LF INDEX ASIQ_T935_10_LF ON IDB_ISLF.EXCHANGE_RATES (CURRENCY_ID)

--Column EXCHANGE_RATES.FX_RATE_DATE has no HG index and cardinality is greater than or equal to 1500. 
--HG index can be created using CREATE INDEX statement: 
CREATE HG INDEX ASIQ_T935_10_HG ON IDB_ISLF.EXCHANGE_RATES (FX_RATE_DATE)

--Column EXCHANGE_RATES.FX_RATE_DATE is of data type DATETIME or TIMESTAMP. 
--DTTM index can be created using CREATE INDEX statement: 
CREATE DTTM INDEX ASIQ_T935_10_DTTM ON IDB_ISLF.EXCHANGE_RATES (FX_RATE_DATE)

--Column EXCHANGE_RATES.TIMESTAMP has no Three Byte FP index and cardinality is between 65536 and 16777216. 
--Consider converting Flat FP to Three Byte FP. 
--Call the stored procedure: 
sp_iqrebuildindex IDB_ISLF.EXCHANGE_RATES,column TIMESTAMP 16777215

--Column EXCHANGE_RATES.TIMESTAMP has no HG index and cardinality is greater than or equal to 1500. 
--HG index can be created using CREATE INDEX statement: 
CREATE HG INDEX ASIQ_T935_10_HG ON IDB_ISLF.EXCHANGE_RATES (TIMESTAMP)

 ...

sp_dba_helpcolumn

sp_dba_helpcolumn interprète et regroupe une partie des informations retournées par les procédures systèmes précédentes.

Elle intègre en outre - en option - une méthode pour réaliser le calcul de la cardinalité sur un sous ensemble de la table dans le but d'obtenir une réponse plus rapide sur des objets très volumineux. Dans ce cadre, une table est matérialisée physiquement empêchant l'exécution simultanée de cette procédure (la génération d'une table temporaire via une exécution dynamique a une portée limitée à cette exécution) .

Elle exploite une fonction très pratique de l'interpréteur IQ qui est la possibilité d'intégrer et d'exploiter le résultat d'une procédure stockée au sein d'une requête SQL SELECT (select ... from <procedure>).

select column_name 
from   sp_iqcolumn('EXCHANGE_RATES', 'IDB_ISLF') 
where  domain_name='timestamp';
column_name    
---------------
FX_RATE_DATE   
FX_UPDATE_DATE 
CREATED_ON     
UPDATED_ON 

sp_dba_helpcolumn

create procedure "sa"."sp_dba_helpcolumn" 
/*
# ############################################################################
# @(#) Proc    : sp_dba_helpcolumn
# @(#) Usage   : sp_dba_helpcolumn TABLE_OWNER, TABLE_NAME [, ROWS ] [, OUTPUTTYPE ]
# @(#)           TABLE_OWNER : ...
# @(#)           TABLE_NAME  : ...
# @(#)           ROWS        : Paramètre utilisé pour calculer les valeurs distinctes 
# @(#)                         0 : full scan de la table, sinon sur les n premières lignes
# @(#)                             définies par [ROWS].
# @(#)           OUTPUTTYPE  : 1 : stats+conseils, 2 : stats uniquement ,
# @(#)                         3 : conseils seulement, 4 : script de conseil seulement
# @(#) Objet   : Récupère divers informations sur les colonnes
# @(#)          (type de données, valeurs distinctes, type d'index, densité, 
# @(#)          conseil de reconstruction des indexes FP, de créations d'index LF...)
# @(#)           mix de commandes variées en une seule opération
# @(#)           - exec sp_iqcardinality_analysis TABLE_NAME, TABLE_OWNER, 'script'
# @(#)           - exec sp_iqcolumn TABLE_OWNER, TABLE_OWNER
# @(#)           - exec sp_iqindex_alt TABLE_OWNER, null, TABLE_OWNER
# @(#)           - exec sp_iqrowdensity 'table TABLE_OWNER.TABLE_NAME'
# @(#)           - exec sp_iqindexinfo 'table TABLE_OWNER.TABLE_NAME'
# @(#) Exemples: sp_dba_helpcolumn 'DAS', 'POSITIONS', 1000000
# @(#) Auteur  : FA
# @(#) Cree le : 20110329
# @(#) Version  : IQ 15.2 (devrait fonctionner sur les versions 15.0 et 15.1) 
# ############################################################################
# Mises a jour
# ----------------------------------------------------------------------------
# ############################################################################
*/

Voici la sortie.

execute sp_dba_helpcolumn 'IDB_ISLF', 'EXCHANGE_RATES'
owner     table_name    column_name    column_type  nul UnqIdx IsUnq DistRowCnt ... 
-------------------------------------------------------------------------------- ... 
IDB_ISLF EXCHANGE_RATES CURRENCY_ID    char          N   N      N            209 ... 
IDB_ISLF EXCHANGE_RATES FX_RATE_DATE   timestamp     N   N      N           3131 ... 
IDB_ISLF EXCHANGE_RATES SOURCE_ID      varchar       N   N      N             14 ... 
IDB_ISLF EXCHANGE_RATES FX_RATE        double        Y   N      N         484106 ... 
IDB_ISLF EXCHANGE_RATES FX_UPDATE_DATE timestamp     Y   N      N           2447 ... 
IDB_ISLF EXCHANGE_RATES CREATED_BY     varchar       N   N      N             38 ... 
IDB_ISLF EXCHANGE_RATES CREATED_ON     timestamp     N   N      N          26463 ... 
IDB_ISLF EXCHANGE_RATES UPDATED_BY     varchar       Y   N      N              4 ... 
IDB_ISLF EXCHANGE_RATES UPDATED_ON     timestamp     Y   N      N           1438 ... 
IDB_ISLF EXCHANGE_RATES TIMESTAMP      varbinary     Y   N      N        1334205 ... 


 ... index_type    index_Mo density cardinality est_cardinality 
 ... -----------------------------------------------------------
 ... One Byte FP          0    1.00           0             255 
 ... Two Byte FP          2    1.00           0             255 
 ... One Byte FP          0    1.00           0             255 
 ... One Byte FP          0    1.00           0             255 
 ... Two Byte FP          1    1.00           0             255 
 ... One Byte FP          0    1.00           0             255 
 ... Two Byte FP          2    1.00           0             255 
 ... One Byte FP          1    1.00           0             255 
 ... Two Byte FP          1    1.00           0             255 
 ... Flat style FP        7    1.00           0             255 


advice
create LF index IDB_ISLF_EXCHANGE_RATES_LF_CREATED_BY on IDB_ISLF.EXCHANGE_RATES( CREATED_BY );
create LF index IDB_ISLF_EXCHANGE_RATES_LF_CURRENCY_ID on IDB_ISLF.EXCHANGE_RATES( CURRENCY_ID );
create LF index IDB_ISLF_EXCHANGE_RATES_LF_SOURCE_ID on IDB_ISLF.EXCHANGE_RATES( SOURCE_ID );
create LF index IDB_ISLF_EXCHANGE_RATES_LF_UPDATED_BY on IDB_ISLF.EXCHANGE_RATES( UPDATED_BY );
exec sp_iqrebuildindex 'IDB_ISLF.EXCHANGE_RATES','column CREATED_ON 65536';
exec sp_iqrebuildindex 'IDB_ISLF.EXCHANGE_RATES','column FX_RATE 16777216';
exec sp_iqrebuildindex 'IDB_ISLF.EXCHANGE_RATES','column FX_RATE_DATE 65536';
exec sp_iqrebuildindex 'IDB_ISLF.EXCHANGE_RATES','column FX_UPDATE_DATE 65536';
exec sp_iqrebuildindex 'IDB_ISLF.EXCHANGE_RATES','column TIMESTAMP 16777216';
exec sp_iqrebuildindex 'IDB_ISLF.EXCHANGE_RATES','column UPDATED_ON 65536';

Les informations retournées sont :

  • Table_owner : propriétaire de la table.
  • table_name : nom de la table.
  • column_name : nom de la colonne.
  • column_type : type de la colonne (domain).
  • nul : valeurs null acceptées ? (Y/N).
  • UnqIdx : contrainte d'unicité posée ? (Y/N).
  • IsUnq : Unicité détectée ? (Y/N). Valeurs distinctes = nombre de lignes.
  • DistRowCnt : nombre de valeurs différentes detectées.
  • index_type : type de stockage de l'index.
  • Index_Mo : taille (en Mo) de l'index FP.
  • Density : densité. Ratio entre le nombre de pages nécessaires et celui utilisé.
  • Cardinality : cardinalité connue, obtenue par la présence de contraintes et/ou d'index sur la colonne.
  • est_cardinality : cardinalité estimée (cardinalité mise en place lors de la commande create table IQ UNIQUE ou de l'exécution de la procédure système sp_iqrebuildindex).

Conseils

Les conseils présentés ici sont liés à la structure et la cardinalité des colonnes. L'application de ces instructions dépend du contexte d'usage avant tout.

  • sp_iqrebuildindex si la densité est < 0.8 ou si le stockage n'est pas optimal au regard de la cardinalité.
  • sp_iqrebuildindex si le type d'index (index_type) n'est pas cohérent avec la cardinalité.
  • alter table add unique si la contrainte d'unicité est manquante.
  • create LF index si la cardinalité est < 1000.
  • create DATE index pour les colonnes de type DATE.
  • create DTTM index pour les colonnes de type datetime ou timestamp.