tes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们这里看到等值查询结果是不一样的，证实了《品悟》一书的可选性是正确的。
SQL> select /*+ index(t,ind_t_obj_ty) */ * from T where object_id > 20 and object_id < 2000 and object_type='TABLE';
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 49 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 49 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_TY | 912 | | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE' AND "OBJECT_ID">20 AND "OBJECT_ID"<2000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
SQL> select /*+ index(t,ind_t_obj_id) */ * from T where object_id > 20 and object_id < 2000 and object_type='TABLE';
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 607336433
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_ID | 9 | | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">20 AND "OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<2000)
filter("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
87 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
结果，不言而喻。以上两个sql在不用hint的时候，CBO都会自动选择走IND_T_OBJ_ID。所以，《收获不止oracle》一书的结论有待商榷（如果不是我测试有问题的话）。
|