can any tell me about query optimization with examples..?

[rameshrevella]

Hi i am Ramesh
can any tell me about query optimization with examples..?

Mail: Rameshrevella@in.com

[dtech-team]

here's a basic tutorial with few exampes :

http://beginner-sql-tutorial.com/sql-query-tuning.htm

[ashishprak]

Query Tuning
There cannot be a perfect set of rules or tips to optimize a query.A query optimization tip might decrease the cost of a query while on the other hand it may increase the cost of some other query. The rule of thumb is to use the right optimization technique for the right query at the right time.

The following are some tips and techniques for query tuning:

a)Avoid full table scans wherever possible :
 It is advisable use indexes to avoid a full table scan as full table scan is expensive and consumes time and resources.
 
 Consider the following query:
 
 SQL>SELECT * FROM emp WHERE emp_id = 54;
 The above query results in full table scan. The cost of this query is 50000 and execution time is about 5-6 seconds. We create an index on the above query as follows:
 
 SQL> CREATE UNIQUE INDEX IDX_EMP_ID on t_asset(EMP_ID);
 
After creating index and re - running the query the cost of the query becomes 1000 and execution time is about 2-3 seconds.So it is advisable to avoid full table scans wherever possible

b)Make optimum use of indexes

i)Do not have index for every column in the table as it slows down the INSERT, UPDATE and DELETE operations for that table.
ii)Avoid specifying NULL in an indexed column.
iii)Never do a calculation on an indexed column.
iv)it is not required to create indexes on table which have less data.
v)Use indexes only if a large result set is not expected from a query.

Oracle by default use Balanced/Binary Tree indexes , Try using Bitmap indexes which are the most suitable for columns having very few unique values specially for columns like 'Y' OR 'N' / 'MALE' or 'FEMALE' /'Flag1' or 'Flag2' etc.

c) Use NOT EXISTS instead of NOT IN

Using NOT EXISTS instead of NOT IN adds a limiting condition to your queries that can reduce the number of full table scans necessary
In the following example, the aim is to find all those employees who are not managers using NOT IN

SQL> SELECT COUNT(*) FROM EMP WHERE emp_number NOT IN ( select mgr_number from EMP );

Now, rewriting the same query using NOT EXISTS
SELECT COUNT(*) FROM EMP T1 WHERE NOT EXISTS ( SELECT emp_number FROM EMP T2 WHERE t2.mgr_number = t1.emp_number );

When using "NOT IN", the query performs nested full table scans, whereas for "NOT EXISTS", query can use an index within the sub-query
Hence, it is advisable to use NOT EXISTS instead of NOT IN.

d)Use hints wherever possible
Hints are the comments in a SQL statements to pass instruction to the Oracle Database optimizer
These hints are used by oracle database optimizer to choose an execution plan for the statement, unless some conditions exists that prevents the optimizer from doing so.

A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword
The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword.

The following is the syntax for using hints in Oracle:

/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */

Example:
SELECT /*+ FIRST_ROWS(10) */ emp_id, last_name, salary, job_id
FROM emp
WHERE dept_id = 20;
The above query returns the first 10 rows of the result most efficiently.