- Cache frequently referenced tables and indexes in the KEEP pool.
- Use temporary tables (GTT) to improve SQL performance of complex queries.
- Implement query re-write within an Oracle database (materialized views)
- Use dbms_stats for good execution plans.
- Check to make sure that your query uses the most selective possible index (especially function-based indexes). Check the "where" clause of the query and compare indexes in dba_indexes.
- Avoid subqueries whenever possible, but if you must have them, use IN if most of the filtering conditions are in the subquery; use EXISTS otherwise.
Here is a good list of tips for writing efficient SQL.
Dig Deeper on Oracle and SQL
Related Q&A from Don Burleson
Suppose you have a table which has only two columns, namely locid (not primary) and place. In Oracle, which query should I run to get the output as ... Continue Reading
I have created a table, Party, and in it there are two main fields, party_code and party_name, and around 2,500 records are available in it. I passed... Continue Reading
I have a query that retrieves around 10 records. I have a task to concatenate the values separated with ','. I would like to know if Oracle has any ... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.