Query huge table vs additional caching table
Posted by: Kristijan Marin
Date: September 28, 2018 01:27AM

Hi,

We have a table for messages. There is
A column ‘alreadyNotified’ tinyint values
0 or 1 if message should go out + a column for datetime if the message should go out now or later.

So my query is basically:
if message delete flag is 0 (so message is not deeted)
If alreadyNotified is set to 0
If startdate is <=now
Then this is a match.

Now this is all good... we have indexes and all that

My question is : if I’ll have a table
with 10-100 milion rows

Because we run this query every minute
there should not be more then maybe 0.001% of rows that would match our query

Will this still be quick comparing
to having a caching table where I’ll have all the rows that should go out + some rows that will go out in the near future like a scheduled message to go out every week or month...

My concern is that caching table rows get deleted when the message is send out ...

So my question is more or less is it better
To run a query on a whole table all the time with no deletion
Or
Having a caching table that is 20% of the main table but is deleting every row when this row gets out. Basically running almost the same query as on the main table but with less data in it, but here we have deletions.

Maybe I’m overthinking the whole thing right now cause we are in beginning and don’t have even 100 messages :),

Thank you for your suggestions.
Best regards,
Kris

Options: ReplyQuote


Subject
Written By
Posted
Query huge table vs additional caching table
September 28, 2018 01:27AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.