Oracle 我可以在复合主键上创建索引吗？

你好，先生，

我有一个表管理器，它有4列。其中3列是复合主键。
创建表管理器 (ID整数，名称varchar(20)，名称varchar(20)，工资整数);

alter table manager添加约束t_pk主键 (ID，名称，指定);

所以，我的问题是
1) 是否会默认在复合主键上创建索引 (默认情况下)？
2) 如果需要在第一列ID上创建索引，可以在复合主键上创建单个索引吗？

提前谢谢你。

默认情况下，您将获得与主键中的列匹配的索引。

另一个好处是，对前导列的查询仍然可以从该索引中受益。

SQL> select     index_name,column_name
  2  from       user_ind_columns
  3  where      table_name = 'T'
  4  order by index_name,column_position;

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
SYS_C0060960                   OBJECT_NAME
SYS_C0060960                   OWNER
SYS_C0060960                   OBJECT_ID

3 rows selected.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where object_name = 'X' and owner = 'Y' and object_id = 123;

Execution Plan
----------------------------------------------------------
Plan hash value: 366573511

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |   132 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |   132 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0060960 |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='X' AND "OWNER"='Y' AND "OBJECT_ID"=123)

SQL>
SQL> select * from t where object_name = 'X' and owner = 'Y';

Execution Plan
----------------------------------------------------------
Plan hash value: 147527283

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |   132 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T            |     1 |   132 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_C0060960 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='X' AND "OWNER"='Y')

SQL>
SQL> select * from t where object_name = 'X';

Execution Plan
----------------------------------------------------------
Plan hash value: 147527283

----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |   132 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T            |     1 |   132 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | SYS_C0060960 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_NAME"='X')