Search notes:

Oracle SQL Plan operation INDEX SKIP SCAN

create table tq84_skip_scan (
   col_idx_1    number(1)    not null check (col_idx_1 in (0,1)),
   col_idx_2    number       not null,
   col_idx_3    varchar2(10) not null,
   col_val_1    number       not null,
   col_val_2    varchar2(10) not null
);
TODO: I used dbms_random.seed using the assumption that the following insert … into statement would always create the same values.
This assumption turned out to be wrong.
begin
   dbms_random.seed(42);
end;
/

insert into tq84_skip_scan
select
   trunc(dbms_random.value * 2),
   round(dbms_random.value(0, 1000), 1),
   dbms_random.string('a', 10),
   round(dbms_random.value(0, 1000), 1),
   dbms_random.string('x', 10)
from
   dual connect by level <= 100 * 10000;
create index tq84_skip_scan_ix
   on tq84_skip_scan
(
   col_idx_1,
   col_idx_2,
   col_idx_3
);
Ensure up-to-date optimizer statistics:
begin
   dbms_stats.gather_table_stats(user, 'TQ84_SKIP_SCAN');
end;
/
Use the index to locate the row. The index can be used although the index's leading column is not specified in the where clause:
explain plan for
   select * from tq84_skip_scan
   where
      col_idx_2 =  341.9 and
      col_idx_3 = 'oFnEjjbqpT'
   ;

select * from table(dbms_xplan.display(format=>'basic +predicate'));
--
-- -----------------------------------------------------------------
-- | Id  | Operation                           | Name              |
-- -----------------------------------------------------------------
-- |   0 | SELECT STATEMENT                    |                   |
-- |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TQ84_SKIP_SCAN    |
-- |*  2 |   INDEX SKIP SCAN                   | TQ84_SKIP_SCAN_IX |
-- -----------------------------------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    2 - access("COL_IDX_2"=341.9 AND "COL_IDX_3"='oFnEjjbqpT')
--        filter("COL_IDX_3"='oFnEjjbqpT' AND "COL_IDX_2"=341.9)

See also

Plan operations

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1758199275, '216.73.216.150', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL/statement/execution/plan/operations/index_/skip-scan/index(102): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78