参见 Oracle性能优化求生指南。
建虚拟索引
HR@ prod> create index test_ix1 on employees4 ( employee_id ) nosegment;
HR@ prod> alter session set "_use_nosegment_indexes" = true ;
HR@ prod> set autotrace on
HR@ prod> select /*oooooooooooo*/ * from employees4 where employee_id = 100 ;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE
----------- -------------------- ------------------------- ------------------------- -------------------- ---------
JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 17-JUN-03
AD_PRES 24000 90
Execution Plan
----------------------------------------------------------
Plan hash value: 499133838
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 133 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES4 | 1 | 133 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IX1 | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1299 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
查看真正的执行计划,需要知道SQL_ID和CHILD_NUBMER。
HR@ prod> conn / as sysdba
Connected.
SYS@ prod> select sql_id , child_number from v$sql where sql_text like 'select /*oooooooooooo*/%' ;
SQL_ID CHILD_NUMBER
------------- ------------
gtx1pw85bgqz0 0
SYS@ prod> select * from table(dbms_xplan.display_cursor('gtx1pw85bgqz0' , 0 )) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID gtx1pw85bgqz0, child number 0
-------------------------------------
select /*oooooooooooo*/ * from employees4 where employee_id = 100
Plan hash value: 2568047056
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMPLOYEES4 | 1 | 133 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPLOYEE_ID"=100)
Note
-----
- dynamic sampling used for this statement (level=2)
22 rows selected.
实际中并没有索引可以用,Oracle走的是全表扫描。