RE: truncate table vs. truncate table partition

  • From: "Mercadante, Thomas F \(LABOR\)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
  • To: <joe_dba@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 28 Sep 2006 11:09:36 -0400



Exec dbms_stats.gather_table_stats(user,'CATALOG',cascade=>true);

Your "analyze" did not analyze the partitions and reset the values to
zero.  I'm sure there is a way to do that with the analyze command, but
you should learn the dbms_stats command as analyze is going away.

Hope this helped.


This transmission may contain confidential, proprietary, or privileged 
information which is intended solely for use by the individual or entity to 
whom it is addressed.  If you are not the intended recipient, you are hereby 
notified that any disclosure, dissemination, copying or distribution of this 
transmission or its attachments is strictly prohibited.  In addition, 
unauthorized access to this transmission may violate federal or State law, 
including the Electronic Communications Privacy Act of 1985.  If you have 
received this transmission in error, please notify the sender immediately by 
return e-mail and delete the transmission and its attachments.

-----Original Message-----

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Joe Smith
Sent: Thursday, September 28, 2006 11:05 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: truncate table vs. truncate table partition

Explain this to me:

this is a partitioned table with global indexes.

sql>truncate table  joe.catalog;

table truncate

sql>select count(*) from joe.catalog;

0 rows

sql>analyze table joe.catalog compute statistics for all indexes;

sql>select count(*) from joe.catalog;

0 rows



TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
CATALOG                         CATALOG_PART_B                 13
CATALOG                         CATALOG_PART_C                 21
CATALOG                         CATALOG_PART_D                 20
CATALOG                         CATALOG_PART_E                 26
CATALOG                         CATALOG_PART_F                 28
CATALOG                         CATALOG_PART_0                 16
CATALOG                         CATALOG_PART_1                 25
CATALOG                         CATALOG_PART_2                 16
CATALOG                         CATALOG_PART_3                 29
CATALOG                         CATALOG_PART_4                 28
CATALOG                         CATALOG_PART_5                 25

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
CATALOG                         CATALOG_PART_6                 15
CATALOG                         CATALOG_PART_7                 17
CATALOG                         CATALOG_PART_8                 19
CATALOG                         CATALOG_PART_9                 17
CATALOG                         CATALOG_PART_A                 31

It is my believe that num_rows is more accurate than select count(*) ...

So why after I truncate the table it report 0 rows?

But if I select from DBA_TAB_PARTITIONS I still see the rows?

Do I need to truncate each individaul partition to really remove the

Is "select count(*) from joe.catalog"  reporting false information?


Be seen and heard with Windows Live Messenger and Microsoft LifeCams


Other related posts: