Oracle , full-table-scans (FTS) problem
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::
- Oracle text indexes to remove full-table scans when using the LIKE operator.
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-system2. 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 消耗. 所以這個現象可以拿來觀察資料庫系統的問題點.
