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.
