Query optimization in Oracle DB

Short article to help newbies (like me) find a way where to go for optimizing SQL queries in Oracle.

First of all, before you started "dance with optimization" you need to know about "short-circuit" run. When you make a SELECT with many conditions Oracle handles this with short-circuit execution, so order of parameters is really matters. You should use optimal parameters order first of all: if Oracle will need to check only one condition from three, this is good way of optimization! Let's think about example of selecting records in a dates range and filter out these records by NULL-field:

SELECT * FROM Table t WHERE t.SomeField not null
AND t.StartDate between SomeDate1 and SomeDate1+0.999999

This query could be more effective if you set Dates Condition first, because Oracle will filter out MOST part of data and it will be more easy to check last one. Depending on the count of data, this could be very helpful.

Second base knowing is Query Plan. Such application like "Oracle SQL Developer" have a tool named "Explain Plan". You can run your query, select "Explain plan" and on the numbers - which part of a query is not effective. For example, if you see conditions with FULL traversal then you should set INDEX to these columns in tables. Without indexes you can't run your database fast enough.