delete column stats only

[khussain]

Is there a way to delete column statistics only...and keep the table/index stats ?

[Mr.Hanky]

Why yes there is...

TELL THE DAMN DEVELOPERS TO WRITE THERE CODE CORRECTLY!!

MH

[slimdave]

I would guess that you would invoke ....

DBMS_STATS.DELETE_COLUMN_STATS (
   ownname       VARCHAR2, 
   tabname       VARCHAR2, 
   colname       VARCHAR2, 
   partname      VARCHAR2 DEFAULT NULL,
   stattab       VARCHAR2 DEFAULT NULL, 
   statid        VARCHAR2 DEFAULT NULL,
   cascade_parts BOOLEAN  DEFAULT TRUE,
   statown       VARCHAR2 DEFAULT NULL,
   no_invalidate BOOLEAN DEFAULT FALSE);

[khussain]

is it possible to specify more than one column at a time ?
if yes, please give me an example.

i've tried different combinations, can't get it to work !

[abhaysk]

Originally posted by khussain
is it possible to specify more than one column at a time ?
if yes, please give me an example.

i've tried different combinations, can't get it to work !

Please dont try this

delete from sys.hist_head$ h 
where 
(h.OBJ#,h.COL#) in 
( 
select o.obj#,c.COL# from sys.col$ c, sys.obj$ o, sys.hist_head$ h 
where 
o.OBJ#=c.obj# and 
c.obj#=h.obj# and 
c.col#=h.col# and
o.name='your_table_name' and     -- Table in which you want to delete COL stats
c.name in ('COL1', 'COL2', ... ) -- Columns you want to delete the stats
);

Jurij, Pando yell at me if any thing else is done to delete the statistics for a COLUMN.

Abhay.

[slimdave]

begin
for x in (select column_name from user_tab_columns where table_name = 'MY_TABLE')
loop
DBMS_STATS.DELETE_COLUMN_STATS (
   ...
   colname => x.column_name,
   ...
   );
End Loop;
End;
/

It ain't follproof mind -- there is an implicit commit in the DBMS_STATS call, I expect, which could cause problems. Really you should load up the column names into a collection and loop through it.