We help IT Professionals succeed at work.

truncate table vs truncate partition table

sunilbains
sunilbains asked
on
773 Views
Last Modified: 2012-05-08
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.
Comment
Watch Question

johnsoneSenior Oracle DBA
CERTIFIED EXPERT

Commented:
NUM_ROWS is not more accurate than count.

I wouldn't use analyze, use DBMS_STATS to gather the statistics.  What you are seeing is more than likely a bug with analyze.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Num_rows is from the last time statistics were collected on the table.

If you really have to see ZERO num_rows, re-analyze the partition.  
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
sorry. missed the analyze step in the original post.

try removing ' for all indexes' and just go after the table itself.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
simple test

truncate table  joe.catalog;
select * from joe.catalog;

you'll see joe.catalog is in fact empty.  and your tab partitions aren't correct.

as jonsone pointed out.  use dbms_stats in all databases 8i and above instead of analyze.
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks ALL!
Top Expert 2009

Commented:
Hi,

If you are going to post followups and then delete the thread before I submit (wasting time), please close this question first, or continue to post in this thread, not new threads.

The answer to the question you just posted is here in my response to this one (http:#25777971), so it tells me that possibly you did not read all the responses here before moving forward.

Thanks.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.