Monday, August 4, 2008

Counting rows in Partition with millions of rows

If the no. of rows in a partitioned table is in millions, use the following to minimize the time in returning the no. of rows

SQL> set timing on

SQL> select /*+ full(m) parallel(m,16) */ count(*) from MY_TABLE partition (MY_TABLE_PR108) M;
COUNT(*)---------- 209204953
Elapsed: 00:06:07.02

SQL> alter table MY_TABLE truncate partition MY_TABLE_PR108;
Table truncated.
Elapsed: 00:00:12.20