Beneficial? View table of recCounts...

[frankk]

Hi Everyone,
I am considering ways to increase the performance of my application, and currently, because I need to record counts of a few tables in the Oracle db, would anyone think that it's beneficial to create a VIEW table that contains (at least) two fields: (tableName, recCount), containing the tableName, and the recCounts of that specified table?

Eg) SELECT COUNT(*) FROM EMP; -> 230921 records

View Table: V_TABLERECS
TABLENAME
-------------
'EMP'

RECCOUNT
--------------
230921

I hope I was clear in my question...

Please advise..

Thanks.

Frank

[jdorlon]

Hi

I don't think a view will help much. It still has to execute the query to get the results.

If the results of count(*) do not have to be 100% accurate 100% of the time, why not analyze your tables (daily, weekly, or whatever interval is needed) and then select the results from ALL_TABLES?

-John

[chrisrlong]

Or, if they *have* to be accurate, then how about an actual table that is updated through triggers on the target tables? In other words, if you are interested in the count of Table1, then create a trigger on Table1 for inserts and deletes that modifies the record in your new TableRecordCount table for Table1 by either incrementing or decrementing it by the proper amount. This will, of course, slow the inserts and updates slightly, but you need to weigh that against the cost of calculating the total every time you need it. Of course, you may want to start by checking the plan of your SELECT COUNT statements currently to verify that they are not doing tablescans. Here is a thread that discusses that:

[url]http://www.dbasupport.com/forums/showthread.php?threadid=6617[/url]

HTH,

- Chris

[rcaballe]

Why don't you investigate in Materialized Views?
It could help.