Execution plan related to B-tree index

Index unique scan, index range scan, index full scan, index fast full scan and index skip scan.

Index unique scan:

SQL > create table employee (gender varchar2 (1), employee_id number); Table created.SQL > insert into employee values ('Fleming page99); 1 row created.SQL > insert into employee values; 1 row created.SQL > insert into employee values; 1 row created. SQL > insert into employee values; 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: 1037614268Murray- -| 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 |- -

Index Quick full scan:

SQL > begin 2 for i in 1.. 5000 loop 3 insert into employee values; 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 ('Manger Magna 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 Murray- -| 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 |- -

Prompt to go to the index, invalid, because employee_id has a null value:

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 Murray- -| 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 |- -

Create a composite index, or restrict employee_id to non-null:

SQL > select / * + index (employee idx_emp_1) * / employee_id from employee 10000 rows selected.Execution Plan---Plan hash value: 438557521 Murray- -| 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 jump 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 Murray- -| 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