DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: normalize

  1. #1
    Join Date
    Jan 2000
    Location
    san jose
    Posts
    149
    hi,

    can somebody gives me an example
    of denormalize improve performance.
    i am not familar with that.

  2. #2
    Join Date
    Jan 2001
    Posts
    71
    sometimes if you have to use join to combine two or more columes, you can move one not frequently changed column to another table to decrease or eliminate join. this improve proformance. but you should be careful.

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828

    De-Normalize

    suppose you have devloped a library mamnagement system
    with the folowing tables which are normlaized.with the folowing relations,
    member can borrow many books and books can be borrowed by many members additionaly a book belongs to a particular subject (hope this is plain english)

    table name(col1,col2.........)
    members(mem-id,name,..........)
    borrow (mem-id,book-code,date-borrowed,city,zip-code)
    books(book-code,desc,sub-code.....)
    subjects(sub-code,desc,)

    if you want to know which members has borrowed which books on which subjects you have to do a 4 table join.which should look something like this
    select distinct a.name,d.desc
    from membes a,borrow b,books c,subjects d
    where
    a.mem-id=b.mem-id and
    b.book-code=c.book-code and
    c.sub-code=d.sub-code

    ah now thats quite a gew joins isnt it ?
    now how about breaking some rules of normalization or denormalizing as u call it.

    lets change the dat model so that subject table is eliminated .
    so we are left with the following tables
    members(....)
    borrow(....)
    books(book-code,desc,subject)
    in order to answer our previous question of you want to know which members has borrowed which books on which subjects
    we would have to write

    select distinct a.name,c.subject
    from members a,borrow b,books v
    where
    a.mem-id=b.mem-id and
    b.book-code=c.book-code;
    you can get an answer in just 3 table join.
    isin that great :-)

    be warned denormalization has got its cost in case of real applications like the one i work upon i would probably ahve to code additional database triggers if i dont normalize to maintain refrential integrity.

    hope this helps


    hrishikesh





  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    adding to what i said on a serious note you acn have alook at denormalization in by running the script sh_mail.sql which creates the datawarehouse schema and the following atbles

    Table Number of Rows
    customers 50.000
    countries 19
    products 10.000
    sales 1.016.271
    times 1461
    promotions 500
    channels 5

    script is supplied with oarcle 9i (i am not sure about 8i though)

    hope this helps

    hrishikesh


  5. #5
    Join Date
    Aug 2000
    Posts
    462
    A simple example is the zip code/state and city relationship. Rarely do you see tables where an address is stored with just a street, number and zip, and then a lookup table with city/state. However, you can determine the city/state from the zip. It's just easier to record it every time, and most people denormalize to avoid the operational, programmatic and database complexity and maintenance headaches of normalizing this relationship.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width