SEO

#1 (**permalink**) 02-27-2008, 05:20 AM

Hi. If I have an index (or primary key) on 3 columns, will a subset of
these columns automatically be indexed also?

For example:

CREATE INDEX idx ON tbl(cola, colb, colc);

Will:

SELECT cold FROM tbl WHERE cola = 1 AND colb = 2

Use the index, or should a separate index be declared for only these
two columns?

Thanks.

#2 (**permalink**) 02-27-2008, 05:20 AM

Hi.

usenet@kikobu.com wrote:
> Will:
>
> SELECT cold FROM tbl WHERE cola = 1 AND colb = 2
>
> Use the index, or should a separate index be declared for only these
> two columns?

You'd better ask your optimizer

Probably the index will be used, as you've given one single explicit
value for the first two components of the index. The index will (quite
for sure) *not* be used for conditions as "where colc = x" - then, a
single index on colc would be appropriate.

regards,
Benjamin
--
11.+12.11.2005: SFSCon 2005
see http://www.sfscon.it

#3 (**permalink**) 02-27-2008, 05:20 AM

<usenet@kikobu.com> wrote in message
news:1131533118.196853.95610@f14g2000cwb.googlegro ups.com...
>
> Hi. If I have an index (or primary key) on 3 columns, will a subset of
> these columns automatically be indexed also?
>
> For example:
>
> CREATE INDEX idx ON tbl(cola, colb, colc);
>
> Will:
>
> SELECT cold FROM tbl WHERE cola = 1 AND colb = 2
>
> Use the index, or should a separate index be declared for only these
> two columns?
>
> Thanks.
>
Yes, so long as the left most columns in the index match the predicate, the
b-tree of the index will be used. In your example matching columns is = 2.
The b-tree will be used if matching columns is > 0.

WHERE cola = 1 AND colb = 2 AND colc = 3, matching columns = 3
WHERE cola = 1 AND colb = 2, matching columns = 2
WHERE cola = 1, matching columns = 1
WHERE cola = 1 AND colc = 3, matching columns = 1
WHERE colb = 2 AND colc = 3, matching columns = 0
WHERE colc = 3, matching columns = 0

However, even if matching columns is 0 (and at least one predicate column is
in the index), DB2 might still use the index, but could not use the b-tree,
and would have to read all the index leaf pages (sort of like a table scan
of the entire index).

#4 (**permalink**) 02-27-2008, 05:20 AM

<usenet@kikobu.com> wrote in message
news:1131533118.196853.95610@f14g2000cwb.googlegro ups.com...
>
> Hi. If I have an index (or primary key) on 3 columns, will a subset of
> these columns automatically be indexed also?
>
> For example:
>
> CREATE INDEX idx ON tbl(cola, colb, colc);
>
> Will:
>
> SELECT cold FROM tbl WHERE cola = 1 AND colb = 2
>
> Use the index, or should a separate index be declared for only these
> two columns?
>
> Thanks.
>
Yes, so long as the left most columns in the index match the predicate, the
b-tree of the index will be used. In your example matching columns is = 2.
The b-tree will be used if matching columns is > 0.

WHERE cola = 1 AND colb = 2 AND colc = 3, matching columns = 3
WHERE cola = 1 AND colb = 2, matching columns = 2
WHERE cola = 1, matching columns = 1
WHERE cola = 1 AND colc = 3, matching columns = 1
WHERE colb = 2 AND colc = 3, matching columns = 0
WHERE colc = 3, matching columns = 0

However, even if matching columns is 0 (and at least one predicate column is
in the index), DB2 might still use the index, but could not use the b-tree,
and would have to read all the index leaf pages (sort of like a table scan
of the entire index).

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Index question