Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: truncate table vs. truncate table partition
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
>
>--
>http://www.freelists.org/webpage/oracle-l
>
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 28 2006 - 10:16:56 CDT
![]() |
![]() |