1. Member
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. Junior Member
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.

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

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. Senior Member
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

×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.