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_RATES IDB_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
LFsur une colonne dont la cardinalité est très faible ( < 255 ). - Absence d’index
HGsur une colonne dont la cardinalité est moyenne ( > 1500 ). - Absence d’index
DTouDTMsur des colonnes de typedateoudatetime.
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’indexFP.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 commandecreate table IQ UNIQUEou de l’exécution de la procédure systèmesp_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_iqrebuildindexsi la densité est < 0.8 ou si le stockage n’est pas optimal au regard de la cardinalité.sp_iqrebuildindexsi le type d’index (index_type) n’est pas cohérent avec la cardinalité.alter table add uniquesi la contrainte d’unicité est manquante.create LF indexsi la cardinalité est < 1000.create DATE indexpour les colonnes de typeDATE.create DTTM indexpour les colonnes de typedatetimeoutimestamp.