Oracle SQL developer - Indexes and Execution plans

Question

I am currently analyzing my queries in oracle sql developer via the "Explain plan" function. I did not define any index for my data, yet, the execution plan indicates that I am. So I looked in the indexes, and there are some indexes which seem to be created by default. But what kind of indexes are those? I tried accessing the "Details" tab of those indexes but they are not really helpful. All I could understand was that their actually is 1 index for each table I created.

Are there any constraints on your table? If so, maybe these indexes have been created when you defined the constraints — Aleksej
I did specify primary keys as well as foreign keys on each table. So are you saying these indexes are based on a certain order of my primary keys? — Loïs Talagrand

Aleksej Aleksej · Accepted Answer · 2016-05-23T08:03:21

Probably you did not explicitly define any index, but Oracle implicitly did it for your when you created constraints; for example:

SQL> create table master_table(id_master number, description_master varchar2(100));

Table created.

SQL> select count(1)
  2  from user_indexes
  3  where table_name = 'MASTER_TABLE';

  COUNT(1)
----------
         0

SQL> alter table master_table add constraint master_pk primary key (id_master);

Table altered.

SQL> alter table master_table add constraint detail_uk unique (description_master);

Table altered.

SQL> select count(1)
  2  from user_indexes
  3  where table_name = 'MASTER_TABLE';

  COUNT(1)
----------
         2

About the order of the columns:

SQL> create table DOUBLE_PK_TABLE(id1 number, id2 number);

Table created.

SQL> alter table double_pk_table add constraint double_pk primary key (id1, id2);

Table altered.

SQL> select index_name, column_name, column_position
  2  from user_indexes
  3       inner join user_ind_columns col
  4         using(index_name, table_name)
  5  where table_name ='DOUBLE_PK_TABLE';

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
DOUBLE_PK                      ID1                                          1
DOUBLE_PK                      ID2                                          2

Oracle SQL developer - Indexes and Execution plans

1 Answers