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

Advertisements

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

06 Index Suppression


Reasons for Index Suppression (Accidental)

There are many reasons why an index might not be used even though you have an index on the column you are searching for.

  • Use of <> operator on an indexed column
  • Use o a SUBSTR function on an indexed column
  • use of arithmetic operators (+,_,*,/) on an indexed column
  • Use of TRUNC function on an indexed column
  • Use of || operator on an indexed column
  • Data type conversion on an indexed column
  • Use of IS NULL/ IS NOT NULL on an indexed column

Continue reading

05 Simple Tuning Rules


Select Consideration


select * from sales_history

versus

Select s.order_ID, s.product_ID, s.price, s.product_name

from sales_order s

The simplest rule is to select only the data you need  instead of all of the data from the table.

oracle explain plan fr select *

oracle explain plan fr select *

Watch the size of the select * query …. its 55 MB

versus

Oracle explain plan for limited felds

Oracle explain plan for limited fields

The size is now only 29 MB

Continue reading