Deadly sins in Oracle

These are the deadly sins:

Not using bind variables
No primary keys
No foreign keys
Adding indexes all over the place for performance reasons.
No (proper) change control procedures.
Exporting from the database and defaulting COMPRESS to Y, even worse, exporting with COMPRESS=Y explicitly.
Allowing developers to create obbjects in a tablespace with a different extent size to the tablespace defaults.
Having numerous indexes (on the same table) which could be replaced by one or two carefully thought out indexes. (you know, indexes on A and B and A,B and C and A,B,C and B,C ....)
Putting the ref int checks in the application instead of the database.
Ditto, but keeping business rules in the app instead of the db.
Using standalone procs and functions instead of packages.
When actually writing packages, not separating the PACKAGE specs from the PACKAGE BODY so every time you change the code in a body, you re-create the spec as well and invalidate everything that depends upon it.
Using a table to hold a sequence number is a bad one. You know, read the number and lock it, increment it and write it back - then commit at some point in the far future. Even committing ASAP is a major problem as soon as you get more than 5 or so users all doing the same thing.
Even worse, having a single table for all the sequence numbers in the system.
Creating 'temporary' tables to hold intermediate results MS SQL Server style.
Converting triggers from another db system into Oracle and discovering that the other system allows you to update rows in the table that has triggered the trigger leading to mutating table errors on Oracle (which IMHO does it correctly !)
Not enough SQL reuse.
No primary keys
No foreign keys
Creating intermediate tables as often as possible and then drop them when done with.
Better aproach: use temporary tables
Use PL/SQL instead of SQL
Use Java SP instead of PL/SQL
Use EJB instead of the Database.
Use Net instead of the Database
Avoid array fetch at all costs.
Parse repeatedly.
Test on your laptop with a maximum of 5 rows per table.
Don't test at all, that is what pilots are for.
Return all of the table to your client and filter on the client to avoid loading the server.
Never document anything - noone will read it.
Never have a database model - that is just for classes.
Developers that don't understand the implications of the multiversion concurrency model.
Developers that don't understand the implications of reads not blocking writes and writes not blocking reads.
DBAs that don't understand the implications of ORA-01555.
Those schooled in other RDBMS products that insist on treating Oracle as if were the other product.
Organizations that don't conduct code reviews.
Developers of testing prorams that can't read code and don't know how to identify potential weaknesses and test for them.
Enforce the use of RBO instead of CBO!
Rebuild indexes periodically, in the hope that "it will help performance"
Create gazillions of indices on an OLTP system (it's not common in Siebel that more than 100 indices are defined on a table)
Have no reference integrity at the database level!
Avoid at all costs to use MV's or views!!!! (they prefer instead to use giant select select).
Letting developpers into the production base
Avoiding DBA (or at least data architect) input during design.
Having developers with no datamodeling experience dictate table and column definitions
Not using correct datatypes (ie Varchar2 instead of Number, Number instead of Date...)
SELECT DISTINCT ...
.... WHERE attr IN (SELECT DISTINCT) ...
LOOP EXECUTE IMMEDIATE '...'; END LOOP;
If there is a proven requirement to dynamically execute sql statements, use dbms_sql instead.
SELECT MAX(id)+1 as new_id FROM potentially_big_table
WHERE TRUNC(date_attr) = TRUNC(date_var)

		Deadly sins in Oracle
		I have centralized ideas that were mentioned the thread deadly sins against database performance/scalabilty on comp.database.oracle.server The thread can be found here. These are the deadly sins: Not using bind variables No primary keys No foreign keys Adding indexes all over the place for performance reasons. No (proper) change control procedures. Exporting from the database and defaulting COMPRESS to Y, even worse, exporting with COMPRESS=Y explicitly. Allowing developers to create obbjects in a tablespace with a different extent size to the tablespace defaults. Having numerous indexes (on the same table) which could be replaced by one or two carefully thought out indexes. (you know, indexes on A and B and A,B and C and A,B,C and B,C ....) Putting the ref int checks in the application instead of the database. Ditto, but keeping business rules in the app instead of the db. Using standalone procs and functions instead of packages. When actually writing packages, not separating the PACKAGE specs from the PACKAGE BODY so every time you change the code in a body, you re-create the spec as well and invalidate everything that depends upon it. Using a table to hold a sequence number is a bad one. You know, read the number and lock it, increment it and write it back - then commit at some point in the far future. Even committing ASAP is a major problem as soon as you get more than 5 or so users all doing the same thing. Even worse, having a single table for all the sequence numbers in the system. Creating 'temporary' tables to hold intermediate results MS SQL Server style. Converting triggers from another db system into Oracle and discovering that the other system allows you to update rows in the table that has triggered the trigger leading to mutating table errors on Oracle (which IMHO does it correctly !) Not enough SQL reuse. No primary keys No foreign keys Creating intermediate tables as often as possible and then drop them when done with. Better aproach: use temporary tables Use PL/SQL instead of SQL Use Java SP instead of PL/SQL Use EJB instead of the Database. Use Net instead of the Database Avoid array fetch at all costs. Parse repeatedly. Test on your laptop with a maximum of 5 rows per table. Don't test at all, that is what pilots are for. Return all of the table to your client and filter on the client to avoid loading the server. Never document anything - noone will read it. Never have a database model - that is just for classes. Developers that don't understand the implications of the multiversion concurrency model. Developers that don't understand the implications of reads not blocking writes and writes not blocking reads. DBAs that don't understand the implications of ORA-01555. Those schooled in other RDBMS products that insist on treating Oracle as if were the other product. Organizations that don't conduct code reviews. Developers of testing prorams that can't read code and don't know how to identify potential weaknesses and test for them. Enforce the use of RBO instead of CBO! Rebuild indexes periodically, in the hope that "it will help performance" Create gazillions of indices on an OLTP system (it's not common in Siebel that more than 100 indices are defined on a table) Have no reference integrity at the database level! Avoid at all costs to use MV's or views!!!! (they prefer instead to use giant select select). Letting developpers into the production base Avoiding DBA (or at least data architect) input during design. Having developers with no datamodeling experience dictate table and column definitions Not using correct datatypes (ie Varchar2 instead of Number, Number instead of Date...) SELECT DISTINCT ... .... WHERE attr IN (SELECT DISTINCT) ... LOOP EXECUTE IMMEDIATE '...'; END LOOP; If there is a proven requirement to dynamically execute sql statements, use dbms_sql instead. SELECT MAX(id)+1 as new_id FROM potentially_big_table WHERE TRUNC(date_attr) = TRUNC(date_var) Credits: I have taken this list from postings of the following persons (In no particular order): Norman Dunbar Daniel Morgan Daniel Roy Joel Garry Hans Forbrich Alkos Marc Blum


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger