12 Oracle Design Consideration

Posted on October 12, 2016 by curryNcode

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.

11 House Keeping in Oracle

Posted on October 12, 2016 by curryNcode

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

Posted on October 12, 2016 by curryNcode

Hints

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)

Posted on October 12, 2016 by curryNcode

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 →