1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

An example of Index Skip Scan

Discussion in 'Server Administration and Options' started by Arju, Oct 27, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Many one think about index skip scan as wrong one and mistakenly think it as index is not used. But in fact skip scan is efficient enough when leading indexed column contain distinct values. With the following example I have shown when optimizer uses index skip scan.

    1)Create emp table.
    Code (Text):
    SQL> create table emp(name varchar2(30), age number(2),emp_id number,gender varchar2(1));

    Table created.
    2)Enter some random data.
    Code (Text):
    SQL> begin
    for i in 1 .. 20000 loop insert into emp values(dbms_random.string('U',10),
    to_number(substr(abs(dbms_random.random),1,2)),
    i,
    decode(mod(abs(dbms_random.random),2),0,'M','F'));
    end loop;
    end;
    /
      2    3    4    5    6    7    8

    PL/SQL procedure successfully completed.
    3)See some data to look how they are distributed.
    Code (Text):
    SQL> select * from emp where rownum<=10;

    NAME                                  AGE     EMP_ID G
    ------------------------------ ---------- ---------- -
    ONODUUBNEM                             14        875 M
    BSRZFVUZLD                             19        876 F
    AIFATIZUJD                             79        877 F
    XFITMYIRPY                             15        878 M
    KJESGKMOVT                             13        879 M
    CBIOQFKZYZ                             10        880 M
    UCRBBVMLRJ                             10        881 F
    SMJFXWGAHQ                             77        882 F
    ETQOXQGFMV                             12        883 M
    FYZNDYNVNR                             11        884 F

    10 rows selected.
    4)Create an index on column gender and age table. Note that here order of column gender is ahead of age.

    Code (Text):
    SQL> create index emp_index on emp(gender,age);

    Index created.
    5)Estimate statistics.
    Code (Text):
    SQL> analyze table emp estimate statistics;

    Table analyzed.
     
    6)see the execution plan
    Code (Text):
    SQL> set autot trace
    SQL> select age from emp where age=1 ;

    no rows selected


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 505787091

    ------------------------------------------------------------------------------
    | Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |           |   200 |   400 |     3   (0)| 00:00:01 |
    |*  1 |  INDEX SKIP SCAN | EMP_INDEX |   200 |   400 |     3   (0)| 00:00:01 |
    ------------------------------------------------------------------------------

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

       1 - access("AGE"=1)
           filter("AGE"=1)


    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            320  bytes sent via SQL*Net to client
            458  bytes received via SQL*Net from client
              1  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              0  rows processed
     
    Now see index skip scan is used.