Useful Skip scan demo

View All Public Scripts Login to Run Script

Script Name Useful Skip scan demo
Visibility Unlisted - anyone with the share link can access
Description Small demo of skip scan doing less work than a fast full scan
Contributor Andrew Sayer
Created Wednesday August 18, 2021

Statement 1

create table as_table as select ao.* from all_objects ao where object_type in ('VIEW','SYNONYM','TABLE')

Table created.

Statement 2

create index as_skip_me on as_table (object_type, object_name)

Index created.

Statement 3

alter session set statistics_level=all

Statement processed.

Statement 4

select /* find_me:ss */ object_name 
from   as_table t 
where  t.object_name = 'DUAL'


OBJECT_NAME
DUAL
DUAL

2 rows selected.

OBJECT_NAME
DUAL
DUAL

Statement 5

select /*+ index_ffs(t) */ /* find_me:ffs */ object_name 
from   as_table t 
where  t.object_name = 'DUAL'


OBJECT_NAME
DUAL
DUAL

2 rows selected.

OBJECT_NAME
DUAL
DUAL

Statement 6

select t.*  
from v$sql s 
cross apply dbms_xplan.display_cursor(s.sql_id, s.child_number,format=>'allstats last typical') t 
where s.sql_text not like '%v$sql%' 
and (s.sql_text like '%find_me:ss%' 
or   s.sql_text like '%find_me:ffs%' 
    )


PLAN_TABLE_OUTPUT
SQL_ID  8bhnubfzcfnhf, child number 0
-------------------------------------
select /*+ index_ffs(t) */ /* find_me:ffs */ object_name  from   
as_table t  where  t.object_name = 'DUAL'
 
Plan hash value: 3463575299
 
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |      1 |        |       |    28 (100)|          |      2 |00:00:00.01 |      91 |
|*  1 |  INDEX FAST FULL SCAN| AS_SKIP_ME |      1 |      1 |    28 |    28   (0)| 00:00:01 |      2 |00:00:00.01 |      91 |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."OBJECT_NAME"='DUAL')
 
SQL_ID  4x5xamx7ubbpc, child number 0
-------------------------------------
select /* find_me:ss */ object_name  from   as_table t  where  
t.object_name = 'DUAL'
 
Plan hash value: 4212152606
 
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |      1 |        |       |     4 (100)|          |      2 |00:00:00.01 |      10 |
|*  1 |  INDEX SKIP SCAN | AS_SKIP_ME |      1 |      1 |    28 |     4   (0)| 00:00:01 |      2 |00:00:00.01 |      10 |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("T"."OBJECT_NAME"='DUAL')
       filter("T"."OBJECT_NAME"='DUAL')
 

39 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 8bhnubfzcfnhf, child number 0
-------------------------------------
select /+ index_ffs(t) / /* find_me:ffs */ object_name from
as_table t where t.object_name = 'DUAL'

Plan hash value: 3463575299

-----------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \|E-Bytes\| Cost (%CPU)\| E-Time \| A-Rows \| A-Time \| Buffers \|
-----------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| \| 28 (100)\| \| 2 \|00:00:00.01 \| 91 \|
\|* 1 \| INDEX FAST FULL SCAN\| AS_SKIP_ME \| 1 \| 1 \| 28 \| 28 (0)\| 00:00:01 \| 2 \|00:00:00.01 \| 91 \|
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T"."OBJECT_NAME"='DUAL')

SQL_ID 4x5xamx7ubbpc, child number 0
-------------------------------------
select /* find_me:ss */ object_name from as_table t where
t.object_name = 'DUAL'

Plan hash value: 4212152606

-------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \|E-Bytes\| Cost (%CPU)\| E-Time \| A-Rows \| A-Time \| Buffers \|
-------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| \| 4 (100)\| \| 2 \|00:00:00.01 \| 10 \|
\|* 1 \| INDEX SKIP SCAN \| AS_SKIP_ME \| 1 \| 1 \| 28 \| 4 (0)\| 00:00:01 \| 2 \|00:00:00.01 \| 10 \|
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."OBJECT_NAME"='DUAL')
filter("T"."OBJECT_NAME"='DUAL')

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database