Re: truncate table vs. truncate table partition

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: joe_dba@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 28 Sep 2006 23:16:56 +0800

SELECT COUNT(*) FROM <tablename> shows the __correct__ count as it is NOW !

shows the count of Rows computed or estimated when ANALYZE or DBMS_STATS
was last run against the table/partition/index.
eg when querying NUM_ROWS from DBA_TABLES, also check LAST_ANALYZED
and SAMPLE_SIZE. These would show when ANALYZE/DBMS_STATS was last
run and whether the whole table was read by the Analyze/gather_stats or a sampling
was done. In either case, the value of NUM_ROWS is historical not current.
NUM_ROWS and other statistics are used by the Query Optimizer in determining
an Execution Plan.
Never rely on these as accurate reflections of the current count of records/chained rows/
free blocks etc for the Table and Index.

At 11:04 PM Thursday, Joe Smith wrote:
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 rows?

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


Be seen and heard with Windows Live Messenger and Microsoft LifeCams


Hemant K Chitale

-- //

Other related posts: