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

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

ajexpert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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]

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.

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..