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.

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.