Total Pageviews

March 28, 2016

3/28/2016 06:48:00 PM
Use aliases
Whenever you issue a DML command, Oracle must match each column with its corresponding table. Aliases reduce that time. When using aliases, strive for single character names.
For example:
select event_id, event_role.caterer_id, caterer_name from event_role, caterer
where   event_role.caterer_id = caterer.caterer_id and role = 'Manager';
Will cause Oracle to resolve the fields event_id and caterer_name before solving the query.  A faster query is:
select a.event_id, a.caterer_id, b.caterer_name from event_role a, caterer b
where   a.caterer_id = b.caterer_id and a.role = 'Manager';
 Driving Table goes Last
Oracle processes the results one table at a time. This process begins with what is known as the driving table. After Oracle retrieves the data from the driving table, it uses the results to limit the number of rows processed for the remaining tables, also called driven tables.
The driving table is usually the last one in the FROM clause. In the above example, the driving table is caterer and the driven table is event_role. This makes sense, as the event_role table is likely to contain more records than the caterer table.
This is not always the case, mostly because the process is highly dependent on the optimizer in use by the database engine. 
There are 2: Rule and Cost based. In general, the optimizer selects a driving table for each DML statement. If it cannot make a decision, then it follows the order explained above to determine which one the driving table will be.

In the same order of ideas, subsequent tables should follow the same order. This is, the one that provides a larger result set should be the one placed last, followed by the others in descending order, with the one expected to retrieve the least amount of rows next to the FROM clause.
  
The WHERE clause, on the other hand, should be placed in the opposite order.  This is, the conditions of the driving tables first.
Using the above example, we might be tempted to write an optimized version of the query as follows:
select a.event_id, a.caterer_id, b.caterer_name from event_role a, caterer b
where   b.caterer_id = a.caterer_id and a.role = 'Manager';
However, in the above example, Oracle might choose event_role as the driving table, mostly because there are 2 limiting conditions in the WHERE clause.  Thus, a better option to assist the optimizer might be:
select a.event_id, a.caterer_id, b.caterer_name from event_role a, caterer b
where   a.role = 'Manager' and a.caterer_id = b.caterer_id;
The above will guide the optimizer more efficiently making table event_role return fewer rows. The order of the items in both the FROM and WHERE clause will not force the optimizer to pick a specific table as a driving table, but it may influence its decision. The grouping of limiting conditions onto a single table will reduce the number of rows returned from that table, and will therefore make it a stronger candidate for becoming the driving table.

Using Indexes

Indexes reduce the time that Oracle spends searching for data. By default, primary keys are indexed, but others are also  candidates  for indexing. Fields that are likely to be used often for retrieval by itself, or in joins must be indexed. A good example is the Social Security Field. Sometimes, last names are indexed.
The main reason for creating an index for use in the WHERE clause:
where   a.role = 'Manager'
However, the presence of an index on a column does not guarantee that it will be used. Among the factors that can prevent an index from being used, are:
·         The indexed column is used in mathematical operations:
where   salary = 100 + 50
·         The indexed column is concatenated:
where   last_name || ', ' || first_name = 'Garcia, Maria'

·         The optimizer decides it would be more efficient not to use the index. In general, if the data is distributed evenly, Oracle will use the index if

it restricts the number rows returned to 5% or less of the total number of rows.
One word of caution: excessive indexing can reduce INSERT, UPDATE and DELETE performance.
Joins are faster than Subqueries

A subquery will most likely be slower than a join. This is due to the way in which Oracle retrieves the results.

EXISTS is faster than IN

EXISTS searches for the presence of a single row meeting the stated criteria. On the other hand, IN looks for all occurrences. For example:
Caterer:             30 rows
Event_Role: 120 records

select caterer_id, caterer_name from caterer
where caterer_id IN ( select *
from event_role );
Oracle will read all 120 records in the event_role table for each of the 30 rows in the caterer table. This results in 3,600 rows read.
select caterer_id, caterer_name from caterer
where EXISTS ( select *
from event_role );
Oracle will read a maximum of 1 record in the event_role table for each of the 30 rows in the caterer table. This results in 30 rows read.

Computing Fields v. Storing computed fields

If the computation is likely to be retrieved frequently, it’s better to store it. Otherwise, calculate it on the spot. Make sure that the data  can  be computed despite of changes in the database. For example, the total amount of an invoice may or may not be recomputable.

Note:This notes is know the the basics of Oracle SQL tuning tips.