links for 2006-06-30
-
Over-normalization of table structures, inadequate indexing, poor optimization of initialization parameters, poor schema statistics management, bad disk I/O sub-system and more…
-
Change the default Apps password from ‘Apps’, work with a query-only user in Prod, demand a dedicated development instance, maintain version control and create all custom objects in the custom schema.
-
SQL*Plus, Toad for Oracle, SQL Developer, Workflow Builder, XML Publisher, Discoverer, JDeveloper and Developer 6i.
-
Don’t run non-select commands in production without testing first, never change the definition of a standard database object, do not leave your desk when you have a connection open on production and more…
Related articles:
Filed in Interesting Stuff on 30 Jun 06 | Tags: Links
I’m sorry, but I think Don Burleson’s list of worst practices is far from the truth.
Normalization is not just for saving space and preventing duplicate data. It is a basic step that you MUST take to fully understand your data and your system requirements. It is essential for a flexible and easy to modify database. For instance, I have a database with physician names and addresses. I put the name and one address in a table. Now I am tasked to allow the entry of more than one address for a physician. Yes, I could add a second set of address fields, but what if we need to add a third address? What I should have done is normalize, and put the addresses in a separate table.
Can normalization be overdone? Possibly, but at least take your data to 3rd normal form, and if you decide to denormalise from there, know WHY you are doing it, what you expect to gain, AND what you will lose. That said, I am highly in favor of giving developers denormalised VIEWs of your normalised TABLES, with INSTEAD OF triggers to do the proper updates behind the scenes. This makes sure that the joins are written well, and that updates are unlikely to deadlock.
Which leads me to: Writing good SQL in your applications, plus updating statistics and having the right indexes is far more effective for database performance than tuning database initialization parameters. My experience tells me that, yes, improving an init.ora parameter can improve performance, but not orders of magnitude better performance. Tuning the SQL, and the database structures that support the SQL can give orders of magnitude better performance.
>>Can normalization be overdone? Possibly
Interesting comment, my experience must be different than yours. Also, my list is for DBA’s, not developers. Did I miss any worst practices? I hung this page last year . . .
Oracle materialized Views are a direct denormalization, a replication with update controls. I agree that you start at 3NF, but then you introduce redundancy to reduce tables joins and system overhead.
Here is my favorite example, a performance killer that I see, over and over:
http://www.dba-oracle.com/demo/design_performance.htm