Q
Problem solve Get help with specific problems with your technologies, process and projects.

Rules for creating efficient queries

Can you tell me the main rules to create an efficient query? I know there are a lot of articles about this, but I can't find these rules. I'm a DBA and I have to tune some queries.

Hi, can you tell me the main rules to create an efficient query? I know there are a lot of articles about this, but I can't find these rules. I'm a DBA and I have to tune some queries.
There are several rules for creating "efficient" queries, but not all of them apply to all situations.

  • 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.

This was last published in November 2005

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close