Avatar of gs79
gs79
 asked on

to the get counts of the table faster

I am using a dynamic sql to get the counts of all the tables by passing the table name as follows:

 v_str := 'SELECT COUNT(*) FROM ' || p_table;

Since some of the tables are huge, and contains millions of rows, getting the counts of some of the tables is taking longer and hence the over process is taking a lot of time..

Is there a way to get the count of the tables faster.In other words .is there a way to speed up the below query by using hints or any other means


select count(*) from TABLE_ABC

I know we can get the row count roughly by querying the ALL_TABLES dictrionary table but these numbers will not be accurate as we analyze the tables once a week..

Thanks
Oracle Database

Avatar of undefined
Last Comment
gs79

8/22/2022 - Mon
SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER CERTIFIED SOLUTION
ajexpert

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sanjeev Labh

I think the query has to do a full table scan anyhow to get the full count. Parallelism might help but sometimes would not see any potential difference. One variation to the above query would be using simply count(1) instead of count(*) this will reduce the number of bytes processed. You can try both parallelism and count(1) and check for yourself which suits more for your case.

select count(1) from abc;
ajexpert

I don't agree that count(1) will improve performance.

Oracle has grown past 9i versions and Oracle Optimizer don't see any difference between count(*) and count(1)

Other experts can comment
PortletPaul

I agree, count(1) and count(*) should be the same. The * is a constant, so is 1.

I'm also not sure parallelism will help here, as code is being generated, what fields(s) to include in the hint? It might be worth trying.

Will these count operations be frequent or is this something ad-hoc/irregular?

[edit for spelling]
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Naveen Kumar

i do not know if this works our for you but there was a requirement in OLTP system where all the records counts in all the related tables has to be checked before the jobs/batches/schedules are triggered, so there was a small table setup which had 3 columns with tablename/count/date and a daily job/procedure runs to update record count/date into this table. But again if you need the exact count until the time when you execute your query/program, then there is no other way out i believe apart from waiting for your query to finish in getting the exact counts of all required tables.

If you are ok with overnight count, then the above idea may help you.

Thanks,
gs79

ASKER
Thank you for all the comments. I used parallel degree (32) and it helped a great deal. The query which took around 6 hrs ran in 20 min. I may probably get the counts stored in the data dictionary(we analyze everyday) as it may not be feasible to get the counts everyday ..

this is dw systems

Thanks
PortletPaul

>>we analyze the tables once a week..
>>You could try to align your data gathering shortly after you analyze
...
>> we analyze everyday

interesting result here, thanks for letting us know - I would certainly make use of the analyse results if you can.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gs79

ASKER
@portletpaul

I am sorry I should have given some points to you. Your comments deserve points..I will bring it to the moderator's attention to open so that I can split. thanks for reminding..