Re: Fastest way to count exact number of rows in a very large table

One way speeding things up is using parallel query. The other one is to use faster disk. If the table has the primary key, index FFS would probably be much faster than the full table scan. Of course, the instance will have to read significant number of disk blocks in both cases. You should use the fastest storage available. On Linux, you should use SSD devices like Pure or, even better, EMC XtremIO. You can also use an NVME device as flash cache.By the way, migrating to 12c is a bad idea. These days, you should be migrating to 19c. It is the last of the version in the 12c series of products. And yes, that means that Oracle 20c will not be a part of the 12c series of products. Oracle 20c was supposed to be available in June but the new rumor is that it will be available for Christmas, if you were good throughout the year. I know I have been naughty, Santa hasn't brought me anything since I was 10. BTW, as of Oracle 19.8 you can enable in-memory for free, as long as in-memory size is not greater than 16 GB. You can also maintain up to 3 tenant databases for free.
https://blogs.oracle.com/in-memory/base-level-198-ru Oracle 19.9 will be available in around 2 weeks.Regards On Fri, 2020-10-02 at 12:44 -0500, Ashoke Mandal wrote:
> Dear All,I have a table with 108 billion rows and migrating this
> database from Oracle 11g on Solaris to Oracle 12c on Linux.
> After the migration I need to compare the row count of this table in
> both the source DB and the destination DB. It takes almost two hours
> to get the row count from this table.SQL> select to_char(count(*),
> '999,999,999,999') from test_data;
>
> TO_CHAR(COUNT(*)
> ----------------
> 108,424,262,144
> Elapsed: 02:22:46.18
>
> Could you please suggest some tips to get the row count faster so
> that it reduces the cut-over downtime.
> Thanks,Ashoke

-- 
Mladen Gogala

Database Consultant

Tel: (347) 321-1217


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