复合索引的前导列如何选择(三)
，则前导列为，单个字段过滤后的数据量接近两个字段过滤后的数据量。以下面的列子来说，最终结果是830条数据，用 owner >='TEST'为1076条，用object_id >= 30000是21029条，这样会减少filter的操作，所以选择owner作为前导列。
SQL> select /*+index(test,ind_id_owner)*/*from test where owner >='TEST' and object_id >= 30000;
已选择830行。
执行计划
----------------------------------------------------------
Plan hash value: 1096520809
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5377 | 488K| 156 (1)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 5377 | 488K| 156 (1)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_ID_OWNER | 5377 | | 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
228 consistent gets
0 physical reads
0 redo size
86578 bytes sent via SQL*Net to client
990 bytes received via SQL*Net from client
57 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
830 rows processed
SQL> select /*+index(test,ind_owner_id)*/*from test where owner >='TEST' and object_id >
= 30000;
已选择830行。
执行计划
----------------------------------------------------------
Plan hash value: 724495818
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5377 | 488K| 188 (1)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 5377 | 488K| 188 (1)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | IND_OWNER_ID | 5377 | | 36 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER">='TEST' AND "OBJECT_ID">=30000 AND "OWNER" IS NOT NULL)
filter("OBJECT_ID">=30000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
164 consistent gets
0 physical reads
0 redo size
86578 bytes sent via SQL*Net to client
990 bytes received via SQL*Net from client
57 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
830 rows processed
SQL> set autotrace off
SQL> select count(*) from test where owner >='TEST';
COUNT(*)
----------
1076
SQL> select count(*) from test where object_id >= 30000;
COUNT(*)
----------
21029