We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

taable_design

Medium Priority
280 Views
Last Modified: 2013-11-13
if I have a database with a lookup table for states used for validating data on screens.
REF_STATE_CODES
-------------------------
state_code,
state_name
active_yn
created_date
created_by

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.





Comment
Watch Question

Commented:
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..
Sr Software Engineer
CERTIFIED EXPERT
Commented:
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" ?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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.

Commented:
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.

Author

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.

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.