If you understand what happens in the background, you will be able to make your query work its best. So, try and run explain plan on your query before executing it and see how the PE(Parsing Engine) has planned to execute it.
Understand the Key-words in Explain plan. I will have to write a more detailed post on this topic. But for now, let us go on with the highlights
Understanding Resource consumption:
Resource that you consume can be directly related to dollars. Be aware and frugal about the resources you use.
Following are the factors you need to know and check from time to time:
Parallel Efficiency / Hot amp percentage
Help the Parser:
Since the architecture has been made to be intelligent, we have to give it some respect J. You can help the parser understand data you are dealing with, by collecting statistics. But you need to be careful when you do so, due to 2 reasons:
Incorrect stats are worse than not collecting stats, so make sure your stats are not stale(old)
If your dataset changes rapidly in your table, and suppose you are dealing with a lot of data, then collecting stats itself might be resource consuming. So, based on how frequently your table will be accessed, you will have to make the call
Since same SQL can be written in different ways, you will have to know which method is better than which. For eg, creating Volatile table vs Global temp table vs working table. You cannot directly point out which is the best, But I can touch base on the pros and cons and comparison for them.
Take a step back and look at the whole process. Consider how much data you need to keep, how critical is it for your business to get the data soon, how frequently do you need to run your SQL. Most of the times, the ‘big picture’ will give you a lot of answers.