truncate table vs. truncate table partition
- From: "Joe Smith" <joe_dba@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 28 Sep 2006 10:04:35 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
