Tune SQL First
The entire reason for PL/SQL is to encapsulate SQL
into a procedural context. The procedural context allows set
processing to be pieced out into the procedural logic of a PL/SQL
program. Therefore it is of primary importance to tune all
encapsulated SQL statements to perform at an optimal level. There
are many techniques that can be used to tune SQL statements, let's
look at a few of the heavy hitters.
IN and EXISTS
When using SQL of the structure:
select id
from s_warehouse
where id in
(select warehouse_id from s_inventory);
Can usually be improved by converting it into a
statement that uses EXISTS:
select id
from s_warehouse W
where EXISTS
(select 'X' from s_inventory S
where s.warehouse_id = w.id);
the reason EXISTS usually improves performance is
that the IN clause will need to process a complete result set
while the EXISTS only proceeds until the first positive result is
reached.
In a similar technique the NOT IN clause form a
statement similar to:
select p.id,
p.name
from s_product p
where p.id not in
(select product_id
from s_inventory);
Can be altered to use NOT EXISTS:
select p.id,
p.name
from s_product p
where not exists
(select 'X'
from s_inventory i
where i.product_id = p.id)
and name like 'W%';
Notice how the use of an index is forced by the
use of a select against the name column that cannot be satisfied
(based on preknowledge of distribution in the name column) if you
don't have knowledge about the distribution in an index (or want
to use a better technique) just use the INDEX hint to specify the
index to be used:
select /*+ INDEX
(p, name_s_product_lu) */ p.id,
p.name
from s_product p
where not exists
(select 'X'
from s_inventory i
where i.product_id = p.id);
Indexes
Of course the proper use, or non-use of indexes is
also critical to proper SQL performance. If a SELECT processes
more than 5% of a tables rows (up to 20% on 9i) then a full table
scan may be more efficient. The best way to force index usage is
via the INDEX hint as shown above, there is also the NO_INDEX and
FULL hints which should be used to force specific use or non-use
of indexes.
DISTINCT
One sign of a beginning or inexperienced PL/SQL
and SQL programmer is over use of the DISTINCT clause. There
should be n-1 joins in a SQL statement where n is the number of
tables involved. If there are not n-1 joins (involving n-1 tables)
then a Cartesian join will result that will produce duplicate
rows. A beginning or inexperienced developer will get to the
point where a result set is returned, see duplicate rows and
insert a DISTINCT to reduce this to a final result. Of course the
proper technique is to add the appropriate join condition. A
DISTINCT will always generate a SORT that is a performance hit.
and include the URL for the page.
