Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, June 26, 2009

Oracle and the Law of Attraction

If you have seen the movie "The Secret", you would be well aware of Law of Attraction. One of the tenets of Law of Attraction is that you should always focus on what you want and never on what you do not want. The universe is all inclusive and doesn't exclude. So if you say you want money, you'll get money, but if you say I don't want poverty, you'll get poverty. If you say I don't want disease, you get disease. So you should say that I want wellness instead of saying I don't want disease.

During the HotSoS Oracle performance management, I learnt that Oracle's optimizer also follows similar principle.

The clauses NOT EXISTS and NOT IN take more time and do more LIOs. This is because the optimizer will grab all rows and check each row for the NOT condition and drop it. This always takes more time. For example

select name,salary from emp where salary <>50000 is going to take more time than

select name,salary from emp where salary < 50000

You should avoid these NOT clauses and try to create your query with EXISTS or IN.

No comments: