This article discusses how denormalising your Oracle database can make a big difference to its performance. Other articles can be accessed from http://www.smart-soft.co.uk/Oracle/oracle-tips-and-tricks.htm .
Let's quickly recap the techniques you have available to you to improve the
performance of your database:- (1) Full
table scans ; (2) Indexes
; (3) Joins; (4) Views
; (5) De-normalization
(6) Oracle Stored Procedures;
(7) Sub-queries
"No major application
will run in Third Normal Form."
George
Koch - Oracle8:The Complete Reference
There is a very common misconception among developers that an Oracle database should be normalised to third normal form or above. This is not the case - normalisation is (or should be anyway) part of the analysis stage not the design stage. The reason is that normalisation increases the complexity of the database and increases the number of tables that have to be joined to provide the required information.
The emphasis is on major (large) applications. A small application with only a few tables will not suffer unduly from having a normalised database. However for a large multi-user application an Oracle database in 3NF or above will quickly grind to a halt as your Oracle database struggles to join all the required tables, particularly if any of the tables are quite large.
Remember we've already seen that the performance of a join query deteriorates rapidly as more tables are added to the join. The space requirements will also be higher as each table will require its own indexes and each update transaction may have to write to several tables instead of just the one.
So what should we do when designing our Oracle database? The first thing to do is to forget the theory - this is the real world . The next thing to do is to analyse the information requirements (queries) and the update requirements.
You need to subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours searching for information. Subscribe today and your first issue will soon be winging its way to your mailbox.
The information requirements tell us which pieces of data are needed together (eg. customer name, address, account transaction history, etc.) and how often. The update requirements tell us which pieces of data are changed, and again how often. Together this tells us which items of data are often required together and whether or not they are updated together. This will then indicate which entities should be combined to form one table. There is of course a trade-off to be made in denormalising for speed of access but normalising to simplify maintenance and reduce storage requirements caused by duplication of data.
Assume we have a HR application using our Oracle database with an employee table with one column being the id of the department for which they work. The data requirements are found to be the normal employee details (name, address, salary, job title, holiday entitlement etc.) plus the name of their department.
This obviously requires a join between the employee table and the department table which translates the department id into a department name. If the department table contained no other information then it could be eliminated and the department name stored on the employee table instead.
Even if the department table had more fields then just the id and the name it would still be worth storing the department name on the employee table, redundantly, if the department names were very rarely updated, if not the query time saved would be more than lost by the extra time required to update all employee records whenever the department name changed.
Another very common use of denormalisation is for summary information. This can be achieved either by creating extra tables (or using materialized views in Oracle 8i and above) or by creating extra columns in existing tables.
Assume we're building a management information system for a large retailer that wants to track sales by product, by sales person, by department, by month, by quarter, by year to date and by region. In short the top management want to spend all day analyzing where the money comes from. At the bottom level we have our sales transactions which record for each sale, the date, the sales person, the product id, the department id and the region id.
The quickest and easiest way to display summaries is obviously just to write a few queries that sum the transactions for the various parameters. However if these summaries are required frequently or by many different people, then performance of the application will be a major issue especially as they are required at many different levels.
As a first stab at the problem, we decide to create some extra columns on the transaction table to store the cumulative total for the year to date, but realise very quickly, that this doesn't help because we're storing the same information many times over, so we go back to the drawing board.
This time we decide to create some summary tables in our Oracle database. On looking at the requirements we realise that we need a summary by product, a summary by department and a summary by region plus an overall summary with each summary providing a total for the month, the quarter and the year to date.
Let's concentrate on the product summaries as all the others will be very similar. One way of doing this would be to start with a bottom level summary table containing the total for each product with one row per month, then create a quarterly product summary table with one row per quarter and finally a ytd summary table with one row containing the year to date figure for each product.
This gives a set of tables like this:
Alternatively we could flip the lowest level product summary table around and have one column for each of the 12 months in the year. Once we've done this we can then add other columns for the quarterly total and the year to date total. That way we've not only eliminated the summing of every transaction record we've also made it possible to provide the totals for the month, the quarter and the year to date in one hit. In this case the table would look like this:
product_summary
prd m1 m2 m3 m4 m5 m6 m7
m8 m9 m10 m11 m12 q1 q2 q3 q4 ytd
--- -- -- -- -- -- -- -- -- -- --- --- --- -- -- -- -- ---
1 10 6 9 4 4 3 0 0 0 0 0 0 25 11 0 0 36
The final type of denormalisation is to include the detail record with the master record. This is useful when the most recent detail record is the one most often required and previous detail records are held for archive purposes.
For example if a customer database was required to store customers' past and present addresses, this would usually be normalised to create a separate customer address table. However, most of the time, previous addresses would not be of much interest so the current address could be stored redundantly in the customer table.
Remember normalization is part of the analysis phase not the design phase. A fully normalised database will have severe performance problems so as Jason Couchman author of Oracle8 Certified Professional DBA Certification Exam Guide says "normalize until it hurts, denormalize until it works" !
The 3 main ways to denormalise your Oracle database are: eliminate joins; create summary tables; store detail records in the master table.
See Oracle Performance Tuning Part 6 to learn about the use of Oracle Stored Procedures. See the Oracle resources section for other web sites and recommended books etc. For more tips and tricks on Oracle see the Oracle Tips & Tricks page
product_qtr_summary
prod_id qtr sales_tot
------- --- ---------
1
1 40550
2 1 43798
3 1 44060
product_ytd_summary
prod_id sales_tot
------- -----------
1 40550
2 43798
3 44060
product_month_summary
prod_id month sales_total
------- ----- -----------
1
1 10000
2 1 10863
3 1 13780
1
2 20010
2 2 18903
3 2 17970
1
3 10540
2 3 14032
3 3 12310
--------------------------------------
Are your Oracle developments over running, over budget or just failing to deliver? Learn the short cuts with high quality Oracle training from our top consultants . See the Oracle training page for more info. Details of our Oracle training courses including locations and dates are available here. For booking or further details, just click here and complete the form to have somebody contact you, or call 0845 0031320 .
Looking to sky-rocket productivity, save time and reduce costs ? Discover how to develop your Oracle systems better, faster and smarter with help from our top consultants . Contact us for more info.
Smartsoft Computing Ltd
Bristol, England
Tel: 0845 0031320
Contact Us
Click here to view our privacy policy .
This site uses woopra.com to gather statistical information about our visitors. View woopra privacy policy .
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
© Copyright Smartsoft Computing Ltd 2001-2009. All rights reserved.