DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: delete column stats only

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    delete column stats only

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

  2. #2
    Join Date
    Jan 2001
    Posts
    3,134
    Why yes there is...

    TELL THE DAMN DEVELOPERS TO WRITE THERE CODE CORRECTLY!!

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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);
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    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 !

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width