Posted on 2009-02-11
Last Modified: 2013-11-13
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.

Question by:sam15
    LVL 25

    Expert Comment

    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..
    LVL 23

    Accepted Solution

    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" ?
    LVL 3

    Assisted Solution

    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.
    LVL 2

    Expert Comment

    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 Comment

    what does the "cache" clause do? and is this done when you create/alter the table.
    LVL 3

    Expert Comment

    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 Comment

    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.
    LVL 3

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Dependencies in Software Design In software development, the idea of dependencies ( is an issue of some importance. This article seeks to explain what dependencies are and where they …
    Transparency shows that a company is the kind of business that it wants people to think it is.
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    Via a live example, show how to take different types of Oracle backups using RMAN.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now