Performance testing: Several SQL statements that may cause full table scans

1. Low Efficiency of fuzzy search:

Cause: Like itself is relatively inefficient. You should try to avoid using like in query conditions; for like '%... % '(Full fuzzy) is a condition where indexes cannot be used. Full table scan is naturally inefficient.AlgorithmThe greater the length of the fuzzy query field, the lower the efficiency of fuzzy query.

Solution: Avoid fuzzy queries as much as possible. If fuzzy queries are required for services, do not use full fuzzy queries. For right fuzzy queries, that is, like '... % ', The index will be used; left fuzzy like

'%...' The index cannot be used directly, but the reverse + function index format can be used to change to like '... % '; Full fuzzy search cannot be optimized. You must use a search engine. To reduce the load on the database server, you can minimize the number of fuzzy queries.

2. Slow execution of select statements whose query conditions contain is null

Cause: in Oracle 9i, when the query field is null, a single index fails, causing a full table scan.

Solution: using NULL in SQL syntax can cause a lot of trouble. It is best that the index columns are not null. For is null, you can create a composite index, nvl (field, 0 ), after analyze is performed on tables and indexes, you can re-enable index search when querying is null, but the efficiency is not worth noting. If it is not null, indexes will never be used. Do not use is null to query tables with large data volumes.

3. the unequal operator (<> ,! =) Select statement execution is slow

Cause: in SQL, non-equal operators limit indexes, causing full table scanning, even if the field to be compared has an index

solution: You can use indexes to avoid full table scanning by changing the operator not equal to or. For example, if you change column <> 'aaa' to column <'aaa' or column> 'aaa', you can use the index.

6. if a composite index is used, if no leading Column exists in the query condition, the index does not work and will cause a full table scan. However, starting from Oracle9i, the index skip scan feature is introduced, the optimizer can use composite indexes, even if the index's leading column does not appear in the WHERE clause. Example: Create index skip1 on emp5 (job, empno); Full index scan select count (*) from emp5 where empno = 7900; index skip scan select/* + index (emp5 skip1) */count (*) from emp5 where empno = 7900; the former is full table scan, and the latter is composite index. 7. Improper use of the or statement may cause full table scan. The two conditions in the WHERE clause are compared, one with an index and the other without an index. Using the or clause will cause full table scan. For example, if where a =: 1 or B =: 2, A has an index, and B has no index, the full table scan starts again when B =: 2 is compared. 8. The composite index should be sorted according to the order of the columns in the composite index. Even if only one column in the index is to be sorted, the sorting performance will be poor. Example: Create index skip1 on emp5 (job, empno, date); select job, empno from emp5 where job = 'manager' and empno = '10' order by job, empno, date DESC; in fact, only records that meet the job = 'manager' and empno = '10' conditions are queried and sorted in descending order by date. However, the performance of the records written as order by date DESC is poor. 9. In the update statement, if only one or two fields are modified, do not update all fields. Otherwise, frequent calls may cause significant performance consumption and a large number of logs. 10. For tables with more than a large data size (hundreds of rows are larger), we need to perform the join operation by page. Otherwise, the logical read operation will be very high and the performance will be poor. 11. Select count (*) from table; this way, the count without any conditions will cause a full table scan without any business significance, and it must be eliminated. 12. the where condition of SQL must be bound to variables, such as where column =: 1. Do not write where column = 'aaa'. This will cause re-analysis during each execution, wasting CPU and memory resources.

this article from the csdn blog, reprinted please indicate the source: http://blog.csdn.net/linkyou/archive/2009/07/21/4367424.aspx