if I have a database with a lookup table for states used for validating data on screens.

Someone wants to add a new column (default quantity) for each state for some invoicing estimates. Does it make sense to add this to this table or create a new table for that specific purpose especially since that quantity will be updated?
any good suggestions and arguments.

Who is Participating?
DavidConnect With a Mentor Senior Oracle Database AdministratorCommented:
A different POV:  as a lookup table (they're not making new states anymore....) this should be small, bitmapped, static.  I think your quantity, and also your active_yn boolean, are probably related to an order process far more than the U.S. geography.  Secondly, I am strongly opinionated that all data constraints and defaults should be defined in the table(s).  In some table X, you have a column QUANTITY with DEFAULT "<somevalue>".
Likewise, I trust your boolean column is constrained by being in ("N","Y").  Otherwise, does your program accept trash data such as "1" ?
A default is usually a look-up value, so there shouldn't really be a problem in having that column in your above table..

The quantity wouldn't necessarily be updated in this table frequently, it should be used as a initialization value in the application and actualy updates should go to a transactional table..
garysadlerConnect With a Mentor Commented:
I agree that there's no reason why you shouldn't add the column to the existing table.  One of Oracle's strengths is in handling concurrency, so even if the column IS updated frequently, it wouldn't be a problem for other sessions that just needed to read the values from that table.  

You might also consider using the "cache" clause when altering the table so that it stays in the buffer cache.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

I would agree with DVZ, a small static in core table will provide better performance. Since I don't expect we will be adding any new states, you should never have to update the program for changes.  Even if it only saves .5 second, it is worth it for any online process.
sam15Author Commented:
what does the "cache" clause do? and is this done when you create/alter the table.
The cache clause can be used in either the CREATE TABLE or ALTER TABLE statements.  It will direct Oracle to keep blocks holding the table data at the most-recently used end of the database buffer cache LRU queue following a scan of the table.  That will, in effect, help keep a small and frequently-accessed  reference table cached in memroy.
sam15Author Commented:
I thought oracle caches everything anyhow.

if i do select  * from state_table

is not the data cached?
what about if somone changed something in the lookup? would it know that and go to the DB instead of the cache.
Data is cached in Oracle's database buffer cache, but if you do full scans of large tables, it may be forced out.  Using the "cache" directive is just a way of helping to prevent that.  

Oracle keeps track of read consistency states so you don't need to be concerned about whether a particular data block is in memory on on disk when an update occurs.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.