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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 2 Oct 2020 16:15:56 -0400
Message-ID: <02a401d698f8$d657adb0$83070910$_at_rsiz.com>

And, is the table partitioned, if so, how?

Is the table “frozen” during the migration? By “frozen” I mean, are insert, update, and delete suspended for the table from the point you start the migration until the migration is complete, and you are attempting to verify the correctness of the count? In other words, what is the purpose of your count?

mwf

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ahmed.fikri_at_t-online.de Sent: Friday, October 02, 2020 2:37 PM
To: ramukam1983_at_gmail.com; list, oracle Subject: AW: Fastest way to count exact number of rows in a very large table

Hi Ashoke,

could you send the execute plan of the query too? I think there is no general approach for that, it depends on several factors: whether the table has indexes (normal/bitmap) and in case the table has indexes the size of the table compared to the existing index...... But generally parallel processing should help.

Best regards

Ahmed

-----Original-Nachricht-----

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

Datum: 2020-10-02T19:45:19+0200

Von: "Ashoke Mandal" <ramukam1983_at_gmail.com>

An: "ORACLE-L" <oracle-l_at_freelists.org>

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(*)