Store rarely changed data but frequently accessed, which is solution?
My Oracle database contains some simple, rarely-changed data (it takes about months or even years for it to change). But it's frequently accessed (hundred times/day). I think to continuously access it in database is expensive.
EDIT: I'm thinking about an alternative way to store this data, not in database, for example, store it in my app's cache, but I'm really confused about data consistency. How can I do this efficiently?
EDIT: my original question is quite too general. I want to explain it clearer:
I have a table that contains:
MinValue MaxValue PackageID 1 4 1 5 10 2 11 50 3
When the client send an request to our service, it will send the amount, then our service has to determine which package this request belong to. This depends on the amount, and may be changed due to business needs (as I mentioned before, it's very rarely changed).
I use this query to do this:
select packageid from vmeet_temp where amount between minvalue and maxvalue
Yes, it does work. But since I'm an inexperienced programmer, I doubt that if there's more efficient way to archive this.
So my question is: for our need, should we store this information in database, or not? If not, which solution to go?
Store it in a table, and let the DBMS worry about it. It has caching and is good at making sure that frequently used data is stored in memory so it does not need to go to disk for it. If you really want to, you can load the data into your application - you run the (small) risk that the data will be stale. But generally, let the DBMS worry about it and it will get it right for you.
If the table is only 10 rows, and the rows are modest in size (up to a few hundred bytes each), then the DBMS will probably not bother with using an index even if you create one - it knows that it will be simpler and more efficient to use the table directly. Even simple-minded optimizers manage that. Obviously, if you bludgeon it into using the index with misplaced hints, then you get to pay the performance penalty. If left to its own devices, it is unlikely the optimizer would use the index.