In order to improve overall application performance, it’s very important to construct SQL queries in the most efficient way. There can be many different ways to write a SQL query. Here are few tips that can help you in writing efficient and reusable SQL queries. All examples given below are based on Oracle default demo tables EMP and DEPT. You can create these tables in your local schema from the following directory in windows environment to test and practice the same.
%ORACLE_HOME%\sqlplus\demo\demobld.sql
2.0 Hint #1: Order of the tables in Joins
If you specify 2 or more tables in the FROM clause of a SELECT statement, then Oracle parser will process the tables from right to left, so the table name you specify last will be processed first. In this case you have to choose one table as driving table.
Always choose the table with less number of records as the driving table
3.0 Hint #2: Name the Columns in a Query
There are three good reasons why it is better to name the Columns in a query rather than to use "select * from ...”
3.1 Network traffic is reduced. This can have a significant impact on performance if the table has a large number of columns, or the table has a long or long raw column (both of which can be up to 2 GB in length). These types of columns will take a long time to transfer over the network and so they should not be fetched from the database unless they are specifically required.
3.2 The code is easier to understand.
3.3 It could save the need for changes in the future. If any columns is added to or removed from the base table/view, then “select * “statement can produce wrong results set and statement may fail.
4.0 Hint #3: Use table alias
Always use table alias and prefix all column names with the aliases when you are using more than one table
5.0 Hint #4: Use Bind Variables
It is also better to use bind variables in queries. That way the query becomes generic and therefore re-usable. For example, instead of writing a query like:
SELECT ename, sal
FROM EMP
WHERE deptno = 20;
Change it to -
SELECT ename, sal
FROM EMP
WHERE deptno =: deptno;
The first query can be re-used for deptno number 20 only, whereas the second query can be reused
for any other deptno also.
6.0 Hint #5: SQL Writing Convention
It is a good practice to use a standard syntax for writing SQL queries.
Write all standard SQL TEXT in upper case:
For example:
SELECT ename, sal
FROM EMP
WHERE deptno = 20;
Write all non standard SQL TEXT (Table name, Column name etc) in lower case:
For example:
SELECT ename, sal
FROM EMP
WHERE deptno = 20;
Note: It is important to write similar SQL statement in same case.
For example: Oracle will reparse following queries as they are not written in the same case
Select * from EMP;
Select * from emp;
7.0 Hint #6: Use EXISTS instead of DISTINCT
Use EXISTS in place of DISTINCT if you want the result set to contain distinct values while joining tables.
For example:
SELECT DISTINCT d.deptno, d.dname
FROM DEPT d, EMP e
WHERE d.deptno = e.deptno;
Better alternative SQL statement
SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS (SELECT e.deptno
FROM EMP e
WHERE d.deptno = e.deptno);
8.0 Hint #7: Use of expressions and indexes
The optimizer fully evaluates expressions whenever possible and translates certain syntactic constructs into equivalent constructs. This is done either because Oracle can more quickly evaluate the resulting expression than the original expression or because the original expression is merely a syntactic equivalent of the resulting expression.
Any computation of constants is performed only once when the statement is optimized rather than each time the statement is executed. Consider these conditions that test for salaries
Greater than $2000
Sal > 24000/12
Sal > 2000
Sal*12 > 24000
If a SQL statement contains the first condition, the optimizer simplifies it into the second condition.
Please note that optimizer does not simplify expressions across comparison operators. The optimizer does not simplify the third expression into the second. For this reason, we should write conditions that compare columns with constants whenever possible, rather than conditions with expressions involving columns.
The Optimizer does not use index for the following statement:
SELECT empno
FROM EMP
WHERE sal*12 > 24000
Instead of this use the following statement:
SELECT empno FROM EMP
WHERE sal > 24000/12;
9.0 Hint #8: Use of NOT operator on indexed columns
Never use NOT operator on an indexed column. Whenever Oracle encounters a NOT on an index column, it will perform full-table scan.
For Example:
SELECT empno
FROM EMP
WHERE NOT deptno = 0;
Instead use the following:
SELECT empno
FROM EMP
WHERE deptno > 0;
10.0 Hint #9: Function or Calculation on indexed columns
Never use a function or calculation on any indexed column. If there is any function is used on an index column, optimizer will not use index.
For Example:
Do not use until need exactly match string:
SELECT empno
FROM EMP
WHERE SUBSTR (ename, 1, 3) = 'MIL';
Use the below approach instead:
SELECT empno
FROM EMP
WHERE ename LIKE 'MIL%';
Do not use the following as || is the concatenate function. Like other functions and it disables Index.
SELECT empno
FROM EMP
WHERE ename || job = 'MILLERCLERK';
Use the following instead
SELECT empno
FROM EMP
WHERE ename = 'MILLER' AND job = 'CLERK';.
11.0 Hint #10: Avoid Transformed Columns in the WHERE Clause
Use untransformed column values.
For example, use:
WHERE a.order_no = b.order_no
Rather than
WHERE TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))
= TO_NUMBER (SUBSTR(a.order_no, INSTR(b.order_no, '.') - 1))
12.0 Hint #11: Combine Multiples Scans with CASE Statements
Often, it is necessary to calculate different aggregates on various sets of tables. Usually, this is done with multiple scans on the table, but it is easy to calculate all the aggregates with one single scan. Eliminating n-1 scans can greatly improve performance.
Combining multiple scans into one scan can be done by moving the WHERE condition of each scan into a CASE statement, which filters the data for the aggregation. For each aggregation, there could be another column that retrieves the data.
The following example has count of all employees who earn less then 2000, between 2000 and 4000 and more than 4000 each month; this can be done with three separate queries.
SELECT COUNT (*)
FROM EMP
WHERE sal <>
SELECT COUNT (*)
FROM EMP
WHERE sal BETWEEN 2000 AND 4000;
SELECT COUNT (*)
FROM EMP
WHERE sal>4000;
However, it is more efficient to run the entire query in a single statement. Each number is calculated as one column, the count uses a filter with the CASE statement to count only the rows where the condition is valid like
SELECT COUNT (CASE WHEN sal <>
THEN 1 ELSE null END) count1,
COUNT (CASE WHEN sal BETWEEN 2001 AND 4000
THEN 1 ELSE null END) count2,
COUNT (CASE WHEN sal > 4000
THEN 1 ELSE null END) count3
FROM EMP;
13.0 Hint #12: Proper Data Casting and Index Search
To avoid Data Casting Problems it is better place proper data type for the Index Search
If JOB is a VARCHAR2 column, the following statement will not use
use an index.
Select sum (sal)
from EMP
where deptno=36
and job = '20001999'
Runs for 0.09 Seconds
Select sum (sal)
from EMP
where deptno=36
and job = 20001999
Runs for 4.97 Seconds
14.0 Hint #13: Beware more than 5 Tables Joined.
It is better to plan the Query based on the requirement and Business Logic, use the proper optimization approach to overcome the performance issues. It is always good practice not to join the multiple tables without knowing those indexes and count of records. Approach the DBA for tuning the Query.
15.0 Hint #14: Carefully Select Your Control Structures
Control flow and conditional statements, if-then-else and loops, are the basic building blocks of any programming language. Both these structures provide several different variations and forms to choose from. It it better and comfortable with using one kind of structure only and rarely do a comparison when implementing, especially in case of loop structures
Use of CASE statements, is a much better alternative to nested if-elsif statements, particularly when number of conditions to check for is large.
16.0 Hint #15: De-normalize frequently joined tables
17.0 Hint # 16: Make sure indexes are being used where appropriate
Functions on an indexed column (except for Oracle8i and above function-based indexes) will prevent the use of an index.
Example Select
18.0 Hint # 17 Optimization Approaches and Goals using ‘Oracle Hint Options’
Use the below Optimization Hints in the SQL queries for better performance
/*+ ALL_ROWS */
Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
/*+ CHOOSE */
Causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement
/*+ FIRST_ROWS */
Explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).
/*+ RULE */
Explicitly chooses rule-based optimization for a statement block.
Access Methods
/*+ AND_EQUAL(table index) */
Explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.
/*+ CLUSTER(table) */
Explicitly chooses a cluster scan to access the specified table.
/*+ FULL(table) */
Explicitly chooses a full table scan for the specified table.
/*+ HASH(table) */
Explicitly chooses a hash scan to access the specified table.
/*+ HASH_AJ(table) */
Transforms a NOT IN sub query into a hash anti-join to access the specified table
/*+ HASH_SJ(table) */
Transforms a NOT IN sub query into a hash semi-join to access the specified table
/*+ INDEX(table index) */
Explicitly chooses an index scan for the specified table.
/*+ INDEX_ASC(table index) */
Explicitly chooses an ascending-range index scan for the specified table.
/*+ INDEX_COMBINE(table index) */
If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate. If particular indexes are given as arguments, the optimizer tries to use some Boolean combination of those particular bitmap indexes.
/*+ INDEX_DESC(table index) */
Explicitly chooses a descending-range index scan for the specified table.
/*+ INDEX_FFS(table index) */
Causes a fast full index scan to be performed rather than a full table scan
/*+ MERGE_AJ(table) */
Transforms a NOT IN sub query into a merge anti-join to access the specified table
/*+ MERGE_SJ(table) */
Transforms a correlated EXISTS sub query into a merge semi-join to access the specified table.
/*+ NO_EXPAND */
Prevents the optimizer from considering OR expansion for queries having OR or IN conditions in the WHERE clause.
/*+ NO_INDEX(table index) */
Instructs the optimizer not to consider a scan on the specified index or indexes. If no indexes are specified, the optimizer does not consider a scan on any index defined on the table
/*+ NOREWRITE */
Disables query rewrite for the query block, overriding a TRUE setting of the QUERY_REWRITE_ENABLED parameter.
/*+ ORDERED_PREDICATES */
Forces the optimizer to preserve the order of predicate evaluation (except predicates used in index keys), as specified in the WHERE clause of SELECT statements.
/*+ REWRITE (view [,...]) */
Enforces query rewrite. If you specify a view list and the list contains an eligible materialized view, Oracle will use that view regardless of the cost. No views outside of the list are considered. If you do not specify a view list, Oracle will search for an eligible materialized view and always use it regardless of the cost.
/*+ ROWID(table) */
Explicitly chooses a table scan by rowid for the specified table.
/*+ USE_CONCAT */
Forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator.
Join Orders
/*+ ORDERED */
Causes Oracle to join tables in the order in which they appear in the FROM clause
/*+ STAR */
Forces the large tables to be joined last using nested-loops join on the index
Join Operations
/*+ DRIVING_SITE(table) */
Forces query execution to be done at a different site from that selected by Oracle.
/*+ USE_HASH(table) */
Causes Oracle to join each specified table with another row source with a hash join.
/*+ USE_MERGE(table) */
Causes Oracle to join each specified table with another row source with a sort-merge join.
/*+ USE_NL(table) */
Causes Oracle to join each specified table to another row source with a nested-loops join using the specified table as the inner table.
Parallel Execution
Note: Oracle ignores parallel hints on a temporary table. For more information on temporary tables, see "CREATE TABLE" and Oracle8i Concepts.
/*+ APPEND */
/*+ NOAPPEND */
Specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the INSERT keyword.
/*+ NOPARALLEL(table) */
Disables parallel scanning of a table, even if the table was created with a PARALLEL clause.
Restriction: You cannot parallelize a query involving a nested table.
/*+ PARALLEL(table)
/*+ PARALLEL(table, integer) */
Lets you specify parallel execution of DML and queries on the table; integer specifies the desired degree of parallelism, which is the number of parallel threads that can be used for the operation. Each parallel thread may use one or two parallel execution servers. If you do not specify integer, Oracle computes a value using the PARALLEL_THREADS_PER_CPU parameter. If no parallel hint is specified, Oracle uses the existing degree of parallelism for the table.
DELETE, INSERT, and UPDATE operations are considered for parallelization only if the session is in a PARALLEL DML enabled mode. (Use ALTER SESSION ENABLE PARALLEL DML to enter this mode.)
/*+ PARALLEL_INDEX
Allows you to parallelize fast full index scans for partitioned and non-partitioned indexes that have the PARALLEL attribute
/*+ PQ_DISTRIBUTE
(table, outer_distribution, inner_distribution) */
Specifies how rows of joined tables should be distributed between producer and consumer query servers. The four possible distribution methods are NONE, HASH, BROADCAST, and PARTITION. However, only a subset of the combinations of outer and inner distributions are valid. For the permitted combinations of distributions for the outer and inner join tables,
/*+ NOPARALLEL_INDEX */
Overrides a PARALLEL attribute setting on an index.
Other Hints
/*+ CACHE */
Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed
/*+ NOCACHE */
Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed.
/*+ MERGE(table) */
Causes Oracle to evaluate complex views or sub queries before the surrounding query.
/*+ NO_MERGE(table) */
Causes Oracle not to merge mergeable views.
/*+ PUSH_JOIN_PRED(table) */
Causes the optimizer to evaluate, on a cost basis, whether to push individual join predicates into the view
/*+ NO_PUSH_JOIN_PRED(table) */
Prevents pushing of a join predicate into the view.
/*+ PUSH_SUBQ */
Causes nonmerged sub queries to be evaluated at the earliest possible place in the execution plan.
/*+ STAR_TRANSFORMATION */
Makes the optimizer use...
19.0 Conclusion Graph on Tuning Issues at Database Level
20.0 Solution
20.1 Choose the best SQL optimizer mode
20.2 Perform trend-based and proactive tuning
20.3 Reduce disk I/O with materialized views, SQL tuning and indexes
20.4 Use Optimization Hints
21.0 Reference
§ Oracle High Performance Technical Support @site http://www.oreilly.com/catalog/oracle2/
§ Oracle High-Performance SQL Tuning by Donald K. Burleson
No comments:
Post a Comment