-
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 remember when this place was cool.
-
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 !
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|