ORACLE中的索引与组合索引比对

71 阅读 0 评论 47 点赞

我是靠谱客的博主会撒娇画板，最近开发中收集的这篇文章主要介绍ORACLE中的索引与组合索引比对，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

同一张表上的两个字段，都比较经常查询，用组合索引好呢？还是分开索引好呢？

看下面一个例子：
create table TB1
(
OWNER VARCHAR2(30) not null,
OBJECT_NAME VARCHAR2(30) not null,
SUBOBJECT_NAME VARCHAR2(30),
OBJECT_ID NUMBER not null,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2(19),
CREATED DATE not null,
LAST_DDL_TIME DATE not null,
TIMESTAMP VARCHAR2(19),
STATUS VARCHAR2(7),
TEMPORARY VARCHAR2(1),
GENERATED VARCHAR2(1),
SECONDARY VARCHAR2(1)
)
===========================
列数 object_type
1 20020 SYNONYM
2 16359 JAVA CLASS

先在表上建组合索引INX_TB1_OTYPE_ONAME;

SQL> select * from tb1 where object_type='SYNONYM';

已选择20020行。

执行计划
----------------------------------------------------------
Plan hash value: 3226679318

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1720 | 146K| 149 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TB1 | 1720 | 146K| 149 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_TYPE"='SYNONYM')

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1957 consistent gets
0 physical reads
0 redo size
942680 bytes sent via SQL*Net to client
15059 bytes received via SQL*Net from client
1336 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20020 rows processed

可以发现不走索引，全表扫描。

SQL> select * from tb1 where object_type='SYNONYM' and object_name='DEPT';

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 480521530

--------------------------------------------------------------------------------

-------------------

| Id | Operation | Name | Rows | Bytes | Cost

(%CPU)| Time |

--------------------------------------------------------------------------------

-------------------

| 0 | SELECT STATEMENT | | 1 | 87 |
2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TB1 | 1 | 87 |
2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | INX_TB1_OTYPE_ONAME | 1 | |
1 (0)| 00:00:01 |

--------------------------------------------------------------------------------

-------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_TYPE"='SYNONYM' AND "OBJECT_NAME"='DEPT')

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
992 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

走索引了。

分别两个字段建索引
SQL> select * from tb1 where object_type='SYNONYM';

已选择20020行。

执行计划
----------------------------------------------------------
Plan hash value: 419929180

--------------------------------------------------------------------------------

-------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU

)| Time |

--------------------------------------------------------------------------------

-------------

| 0 | SELECT STATEMENT | | 1720 | 146K| 64 (0

)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TB1 | 1720 | 146K| 64 (0

)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | INX_TB1_OTYPE | 1720 | | 5 (0

)| 00:00:01 |

--------------------------------------------------------------------------------

-------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_TYPE"='SYNONYM')

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3240 consistent gets
0 physical reads
0 redo size
2184300 bytes sent via SQL*Net to client
15059 bytes received via SQL*Net from client
1336 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20020 rows processed

单走索引，但逻辑读很高。

SQL> select * from tb1 where object_type='SYNONYM' and object_name='DEPT';

未选定行

执行计划
----------------------------------------------------------
Plan hash value: 447681844

-------------------------------------------------------------------------------

-------------

| Id | Operation | Name | Rows | Bytes | Cost (%CP

)| Time |

-------------------------------------------------------------------------------

-------------

| 0 | SELECT STATEMENT | | 1 | 87 | 2 (

)| 00:00:01 |

|* 1 | TABLE ACCESS BY INDEX ROWID| TB1 | 1 | 87 | 2 (

)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | INX_TB1_ONAME | 2 | | 1 (

)| 00:00:01 |

-------------------------------------------------------------------------------

-------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_TYPE"='SYNONYM')
2 - access("OBJECT_NAME"='DEPT')

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
992 bytes sent via SQL*Net to client
374 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
走INX_TB1_ONAME 索引逻辑读略高

SQL> select /*+NO_INDEX(tb1 INX_TB1_OTYPE)*/* from tb1 where object_type='SYNONY
M';

已选择20020行。

执行计划
----------------------------------------------------------
Plan hash value: 3226679318

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1720 | 146K| 149 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| TB1 | 1720 | 146K| 149 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_TYPE"='SYNONYM')

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1957 consistent gets
0 physical reads
0 redo size
942680 bytes sent via SQL*Net to client
15059 bytes received via SQL*Net from client
1336 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20020 rows processed

不走索引，效率更高。