delete column stats only

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

Why yes there is...

TELL THE DAMN DEVELOPERS TO WRITE THERE CODE CORRECTLY!!

MH

I would guess that you would invoke ....

Code:

---

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);

---

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 !

Quote:

---

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

Code:

---

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.

Code:

---

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.