Skip to Main Content
  • Questions

  • Record count on basis of insert/delete triggers

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, shiraz.

Asked: September 26, 2016 - 8:32 am UTC

Last updated: September 27, 2016 - 12:14 am UTC

Version: 11

Viewed 1000+ times

You Asked

Hi,

We have a pagination requirement across the UI foothold where we need to show the total# of records for significant db facts off the tables in view of numbers. And the query are not utilizing query filter criteria as triggerd by a menu option landing page showing all the records paginated. The volume of data is close to higher double digit millions. We are obviously perceiving quite a bit of latency/performance turnaround on the page load. Wondering if a count(*) off the tables involving a full table scan and due performance degradation is avoidable if we maintain total # of records based off trigger firing on insert/delete incremementing a db counter. The landing page fetch of total count of records is mandated and a needed usecase. Let me know whats your expert opinion on how to handle the situation and optimize duely. Thanks
__Shiraz


and Chris said...

My opinion?

Don't show the total number of rows!

There's really no need to. Telling the users that there are 10,547,321 results is meaningless. All they need are the data for the current page and that there's more to see.

If for some reason you insist on showing the total number of rows, you could try creating a materialized view:

create materialized view mv 
refresh fast on commit
as 
  select count(*) from tab;


Provided it's refresh fast on commit, Oracle will keep it in sync for you. Finding the count is then a single row lookup which is nice and fast.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

shiraz zaidi, September 26, 2016 - 11:35 am UTC

Hi Chris,

Thanks for quick response on this. I totally agree with your recommendation of not retreving the count and have pagination aware of which page of total pages. But the customer needs this feature as its out of the product and they dont want to change it. On the advice of using a materialized view it seems that might be something we can use to achieve performance improvement, but i have some concerns regarding this. It seems that count(*) would be fired on every insert/delete/select commits. I am wondering if these atomic operations would be impacted with the count(*) query behind the scene if done synchronously with the db commit thread. If yes then i think we are moving the problem from one place to other and impacting OTP transactions. Can you please clarify on this? Thanks. And again appreciate your quick response on the previous comment.
Connor McDonald
September 27, 2016 - 12:14 am UTC

Option 1) Make the MV refresh on commit. As you said, this will have some performance impact, but the numbers are accurate.

Option 2) Make it refresh (say) every few minutes. Keeps the numbers very close with less performance impact.

"2" makes sense to me because after all, the moment you show the count on screen it is potentially out of date anyway (because transactions keep coming in). So a little bit more out of date really makes no difference.

Cheers,
Connor
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library