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 trace
SQL> 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 |
------------------------------------------------------------------------------
?
?
提示走索引，无效，因为employee_id有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 |
------------------------------------------------------------------------------
?
建立组合索引，或许把employee_id限制为非空：
?
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 |
------------------------------------------------------------------------------
?
?
索引跳跃扫描：
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 physic