Search code examples
oracle-databaseindexingcompound-index

Which indexes are necessary?

If I have a table TABLE_1 with, lets say 5 columns:

COL1    |   COL2    |   COL3    |   COL4    |   COL5
[line]
[line]
[...]

And there are two main queries I want to do:

SELECT * FROM table_a WHERE COL1 = 'X' and COL2 = 'Y'

And the other being:

SELECT * FROM table_a WHERE COL2 = 'Z'

Which indexes should I create? Creating one with columns COL1 and COL2 would index for both queries, or should I need another index just for COL2 in order to have the second query faster?

thanks!

Solution

  • A regular B-tree index on table_a(COL2, COL1) could be used by both queries.

    Having an index on COL2 alone might be more efficient to retrieve rows using only a filter on this column but the additional index will use space and will slow down inserts (and updates if you ever update this column). It's a trade-off.