Queries which use DISTINCT, UNION, MINUS, INTERSECT, ORDER BY and GROUP BY call upon the kernel to perform resource intensive sorts. A DISTINCT requires one sort, the other set operators require at least two sorts. Other ways of writing these queries should be found. Most queries that use the set operators, UNION,MINUS and INTERSECT, can be rewritten in other ways.
2) Use realistic test data.
Realistic test data, which matches both in volume and values, that shown in the ER diagrams will be used.
The following is based on the example used in Investigate and Rewriting the Query about rewording queries. There are now 999 people in the table and 9990 jobs. Thus we have increased our people by a factor of ten. Notice how the query performed with an outer join is much quicker than that performed by the subquery. This is a complete reversal of the result in Investigate and Rewriting the Query.
This demonstrates quite clearly that the sql developer must have a knowledge of both the structure of the data and the typical number of rows in each table.
SELECT p.name
FROM people p, job j
WHERE p.name = j.name(+)
AND j.name
is null
fred the 218
Time 23.20 secs.
SELECT name
FROM people
WHERE not name in
(SELECT name
FROM job)
fred the 218
Time 193.46 secs.
SELECT name
FROM people p
WHERE not name in
(SELECT name
FROM job j
WHERE p.name = j.name)
fred the 218
Time 8.66 secs.
3) Use of != operator
Unless it is absolutely necessary avoid using the
!= operator.
The use of this operator disables the use
of indexes, because
the Oracle kernel assumes that the
query will be retrieving most of the rows in the table.
4) Use Oracle's trace facility.
For every query, even the simplest, check the output from oracle's trace facility. The first step in optimising queries is to eliminate FTS's, this can be done using trace. See Explain Facility for details on the trace facility.
5) The 10,15,20 percent rule
Only use indexes for retrieving data from tables, where your want less than 15 percent of the rows. FTS's are quicker if you want more than 15 percent of a tables data. The rule is called the 10,15,20 percent rule, because it depends on which oracle document you read, as to whether its 10,15 or 20 percent.
6) Insert statements.
Use of * in insert statements
Although the use of the shorthand character '*' is allowed by the oracle kernel, it is considered to be bad programming practice. It is not allowed in the WMS project. The reason for it being bad practice, is that if a table is modified during an upgrade to the database, subsequent inserts could fail out in the field.
