Re: truncate table vs. truncate table partition

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: joe_dba@xxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Thu, 28 Sep 2006 23:16:56 +0800


SELECT COUNT(*)  FROM <tablename>   shows the __correct__ count as it is 
NOW !

Querying DBA_TABLES/DBA_TAB_PARTITIONS/DBA_INDEXES etc
shows the count of Rows computed or estimated when ANALYZE or DBMS_STATS
was last run against the table/partition/index.
eg when querying NUM_ROWS from DBA_TABLES, also check LAST_ANALYZED
and SAMPLE_SIZE. These would show when ANALYZE/DBMS_STATS was last
run and whether the whole table was read by the Analyze/gather_stats or a sampling
was done. In either case, the value of NUM_ROWS is historical not current.
NUM_ROWS and other statistics are used by the Query Optimizer in determining
an Execution Plan.
Never rely on these as accurate reflections of the current count of records/chained rows/
free blocks etc for the Table and Index.

Hemant
At 11:04 PM Thursday, Joe Smith wrote:

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



Hemant K Chitale
http://web.singnet.com.sg/~hkchital


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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2006