How to Tune a Join in Oracle database

There are various factors which can be considered for tuning Join conditions, below are few of them:

  • We should use “=” (equality) as first after where condition.
  • We should only use the range operators (like “Between” )where equality is not applicable.
  • We should avoid negatives like != or NOT as much as possible.
  • If possible then avoid matching patens i.e. LIKE.
  • we should try to retrieve less or specific number of rows based on the conditions.
  • Try to filter the large table at first to reduce row joined to it.
  • It is always preferred to use most filer on the largest table because it reduces the row joins , retrieve the tables in order like most filtered used should be downwards.
  • Index should be used on the tables to retrieve the records faster but preferred for very large tables except small tables.

Try to use optimizer and allow it do to its job.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s