Parmi les nouvelles fonctionnalités d’Oracle 11g, il est désormais possible de supprimer un histogramme sur une colonne grâce à l’attribut COL_STAT_TYPE de la procédure DBMS_STATS.DELETE_COLUMN_STATS. Vous pouvez vous reporter à la note 5579764.8 pour plus d’informations. En substance, supposons que vous vouliez supprimer un histogramme sur la colonne COL1 de la table TAB1 de l’utilisateur courant, il vous suffit de lancer la commande ci-dessous :
exec dbms_stats.delete_column_stats(ownname=>user, -
tabname => 'TAB1', -
colname=>'COL1',-
col_stat_type=>'HISTOGRAM')
Jusqu’en 10g, la méthode supportée pour « arriver » au même résultat est de recalculer les statistiques en changeant l’attribut method_opt. En plus d’être peut-être un calcul coûteux pour votre base de données, cette méthode changera très probablement, non seulement les statistiques pour la colonne incriminée mais, si les données ont changé depuis votre dernière collection, changera également d’autres de vos statistiques.
Il existe donc une alternative, quoique la méthode soit non supportée ! Exportez les statistiques, bidouillez-les et réimportez-les. Voici un exemple d’une telle manipulation… Biensur, vous ne pourrez ne vous en prendre qu’à vous seul si c’est une très mauvaise pratique !
Etape 1 : table d’exemple
Vous construirez sans doute une méthode infaillible. Pour ce qui suit, nous nous contenterons d’illustrer la technique avec un exemple simple : 1 table avec 1 colonne et 1 histogramme ; Voici le script pour construire un tel exemple :
create table tab1(col1 number)
tablespace users;
begin
for i in 1..10000 loop
insert into tab1(col1) values (i);
end loop
commit;
end;
/
exec dbms_stats.gather_table_stats(ownname=>user, -
tabname => 'TAB1', -
method_opt =>'FOR COLUMNS COL1 SIZE 254')
select count(*)
from user_histograms
where table_name='TAB1'
and COLUMN_NAME='COL1';
COUNT(*)
----------
255
Etape 2 : Exportez les statistiques de la colonne
Pour ce faire, il suffit de créer une table qui contiendra les statistiques.
exec dbms_stats.create_stat_table(ownname=>user, -
stattab=>'EXPORT_STAT1', -
tblspace=>'USERS');
Puis d’exporter les statistiques de la colonne qui vous intéresse dans la table créée précédemment :
exec dbms_stats.export_column_stats(ownname=>user, -
tabname => 'TAB1', -
colname => 'COL1', -
stattab => 'EXPORT_STAT1',-
statid => 'EXPORT#1');
Vous pouvez vérifier que l’export des statistiques contient bien l’histogramme sur les colonnes comme ci-dessous :
select count(*)
from EXPORT_STAT1
where statid='EXPORT#1'
and C1='TAB1'
and C4='COL1'
and C5=user;
COUNT(*)
--------
255
Etape 3 : Bidouillez les statistiques
Evidemment, ceci est l’étape non supportée et c’est assez délicat de savoir ce qu’il y a exactement dans EXPORT_STAT1, quoique ici on est assez chanceux puisque les statistiques sur la colonne sont répétées pour chacune des valeurs de l’histogramme et seul N9, N10 et N11 changent. Je vous laisse valider que, dans cet exemple, les ordres suivants bidouillent « correctement » nos statistiques :
delete from EXPORT_STAT1
where statid='EXPORT#1'
and C1='TAB1'
and C4='COL1'
and C5=user
and N10>0;
update EXPORT_STAT1
set N9=null, N10=null, N11=null
where N10=0;
commit;
Vous pouvez toujours comparer le résultat obtenu avec des statistiques prises sans histogramme ou pourquoi pas, importer les statistiques en 11g, supprimer les histogrammes et les réexporter en 10g. Enfin, supposons que vous êtes satisfait de vos statistiques…
Etape 4 : ré-importez les statistiques
Le reste n’est donc plus qu’une formalité ; Il faut réimporter les statistiques de la colonne :
exec dbms_stats.import_column_stats(ownname=>user, -
tabname => 'TAB1', -
colname => 'COL1', -
stattab => 'EXPORT_STAT1',-
statid => 'EXPORT#1');
Et vous pouvez vérifier que votre histogramme a disparu
select endpoint_number, endpoint_value
from user_histograms
where table_name='TAB1'
and COLUMN_NAME='COL1'
order by endpoint_number;
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 1
1 10000
Etape supplémentaire : Avant d’en finir avec cet exemple
Une autre fonctionnalité intéressante d’Oracle 10g est la possibilité de comparer des statistiques avec la fonction de table diff_table_stats_in_stattab. Voici donc un exemple simple d’utilisation. Commençons par changer les données dans la table et les réexporter dans la même table mais avec un STATID différent (ici ‘EXPORT#2’) :
begin
for i in 10000..50000 loop
insert into tab1(col1) values (i);
end loop
commit;
end;
/
exec dbms_stats.gather_table_stats(ownname=>user, -
tabname => 'TAB1', -
method_opt =>'FOR COLUMNS COL1 SIZE 254')
exec dbms_stats.export_column_stats(ownname=>user, -
tabname => 'TAB1', -
colname => 'COL1', -
stattab => 'EXPORT_STAT1',-
statid => 'EXPORT#2')
Une fois les exportées, vous pouvez afficher un rapport des différences entre les statistiques grâce à la requête ci-dessous :
set long 10000
set longchunksize 10000
set lines 100
set pages 1000
select report from table(
dbms_stats.diff_table_stats_in_stattab(
user,
'TAB1',
'EXPORT_STAT1',
'EXPORT_STAT1',
10,
'EXPORT#1',
'EXPORT#2'));
Le rapport ressemble à ceci :
REPORT
-------------------------------------------------------------------------------###############################################################################
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE : TAB1
OWNER : SCOTT
SOURCE A : User statistics table EXPORT_STAT1
: Statid : EXPORT#1
: Owner : SCOTT
SOURCE B : User statistics table EXPORT_STAT1
: Statid : EXPORT#2
: Owner : SCOTT
PCTTHRESHOLD : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ
...............................................................................
COL1 A 10000 .0001 NO 0 4 C102 C302 10000
B 50000 .000020000 YES 0 5 C102 C306 50001
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################
Etape supplémentaire : En finir avec cet exemple
Avant de quitter ce post, supprimez les tables et données de cet exemple :
drop table export_stat1 purge;
drop table TAB1 purge;
C’est tout !
