Oracle , full-table-scans (FTS) problem

by

http://www.dba-oracle.com/t_sql_like_clause_index_usage.htm

Indexing when using the SQL "like" clause can be tricky because the wildcard "%" operator can invalidate the index.  For example a last_name index would be OK with a "like ‘SMI%’" query, but unusable with "like ‘%SMI%’.

Solutions to this issue of a leading wildcard can be addressed in several ways::

Burleson Consulting 說:

These unnecessary full-table scans are a problem:
1. Large-table full-table scans increase the load on the disk I/O sub-system

2. Small table full table scans (in the data buffer) cause high consistent gets and drive-up CPU consumption

非必要的 full-table-scans 造成幾個問題 : 大的資料表會增加 disk I/O , 小的資料表則是增加 CPU 消耗. 所以這個現象可以拿來觀察資料庫系統的問題點.

Tuning Oracle Full-table Scans

http://www.orafaq.com/node/39