I agree w/Mladen about going to 19c; otherwise, you’ll be facing another
upgrade “soon”.
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On Behalf
Of Mladen Gogala
Sent: Saturday, October 3, 2020 3:42 PM
To: ramukam1983@xxxxxxxxx; ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Fastest way to count exact number of rows in a very large table
CAUTION: This email originated from outside of the organization. Do not follow
guidance, click links, or open attachments unless you recognize the sender and
know the content is safe.
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<https://nam05.safelinks.protection.outlook.com/?url=https%3A%2F%2Fblogs.oracle.com%2Fin-memory%2Fbase-level-198-ru&data=02%7C01%7Cclay.jackson%40quest.com%7C29e22a249c524ae8b59908d867edb390%7C91c369b51c9e439c989c1867ec606603%7C0%7C0%7C637373617900068228&sdata=jEAzLQSsoazhGUb95ujishEyEVdPOD7Z8UeCHIg2gmA%3D&reserved=0>
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
