Truncate table - are the stats gone?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Truncate table - are the stats gone?

  1. #1
    Join Date
    Sep 2002
    Posts
    11
    Dudes:

    When you truncate a table are the performance tuning stats removed from the data dictionary?

    My guess is not - they would remain and be inaccurate until analyze was run again?

    Thanks - rev

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Why don't you try it for yourself, it is trivial to test it. Just truncate the table and verify what happens with NUM_ROWS (or any other statistics related columns) in USER_TABLES for that table.

    You'll find out that statistics remain unchanged.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    What jmodic said... you only learn by trying this yourself and the test is TRIVIAL.


    SQL> create table test(a number);

    Table created.

    SQL> insert into test values(1);

    1 row created.

    SQL> analyze table test compute statistics;

    Table analyzed.

    SQL> select * from user_tables where table_name = 'TEST';

    TABLE_NAME TABLESPACE_NAME
    ------------------------------ ------------------------------
    CLUSTER_NAME IOT_NAME PCT_FREE
    ------------------------------ ------------------------------ ----------
    PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
    ---------- ---------- ---------- -------------- ----------- -----------
    MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS
    ----------- ------------ ---------- --------------- --- - ---------- ----------
    EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS
    ------------ ---------- ---------- ----------- -------------------------
    NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL
    ------------------- ---------- ---------- ----- -------- ----------- ---------
    PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE DURATION SKIP_COR MON
    --- ------------ - - --- ------- -------- --- --- --------------- -------- ---
    CLUSTER_OWNER DEPENDEN
    ------------------------------ --------
    TEST USERS
    10
    1 255 1048576 1
    2147483645 YES N 1 61
    3 16265 0 6 0
    0 1 1 N ENABLED 1 27-SEP-02
    NO N N NO DEFAULT DISABLED NO NO DISABLED NO
    DISABLED


    SQL> truncate table test;

    Table truncated.

    SQL> select * from user_tables where table_name = 'TEST';

    TABLE_NAME TABLESPACE_NAME
    ------------------------------ ------------------------------
    CLUSTER_NAME IOT_NAME PCT_FREE
    ------------------------------ ------------------------------ ----------
    PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
    ---------- ---------- ---------- -------------- ----------- -----------
    MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOG B NUM_ROWS BLOCKS
    ----------- ------------ ---------- --------------- --- - ---------- ----------
    EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS
    ------------ ---------- ---------- ----------- -------------------------
    NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL
    ------------------- ---------- ---------- ----- -------- ----------- ---------
    PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE GLO USE DURATION SKIP_COR MON
    --- ------------ - - --- ------- -------- --- --- --------------- -------- ---
    CLUSTER_OWNER DEPENDEN
    ------------------------------ --------
    TEST USERS
    10
    1 255 1048576 1
    2147483645 YES N 1 61
    3 16265 0 6 0
    0 1 1 N ENABLED 1 27-SEP-02
    NO N N NO DEFAULT DISABLED NO NO DISABLED NO
    DISABLED


    And there you go...
    OCP 8i, 9i DBA
    Brisbane Australia

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