ITPub博客

首页 > 应用开发 > IT综合 > Full Table Scan!

Full Table Scan!

原创 IT综合 作者:warehouse 时间:2008-05-03 22:06:53 0 删除 编辑
下面列出来的这些条件都会执行Full Table Scan,因此我们在使用时要特别注意![@more@]

RBO Path 15: Full Table Scan

This access path is available for any SQL statement, regardless of its WHERE clause conditions, except when its FROM clause contains SAMPLE or SAMPLE BLOCK.

Note that the full table scan is the lowest ranked access path on the list. This means that the RBO always chooses an access path that uses an index if one is available, even if a full table scan might execute faster.

The following conditions make index access paths unavailable:

  • column1 > column2
  • column1 < column2
  • column1 >= column2
  • column1 <= column2

where column1 and column2 are in the same table.

  • column IS NULL
  • column IS NOT NULL
  • column NOT IN
  • column != expr
  • column LIKE '%pattern'

regardless of whether column is indexed.

  • expr = expr2

where expr is an expression that operates on a column with an operator or function, regardless of whether the column is indexed.

  • NOT EXISTS subquery
  • ROWNUM pseudocolumn in a view
  • Any condition involving a column that is not indexed

Any SQL statement that contains only these constructs and no others that make index access paths available must use full table scans.

For example: The following statement uses a full table scan to access the emp table:

SELECT * 
    FROM emp; 

The EXPLAIN PLAN output for this statement might look like this:

OPERATION                 OPTIONS        OBJECT_NAME 
----------------------------------------------------- 
SELECT STATEMENT 
  TABLE ACCESS            FULL           EMP 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/19602/viewspace-1003275/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5115755