Search code examples
oracle-databaseindexingoracle12c

Oracle SQL: Single Index with two Columns vs index on one Column

I'm using Oracle12c

I have a table with a primary key and separate column.

create tableB(
   ID number(10)
   ,data number(10)
);

ID is my primary key.

I have to join 3 tables on my query and the performance issue is the B.data without an index. B.data contains 'null' values and multiple entries on the same numbers.

select A.examp from tabled D
   join tableb B on D.data = B.data
   join tablec C on B.ID = C.ID
   join tablea A on C.val = A.val 
   where D.ID = :value;

So my question is what is the difference between an index that contains only one value like the data column

create index ind_tableb on tableb (data);

and an index that contains multiple columns like

create index ind_tableb on tableb (data, id);

Can i get an improvement by selecting the id in the index with the data in the way i join the columns ?

Thanks for any advise and help.

Solution

  • For this particular query, you want the two column index version:

    create index ind_tableb on tableb (data, id);

    The above index, if used, would let Oracle rapidly lookup tabled.data values for a potential match with a tableb.data value. If a match be found, then the same index would also contain the tableb.ID value for the next join to tablec. If you just used the single column version on tableb.data alone, then Oracle would have to seek back to the tableb table to find the ID values. This could hurt performance and might even cause the index to not be used.