Execution Plan related to B-tree indexes

Execution Plan related to B-tree indexes
Unique index scan, index range scan, full index scan, quick full index scan, and skip index scan. Unique index scan:

SQL> create table employee(gender varchar2(1),employee_id number);Table created.SQL> insert into employee values('F',99);1 row created.SQL> insert into employee values('F',100);1 row created.SQL> insert into employee values('M',101);1 row created. SQL> insert into employee values('M',102);1 row created.SQL> insert into employee values('M',103);1 row created.SQL> insert into employee values('M',104);1 row created.SQL> insert into employee values('M',105);1 row created.SQL> insert into employee values('F',106);1 row created.SQL> commit;Commit complete.SQL> create unique index idx_unqi_emp on employee(employee_id);Index created.SQL> select * from employee where employee_id=100;G EMPLOYEE_ID- -----------F  100SQL> set lines 200 pagesize 1000SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'ALL'));PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------SQL_IDbum8qv24s6tqp, child number 0-------------------------------------select * from employee where employee_id=100Plan hash value: 1037614268--------------------------------------------------------------------------------------------| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |   |   |   | 1 (100)|   ||   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE   | 1 |15 | 1   (0)| 00:00:01 ||*  2 |   INDEX UNIQUE SCAN    | IDX_UNQI_EMP | 1 |   | 0   (0)|   |---------------------------------------------------------------------------------31 rows selected.

 

Index range scan:

SQL> drop index idx_unqi_emp;Index dropped.SQL> create index idx_unqi_emp on employee(employee_id);Index created.SQL> select * from employee where employee_id=100;G EMPLOYEE_ID- -----------F  100SQL> select plan_table_output from table(dbms_xplan.display_cursor(null,null,'ALL'));PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------SQL_IDbum8qv24s6tqp, child number 0select * from employee where employee_id=100Plan hash value: 407794244--------------------------------------------------------------------------------------------| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time   |--------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |   |   |   | 2 (100)|   ||   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE   | 1 |15 | 2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN    | IDX_UNQI_EMP | 1 |   | 1   (0)| 00:00:01 |--------------------------------------------------------------------------------------------

 

Quick full index scan:

SQL> begin  2  for i in 1..5000 loop  3  insert into employee values('F',i);  4  end loop;  5  commit;  6  end;  7  /PL/SQL procedure successfully completed.SQL> begin  2  for i in 5001..10000 loop  3  insert into employee values('M',i);  4  end loop;  5  commit;  6  end;  7  /PL/SQL procedure successfully completed.SQL> select gender,count(*) from employee group by gender;G   COUNT(*)- ----------M5000F5000BEGIN  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',                                tabname          => 'EMPLOYEE',                                estimate_percent => 100,                                method_opt       => 'for all columns size repeat',                                no_invalidate    => FALSE,                                degree           => 8,                                granularity      => 'ALL',                                cascade          => TRUE); END;PL/SQL procedure successfully completed.SQL> set autot traceSQL> select employee_id from employee;10000 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2119105728------------------------------------------------------------------------------| Id  | Operation  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |     | 10000 | 40000 |   7   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 |   7   (0)| 00:00:01 |------------------------------------------------------------------------------

 

An index is prompted. invalid because the value of employee_id is null:

SQL> create index idx_emp_1 on employee(employee_id);Index created.SQL> select /*+ index(employee idx_emp_1) */ employee_id from employee;10000 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2119105728------------------------------------------------------------------------------| Id  | Operation  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |     | 10000 | 40000 |   7   (0)| 00:00:01 ||   1 |  TABLE ACCESS FULL| EMPLOYEE | 10000 | 40000 |   7   (0)| 00:00:01 |------------------------------------------------------------------------------

 

To create a composite index, you may limit the value of employee_id to a non-empty one:

SQL> select /*+ index(employee idx_emp_1) */ employee_id from employee;10000 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 438557521------------------------------------------------------------------------------| Id  | Operation | Name      | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT |     | 10000 | 40000 |  25   (0)| 00:00:01 ||   1 |  INDEX FULL SCAN | IDX_EMP_1 | 10000 | 40000 |  25   (0)| 00:00:01 |------------------------------------------------------------------------------

 

Index skip scan:

SQL> create index idx_emp_1 on employee(gender,employee_id);Index created.SQL> select * from employee where employee_id=109;Execution Plan----------------------------------------------------------Plan hash value: 2039022311------------------------------------------------------------------------------| Id  | Operation  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------|   0 | SELECT STATEMENT |      |    1 |    6 |    3   (0)| 00:00:01 ||*  1 |  INDEX SKIP SCAN | IDX_EMP_1 |    1 |    6 |    3   (0)| 00:00:01 |------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("EMPLOYEE_ID"=109)       filter("EMPLOYEE_ID"=109)Statistics----------------------------------------------------------   1  recursive calls   0  db block gets   6  consistent gets   3  physical reads   0  redo size 600  bytes sent via SQL*Net to client 524  bytes received via SQL*Net from client   2  SQL*Net roundtrips to/from client   0  sorts (memory)   0  sorts (disk)   1  rows processed