Often you may be faced with a requirement to optimize a SQL query (increase query running time) to be executed in Oracle database. Query Optimization in general is a huge topic to discuss. Let me provide some basic steps to be followed while optimizing a SQL query.
1. If the query has only one table and certain where conditions, then try to index the columns given in the where condition. By default primary key columns are indexed. Then try to reorder the set of where conditions based on number of rows for the column values in descending order.
2. If the query contains more than one table (joins), first try to order the tables in descending order (number of rows) in the 'from' clause. Then try to reorder the set of where conditions based on number of rows for the column values in descending order. Try executing the SQL query in TOAD to get the explain plan and then find the tables which are taking more time to retrieve rows in explain plan and index the required columns in those tables.
3. Try using SQL hints to make the query execution faster. Let see some of the common hints available in Oracle
ORDERED - This hint will execute the query in the same order of tables.
INDEX(tablename indexname) - this hint will force the system to use the particular index while executing the query
USE_NL(table1, table2) - this hint will use nested loop joins to join table1 and table2.
WHERE E.DEPT_ID = D.DEPT_ID
We can also use more than one hint in a single query as follows.
WHERE E.DEPT_ID = D.DEPT_ID
Oracle DBMS will optimize every query it receives for execution. But if you are not satisfied with the default optimization done and query execution time you can follow the above steps to optimize your query.