Checklist for well written Oracle application

• Reference slideshow (last updated March 2006)

Use bind variables

• Place-holder variables in SQL statements that are substituted with concrete values before statement execution.
• Coding your queries using bind variables will speed up your application by avoiding the repeated parsing of the similar queries.
• Bind variables tutorial
• Using Bind variables with OCI

Use primary keys

• Column or set of columns that uniquely identifies table rows (ie differs for rows in a table) and can by used to quickly find a row
• Every table should have a primary key defined
• Oracle documentation - Choosing primary key

Define foreign keys (and add indices)

• Column that is referenced from another table of the schema (eg. TableOrder(OrderID)=TableOrderedItems(OrderId) )
• Define a foreign key constraint to allow the database to insure referencial integrety
• Add indexes on foreign key columns to speed up access for larger tables
• Oracle documentation - Mantain data integrity

Indices

• Like in a book, an index provides a quick (direct) reference to a location of data on disk (of the records with a given value)
• Add indices to columns which are refered to often in the WHERE clause of queries
• Oracle documentation - Using indexes in Application Development

Sessions

• In case the application will use several short request to the database then implement connection pooling so that persistent connections to the database are created.
• The application should also be able to retry to reconnect to the database in case the session was dropped (due network problem, expired idle time, etc).
• The application should ensure that there is a single physical connection to a given database. Multiple transactional contexts should be handled using multiple user sessions for the same physical connection.

Bulk operations

• PL/SQL
  • FORALL keyword to perform a large number of INSERT/UPDATES/DELETES
  • BULK COLLECT INTO for big SELECTs
  • When use bulk bind
• Java/JDBC
  • Disable auto-commit; Fetch size; Batch operations
  • http://oracle-documentation.web.cern.ch/oracle-documentation/10gr2doc/java.102/b14355/apxtips.htm
• OCI/OCCI
  • Array DML operations, Data buffering, Array fetching
  • OCI: http://oracle-documentation.web.cern.ch/oracle-documentation/10gr2doc/appdev.102/b14250/oci04sql.htm
  • OCCI: http://oracle-documentation.web.cern.ch/oracle-documentation/10gr2doc/appdev.102/b14294/performance.htm

Normalized schema

• Presentation on Database design

Fully qualified names

• Use the syntax "schema_name.table_name" in the FROM clause of yours queries. This prevents Oracle errors seen in the past.

Configuration file

• external and easy to edit configuration file with connection string and usernames

Tutorials and presentations:

• WLCG Service Reliability Workshop - November 2007
• Database workshop at CERN - January 2005