[One Moss per day]-Index Skip Scan Feature (212391.1)

[One Moss per day]-Index Skip Scan Feature (212391.1) INDEX Skip Scan, that is, Index quick Scan, usually refers to the first column without composite INDEX in the predicate, however, scanning INDEX blocks is faster than scanning table data blocks. In this case, CBO selects the index ss method. Officially speaking, this concept is easy to understand. If you think of composite indexes as a partition table, the partition primary key (the first column of composite indexes here) defines the partition data stored here. Data in each row under each key (first column) is sorted by this key. Therefore, in the SS, the first column can be skipped, and non-first columns can be accessed as logical sub-indexes. Therefore, the first column can be ignored for a "normal" index access. The composite index is logically divided into smaller sub-indexes. The number of logical sub-indexes depends on the cardinality of the initial column. Therefore, although the first column is not included in the predicate, this index may be used. In addition, you need to add: When the value repetition rate of the first field of the composite index is very low, the efficiency of scanning the index will be higher than that of full table scanning, this is why CBO may choose to use INDEX Skip Scan to access data. The strange thing here is that INDEX Skip Scan is not used when 9i is used:

SQL> create table at2 (a varchar2 (3), B varchar2 (10), c varchar2 (5); Table created. SQL> begin 2 for I in 1 .. 1000 3 loop 4 insert into at2 values ('M', I, 'M'); 5 insert into at2 values ('F', I, 'F '); 6 end loop; 7 end; 8/PL/SQL procedure successfully completed. SQL> create index at2_ I on at2 (a, B, c); Index created. SQL> exec dbms_stats.gather_table_stats (OWNNAME => NULL, TABNAME => 'at2', CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1 '); PL/SQL procedure successfully completed. SQL> set autotrace traceonlySQL> select * from at2 where B = '000000'; Execution Plan limit 0 SELECT STATEMENT Optimizer = CHOOSE (Cost = 2 Card = 2 Bytes = 14) 1 0 INDEX (fast full scan) OF 'at2 _ I '(NON-UNIQUE) (Cost = 2 Car d = 2 Bytes = 14) statistics defaults 0 recursive cballs 0 db block gets 10 consistent gets 0 physical reads 0 redo size 447 bytes sent via SQL * Net to client 587 bytes received via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 2 rows processed uses 10 Gb, then INDEX Skip Scan: SQL> select * from full_tbl where object_name = 'test'; Execution Plan into Plan hash value: 1293869270 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 2 | 58 | 55 (2) | 00:00:01 | * 1 | table access full | FULL_TBL | 2 | 58 | 55 (2) | 00:00:01 | identified Predicate Information (identified by operation id ): ------------------------------------------------- 1-filter ("OBJECT_NAME" = 'test ') statistics limit 1 recursive cballs 0 db block gets 230 consistent gets 0 physical reads 0 redo size 585 bytes sent via SQL * Net to client 492 bytes encoded ed via SQL * Net from client 2 SQL * Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed