SQL> create table test as select * from dba_objects;
Table created.
select * from test where object_id=10 and OWNER='SYS';
SQL> select count(*) from test where object_id=10;
COUNT(*)
----------
1
SQL> select count(*) from test;
COUNT(*)
----------
74443
模拟插入object_id=10 and OWNER='SYS'的记录
SQL> select count(*) from test where object_id=10 and owner='SYS';
COUNT(*)
----------
4194304
create table test4 as (select * from (select * from test where object_id=10 and owner='SYS') where rownum<100001);
SQL> select count(*) from test4;
COUNT(*)
----------
100000
update test4 set owner='SCOTT';
SQL> insert into test select * from test4;
100000 rows created.
SQL> commit;
Commit complete.
SQL> select object_id,owner,count(*) from test group by object_id,owner
2 having object_id=10;
OBJECT_ID OWNER COUNT(*)
---------- ------------------------------ ----------
10 SYS 4194304
10 SCOTT 100000
SQL> create index test_idx1 on test(object_id);
Index created.
SQL> create index test_idx2 on test(object_id,owner);
Index created.
对比单列索引和组合索引效率:
SQL> select /*+index(test test_idx1)*/ * from test where object_id=10 and owner='SYS';
4194304 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2624864549
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4412K| 871M| 202K (1)| 00:40:30 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 4412K| 871M| 202K (1)| 00:40:30 |
|* 2 | INDEX RANGE SCAN | TEST_IDX1 | 4412K| | 8701 (1)| 00:01:45 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SYS')
2 - access("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
616418 consistent gets
56597 physical reads
0 redo size
60679059 bytes sent via SQL*Net to client
3076239 bytes received via SQL*Net from client
279622 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4194304 rows processed
此时table access by index rowid 能过滤掉的数据为 100000 行
对比走组合索引的效率:
SQL> select /*+index(test test_idx2)*/ * from test where object_id=10 and owner='SYS';
4194304 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2533302371
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4412K| 871M| 205K (1)| 00:41:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 4412K| 871M| 205K (1)| 00:41:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX2 | 4412K| | 11214 (1)| 00:02:15 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10 AND "OWNER"='SYS')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
617237 consistent gets
58752 physical reads
0 redo size
384479179 bytes sent via SQL*Net to client
3076239 bytes received via SQL*Net from client
279622 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4194304 rows processed
对比逻辑读:
走TEST_IDX1 索引:
616418 consistent gets
56597 physical reads
走TEST_IDX2 索引:
617237 consistent gets
58752 physical reads
继续测试:
SQL> select /*+index(test test_idx1)*/ * from test where object_id=10 and owner='SCOTT';
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2624864549
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 452 | 93564 | 202K (1)| 00:40:30 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TEST | 452 | 93564 | 202K (1)| 00:40:30 |
|* 2 | INDEX RANGE SCAN | TEST_IDX1 | 4412K| | 8701 (1)| 00:01:45 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SCOTT')
2 - access("OBJECT_ID"=10)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
71101 consistent gets
56599 physical reads
0 redo size
1448081 bytes sent via SQL*Net to client
73745 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
select /*+index(test test_idx2)*/ * from test where object_id=10 and owner='SCOTT';SQL>
100000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2533302371
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 452 | 93564 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 452 | 93564 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX2 | 72 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=10 AND "OWNER"='SCOTT')
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
14774 consistent gets
0 physical reads
0 redo size
9367927 bytes sent via SQL*Net to client
73745 bytes received via SQL*Net from client
6668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
此时TABLE ACCESS BY INDEX ROWID过滤了4194304条记录,走组合索引效率更高:
对比逻辑读:
走TEST_IDX1 索引:
71101 consistent gets
56599 physical reads
走TEST_IDX2 索引:
14774 consistent gets
0 physical reads
结论:如果TABLE ACCESS BY INDEX ROWID不能过滤大量数据,那么走组合索引效率未必高。