SQL Statement Performance Tuning

I. Problems with indexing (index)

1. Indexes (index), using or not? It's a problem.

Whether a full table scan or an index range scan mainly considers the SQL query speed problem. The main concern here is the number of records to read. According to Donald K. Burleson, the principle of using index range scanning is:

For tables with the original sort of data, queries that read less than 40% of the number of table records should use an index range scan. Queries that read 40% more than the number of records should be scanned all over the table.

For unordered tables, queries that read less than 7% of the number of table records should use an index range scan, whereas queries that read more than 7% of the table records should be scanned all over the table.

Note: In different books, the percentage value of the read record that uses the index is not consistent, which is basically an experience value, but the lower the percentage of the read record, the more efficient the use of the index.

2. What SQL queries are useful indexes (index) If there is an index on the column? What SQL query is not indexed (index)?

An index is not used for SQL that has the following condition:

There is a data type for stealth conversions, such as:

SELECT * from Staff_member where staff_id= ' 123 ';

Having mathematical operations on a column, such as:

SELECT * FROM Staff_member where salary*2<10000;

Using a not equal to (<>) operation, such as:

SELECT * FROM Staff_member where dept_no<>2001;

Using a substr string function, such as:

SELECT * from Staff_member where substr (last_name,1,4) = ' FRED ';

'% ' wildcard characters in the first character, such as:

SELECT * from Staff_member where first_name like '%don ';

string concatenation (| |) , such as:

SELECT * from Staff_member where first_name| | ' = ' DONALD '

3. Index of functions

Date types are also easy to use, and the To_char function is used in SQL statements to query for specific range dates. For example: SELECT * from Staff_member where To_char (Birth_day, ' YYYY ') = ' 2003 '; We can establish a function based index such as: Create INDEX Ind_emp_birth on Staff_member (To_char (birth_day, ' YYYY '));