Avoid using functions in the WHERE clause of SQL statements.

---- Start

InWhereAvoid using functions on fields as much as possible in the clause, because this will invalidate the index on the field and affect the performance of SQL statements. Even if this field does not have an index, you should avoid using a function on the field. Consider the following:

Create Table user <br/> (<br/> name varchar (20) not null, --- name <br/> registerdate timestamp --- registration time <br/> ); 

What should I do if I ask you to check all registered users in 9.24? Someone may write this:

Select * from user where registerdate = '2017-9-24 '; 

Unfortunately, this statement is incorrect because registerdate is of the timestamp type, while '2017-9-24 'is of the date type by default, and the type does not match. If the types do not match, you will naturally think of using functions for type conversion. Therefore, you will naturally write the following statement:

Select * from user where date (registerdate) = '2017-9-24 '; 

The preceding statement is completely correct, but if the registerdate field has an index, it will invalidate the index. This should not be done even if there is no index. So what should we do? The answer is to convert it into a range scan, as shown below:

Select * from user where registerdate> = '2014-9-24 00:00:00. 0' and registerdate <'2014-9-25 00:00:00. 0 '; 

--- For more information, see:DB2 SQL

----Statement: indicate the source for reprinting.

---- Last updated on 2009.9.24

---- Written by shangbo on 2009.9.24

---- End