Neil的备忘录

just do it
posts - 66, comments - 8, trackbacks - 0, articles - 0

Index Skip Scanning

Posted on 2009-01-21 15:22 Neil's NoteBook 阅读(209) 评论(0)  编辑  收藏 所属分类: ORACLE
In previous releases a composite index could only be used if the first column, the leading edge, of the index was referenced in the WHERE clause of a statement. In Oracle9i this restriction is removed because the optimizer can perform skip scans to retrieve rowids for values that do not use the prefix.

How It Works

Rather than restricting the search path using a predicate from the statement, Skip Scans are initiated by probing the index for distinct values of the prefix column. Each of these distinct values is then used as a starting point for a regular index search. The result is several separate searches of a single index that, when combined, eliminate the affect of the prefix column. Essentially, the index has been searched from the second level down.

The optimizer uses statistics to decide if a skip scan would be more efficient than a full table scan.

Advantages

This approach is advantageous because:
  • It reduces the number of indexes needed to support a range of queries. This increases performance by reducing index maintenance and decreases wasted space associated with multiple indexes.
  • The prefix column should be the most discriminating and the most widely used in queries. These two conditions do not always go hand in hand which makes the decision difficult. In these situations skip scanning reduces the impact of makeing the "wrong" decision.

Example

First, create and populate a test table with a concatenated index.
CREATE TABLE test_objects AS
SELECT * FROM all_objects;

CREATE INDEX test_objects_i ON test_objects (owner, object_name, subobject_name);

EXEC DBMS_STATS.gather_table_stats(USER, 'TEST_OBJECTS', cascade => TRUE);
Next, run a query that hits the leading edge of the index. Notice the range scan on the index.
SQL> SET AUTOTRACE ON
SQL> SELECT owner, object_name
2 FROM test_objects
3 WHERE owner = 'SYS'
4 AND object_name = 'DBMS_OUTPUT';

OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS DBMS_OUTPUT

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3650344004

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_OBJECTS_I | 1 | 32 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Next, run a query that does not hit the leading edge of the index. Notice the index skip scan on the index.
SQL> SET AUTOTRACE ON
SQL> SELECT owner, object_name
2 FROM test_objects
3 WHERE object_name = 'DBMS_OUTPUT';

OWNER OBJECT_NAME
------------------------------ ------------------------------
PUBLIC DBMS_OUTPUT
SYS DBMS_OUTPUT

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1293870291

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 64 | 14 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | TEST_OBJECTS_I | 2 | 64 | 14 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Finally, clean up the test table.
DROP TABLE test_objects;
For more information see:
Hope this helps. Regards Tim...

原文地址: http://oracle-base.com/articles/9i/IndexSkipScanning.php#HowItWorks

只有注册用户登录后才能发表评论。


网站导航: