CODEWITHSUNDEEP
oracle-databaseindexing
Mikayil Abdullayev
Mikayil Abdullayev

Reputation: 12376

Is it OK to create several indexes on a table if they're really needed

I have a table with 7 columns.

It's going to contain lots and lots of data - something like more than 1.7 million records will be added every month.

Of those 7 columns 5 are the ones that I'll be using in the WHERE clause of my queries against this table in different combinations.

Is it OK to create different indexes for those possible combinations ?

I'm asking this question because if I do that, there'll be more than 10 indexes on this table and I'm not sure if this is a good idea.

On the other hand, I'm afraid of querying a table with this big amount of data without indexes.

Here's the table:

CREATE TABLE AG_PAYMENTS_TO_BE  
(
  PAYMENTID NUMBER(15, 0) NOT NULL  
, DEPARTID NUMBER(3,0)  
, PENSIONERID NUMBER(11, 0) NOT NULL  
, AMOUNT NUMBER(6, 2)  
, PERIOD CHAR(6 CHAR)  
, PAYMENTTYPE NUMBER(1,0)  
, ST NUMBER(1, 0) DEFAULT 0   
, CONSTRAINT AG_PAYMENTS_TO_BE_PK PRIMARY KEY  
  (  
    PAYMENTID  
  )  
  ENABLE  
);

Possible queries:

  1. SELECT AMOUNT FROM AG_PAYMENTS_TO_BE WHERE ST=0 AND DEPARTID=112 AND PERIOD='201207';
  2. SELECT AMOUNT FROM AG_PAYMENTS_TO_BE WHERE ST=0 AND PENSIONERID=123456 AND PERIOD='201207';
  3. SELECT AMOUNT FROM AG_PAYMENTS_TO_BE WHERE ST=0 AND PENSIONERID=123456 AND PERIOD='201207' AND PAYMENTTYPE=1;
  4. SELECT AMOUNT FROM AG_PAYMENTS_TO_BE WHERE ST=0 AND DEPARTID=112 AND ST=0;
  5. SELECT AMOUNT FROM AG_PAYMENTS_TO_BE WHERE ST=0 AND PENSIONERID=123456;

and so on.

Upvotes: 4

Views: 132

Answers (3)

Kirill Leontev
Kirill Leontev

Reputation: 10941

This decision would greatly depend on expected number of distinct values in each column, and thus selectivity of each possible index.

Things I would consider while making decisions:

  1. Obviously, PAYMENTTYPE & ST fields hold up to 10 19 distinct values each, which is pretty unselective if we keep in mind your expected volume of data (~400M rows), so they won't help you much.

  2. However, they probably could become good candidates for list partitioning instead.

  3. I would also think of switching PERIOD CHAR(6 CHAR) to DATE and making a composite range-list partition on period+st/paymenttype.

  4. DEPARTID - If you have hundreds of departments, then it's probably an indexing candidate, but if only dozens - then probably a full scan would perform way faster.

  5. PENSIONERID seems to be a high-selectivity field, so I would consider creating a separate index on it, and including it in a composite index on PERIOD+PENSIONERID (in that field order).

Upvotes: 2

Chris Saxon
Chris Saxon

Reputation: 9865

Ignoring index skip scans* for the moment, in order for a query to use an index:

  • The leading index columns must be listed in the query
  • They must compared using exact joins (i.e. using =, not <,> or like)

For example, a table with a composite index on (a, b) could use the index in the following queries:

  • a = :b1 and b >= :b2
  • a = :b1

but not:

  • b = :b2

because column b is listed second in the index. * In some cases, it's possible for the index to be used in this case via an index skip scan. This is where the leading column in the index is skipped. There needs to be relatively few distinct values for the first column however, which doesn't happen often (in my experience).

Note that a "larger" index can be used by queries which only use some of the leading columns from it. So in the example above, an index on just a is redundant because the queries shown can use the index on a, b. An index on just b may be useful however.

The more indexes you add, the slower your inserts/updates/deletes will be, because the indexes have to be maintained at the same time as the table. Therefore you should aim to keep the number of indexes down, unless there's significant query benefits to adding a new one. This is something you'll have to measure in your environment to determine the exact cost/benefit.

Note that having multiple indexes with similar columns can lead to the wrong index being selected. So there is potential downside for selects when you have many similar indexes. There is also a slight overhead in parse times, as Oracle has more options to consider when selecting the execution plan.

Looking at your queries I believe you only need indexes on:

  • st, departid, period
  • st, pensionerid, period

You may wish to add amount at the end of these as well, so your queries can be fully answered from the index, saving you a table lookup. You may also need further indexes if these columns are foreign keys to other tables, to prevent locking issues.

Upvotes: 3

intrixius
intrixius

Reputation: 1136

I think you should create a few combined indexes (like ('ST' and 'PERIOD') and ('ST' and 'PENSIONERID'). That will speed up most of your sample queries...

Upvotes: 1

Related Questions