truncate table vs. truncate table partition

From: Joe Smith <joe_dba_at_hotmail.com>
Date: Thu, 28 Sep 2006 10:04:35 -0500
Message-ID: <BAY121-F135D6B3C76A11DF38A7AC9971B0@phx.gbl>

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

Then,

select TABLE_NAME, PARTITION_NAME, NUM_ROWS from DBA_TAB_PARTITIONS WHERE TABLE_NAME='CATALOG';

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?

thanks.

---

Be seen and heard with Windows Live Messenger and Microsoft LifeCams http://clk.atdmt.com/MSN/go/msnnkwme0020000001msn/direct/01/?href=http://www.microsoft.com/hardware/digitalcommunication/default.mspx?locale=en-us&source=hmtagline

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 28 2006 - 10:04:35 CDT