复合索引的前导列如何选择(二)
QL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2. 如果是一个是等值，一个是范围查询，等值的字段作为前导列好一些。同样结合索引的存储特性，如果是object_id作为前导列，那oracle 会扫描30000,test到50584,***的数据，丢弃owner !=‘TEST’的列。
如果是owner作为前导列,那oracle 会扫描test,30000到test,50584的数据,没有丢弃。
SQL> select /*+index(test,ind_id_owner)*/* from test where owner ='TEST' and object_id >=30000;
已选择148行。
执行计划
----------------------------------------------------------
Plan hash value: 1096520809
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1076 | 97K| 90 (2)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1076 | 97K| 90 (2)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_ID_OWNER | 1076 | | 73 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=30000 AND "OWNER"='TEST' AND "OBJECT_ID" IS NOT NULL)
filter("OWNER"='TEST')
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
114 consistent gets
0 physical reads
0 redo size
17139 bytes sent via SQL*Net to client
484 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
148 rows processed
SQL> select /*+index(test,ind_owner_id)*/* from test where owner ='TEST' and object_id >=30000;
已选择148行。
执行计划
----------------------------------------------------------
Plan hash value: 724495818
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1076 | 97K| 36 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1076 | 97K| 36 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OWNER_ID | 1076 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='TEST' AND "OBJECT_ID">=30000 AND "OBJECT_ID" IS NOT NULL)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
50 consistent gets
0 physical reads
0 redo size
17139 bytes sent via SQL*Net to client
484 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
148 rows processed
3. 如果两个条件都是范围查询