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
Joe,
Try:
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.
Tom
--------------------------------------------------------
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
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
