1

when I create an index on a db2, for example with the following code:

CREATE INDEX T_IDX ON T(
A,
B)

is it a composite index? if not: how can I then create a composite index? if yes: in order to have two different index should I create them separately as:

CREATE INDEX T1_IDX ON T(A)
CREATE INDEX T2_IDX ON T(A)

EDIT: this discussion is not going in the direction I expect (but in a better one :)) I actually asked how, and not why to create separate indexes, I planed to do that in a different question, but since you anticipated me:

suppose I have a table T(A,B,C) and a search function search() that select from the table using any of the following method

WHERE A = x
WHERE B = x
WHERE C = x
WHERE A = x AND B=y (and so on AC, CB, ABC)

if I create a compose index ABC, is it going to working for example when I select on just C? the table is quite big, and the insert\update not so frequent

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
lucaconlaq
  • 1,511
  • 4
  • 19
  • 36

3 Answers3

2

Yep multiple fields on create index = composite by definition: Specify two or more column names to create a composite index.

Understanding when to use composite indexes appears to be your last question...

If all columns selected by a query are in a composite index, then the dbengine can return these values from the index without accessing the table. so you have faster seek time.

However if one or the other are used in queries, then creating individual indexes will serve you best. It depends on the types of queries executed and what values they contain/filter/join.

If you sometimes have one, the other, or both, then creating all 3 indexes is a possibility as well. But keep in mind each additional index increases the amount of time it takes to insert, update or delete, so on highly maintained tables, more indexes are generally bad since the overhead to maintain the indexes effects performance.

xQbert
  • 34,733
  • 2
  • 41
  • 62
0

First Answer: YES

CREATE INDEX JOB_BY_DPT
      ON EMPLOYEE (WORKDEPT, JOB)

Second Answer:

It depends on your query; if most of the time your query referrence a single column in where clause like select * from T where A = 'something' then a single index would be what you want but if both column A and B get referrenced then you should go for creating a composite one.

For further referrence please check

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000919.htm

Rahul
  • 76,197
  • 13
  • 71
  • 125
0

The index on A, B is a composite index, and can be used to seek on just A or a seek on A with B or for a general scan, of course.

There is usually not much of a point in having an index on A, B and an index on just A, since a partial search on A, B can be used if you only have A. That wider index will be a little less efficient, however, so if the A lookup is extremely frequent and the write requirements mean that it is acceptable to update the extra index, it could be justifiable.

Having an index on B may be necessary, since the A, B index is not very suitable for searches based on B only.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265