Thanks for the question, Karishma.
Asked: February 26, 2018 - 5:10 pm UTC
Last updated: February 27, 2018 - 3:26 am UTC
Version: Oracle 11g
Viewed 10K+ times! This question is 
You Asked
Hello Sir,
I have one table Manager which has 4 columns.Out of which 3 column are composite primary key.
create table manager(ID integer, Name varchar(20), Designation varchar(20), Salary integer);
alter table manager add constraint t_pk primary key(ID, Name, Designation);
So, my question is
1) Will an index(by default which) be created by default on composite primary key?
2) and If I need to create an index on first column ID so, can I create the individual index on composite primary key?
Thank you in advance.
and Connor said...
By default, you'll get an index that matches the columns in the primary key.
The other benefit is that queries on the leading columns can still get benefit from that index as well.
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')