RE: truncate table vs. truncate table partition

From: Mercadante, Thomas F \(LABOR\) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Thu, 28 Sep 2006 11:09:36 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FF681687@EXCNYSM0A1AJ.nysemail.nyenet>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joe Smith Sent: Thursday, September 28, 2006 11:05 AM To: oracle-l_at_freelists.org
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=enus &source=hmtagline

--

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

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 28 2006 - 10:09:36 CDT