CODEWITHSUNDEEP
sqldatabaseoracle-databaseindexing
Dreamer
Dreamer

Reputation: 7549

Is it necessary to index columns just for searching in Oracle views?

Let's say if there is a small table called TB_SMALL that contains 50 rows with simple structure:

TB_SMALL
---------------
ID     |  NAME
---------------
......
......

and there is another table holding large amount of data called TB_HUGE, that contains a million rows:

    TB_HUGE
------------------------------------------------------------------------
ID     |  FIELD 1  |  TB_SMALL_ID  |  FIELD 1  |  ......  |  FIELD n  |
------------------------------------------------------------------------
......
......

So that TB_HUGE has a foreign key indexed to TB_SMALL. However, if there is need to join both table just to search by specific TB_SMALL.name, which I assume it is a common case, like this

SELECT TB_HUGE.* 
FROM TB_HUGE JOIN TB_SMALL ON TB_HUGE.TB_SMALL_ID=TB_SMALL.id
WHERE TB_SMALL.name = "somename";

is it necessary to index TB_SMALL.NAME column?

* may be it is not a good example, as the query can change to

WHERE TB_HUGE.TB_SMALL_ID EXIST (SELECT ID FROM TB_SMALL WHERE NAME="somename")

but above case is just to illustrate whether it is necessary to index column on small table if join with large tables.

Upvotes: 0

Views: 57

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132580

You may not need to for performance reasons, since the whole of TB_SMALL probably fits into a single block or two and can be accessed by a full scan just as fast as by an index (maybe faster). However:

  1. To preserve data integrity, every table should have a primary key, and a primary key requires an index, so in fact you should have an index anyway. (In fact, TB_SMALL would be a good candidate for being an index-organized table (IOT)).

  2. Since the table is small, having an index won't be a big waste of space, so you may as well index it and let the optimizer decide whether or not to use it.

Upvotes: 1

Related Questions