12 Oracle Design Consideration

Effective Schema Design

  • The Schema Plays an important role in  tuning joins
  • Choose data types for all primary and unique keys carefully, and remain consistent through out the schema
  • If you primary key column is varchar2(100), such a primary key is expensive because the large key must be carried in every referencing table, and also because Oracle bust compare long strings when performing a join
  • Create Index where necessary and if possible try to create indexes on multiple columns
  • Normalize your tables , so that there is not a lot of redundancy
  • Put necessary constraints on the table so that you have the right data.

Continue reading


11 House Keeping in Oracle

Invalid Optimizer Statistics

  • Statistics are used by the optimizer to come up with a good execution plan
  • Sometimes optimizer statistics might be  stale or missing

The statistics may not always be accurate because the size of the table might have changed from a few 100k rows to a few million rows. There fore statistics should be periodically collected  and be made as part of the database maintenance routines.

Continue reading

10 Oracle Hints to the Database


A hint is an instruction passed to the optimizer through comments in an SQL statement. Hints enable you to make decisions normally made automatically by the optimizer.

Continue reading

08 Considerations while using PLSQL in programming (Oracle)

Reduce the number of trips to the Database

Every time SQL is executed, Oracle needs to perform all the internal processing steps, so try to reduce the number of database accesses. This reduces a lot of burden on the database.

Example: If you want to fetch the data of 2 products in the PL/SQL programming language.

Continue reading