发表于: 2008.05.03 22:06
分类: 性能优化
出处: http://warehouse.itpub.net/post/777/461025
---------------------------------------------------------------
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
ISNULL - column
ISNOTNULL - column
NOTIN - 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.
NOTEXISTSsubqueryROWNUMpseudocolumn 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
