1. simple search

1. simple search (only one table)

Table 2-1. Search Condition Point Counts for Operators

Operator	Points
=	10
>	5
>=	5
<	5
<=	5
LIKE	3
<>	0

Table 2-2. Search Condition Point Counts for Operands

Operand	Points
Literal alone	10
Column alone	5
Parameter alone	5
Multioperand expression	3
Exact numeric data type	2
Other numeric data type	1
Temporal data type	1
Character data type	0
NULL	0

The Bottom Line: General Tuning

The left side of a search condition should be a simple column name; the right side should be an easy-to-look-up value.

Each component of a search condition has a point count. The higher the points, the faster the component. The condition that takes the least time gets the most points.

Put multiple expressions in the correct order.

Use the Law of Transitivity and the concept of constant propagation to substitute a literal for a column name or column expression whenever you can do so without changing the meaning of an expression.

Some DBMSs won't fold most obvious-looking (to a C programmer) expressions. Don't use this principle as the reason to always transform. such expressions when you find them in old code though—usually they're there for historical reasons. Remember Rule #1: Understand the code before changing it.

If the code involves an obvious math expression, do evaluate it and transform. the condition to the evaluated result. Constant folding can lead to constant propagation and is therefore A Good Thing.

Avoid functions on columns.

If you can't avoid functions, don't use UPPER to ensure case insensitivity. Use LOWER instead

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23895263/viewspace-680587/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23895263/viewspace-680587/