Index usage on db2 v7 for os390

[Andr? Queiroz]

Hi,
I have a table with 10M records and col A has a index created on it.
The data on that table has the same value for col A on all 10M
records. After that I insert diferent values for that column but my
queries do not use the index I created for that column.
Is there any way I can force the usage of the index or to ommit a
value on the index creation, like 0 (zeroes) or spaces?
Thanks in advance,

André Queiroz

[Blair Adamache]

DB2 on Linux/UNIX/Windows doesn't support hints that will force index
usage. The following things may help an index get chosen:

runstats (accurate statistics for the table and index, with distribution)

declaring a table volatile if it changes too often to rely on runstats
(search on volatile here to learn more:
http://publib.boulder.ibm.com/infoce...help/index.jsp)

You could also try SET CURRENT QUERY OPTIMIZATION=0

Andr? Queiroz wrote:

Hi,
I have a table with 10M records and col A has a index created on it.
The data on that table has the same value for col A on all 10M
records. After that I insert diferent values for that column but my
queries do not use the index I created for that column.
Is there any way I can force the usage of the index or to ommit a
value on the index creation, like 0 (zeroes) or spaces?
Thanks in advance,

André Queiroz

[Mark A]

"Andr? Queiroz" <an***********@netcabo.pt> wrote in message
news:e5**************************@posting.google.c om...

Hi,
I have a table with 10M records and col A has a index created on it.
The data on that table has the same value for col A on all 10M
records. After that I insert diferent values for that column but my
queries do not use the index I created for that column.
Is there any way I can force the usage of the index or to ommit a
value on the index creation, like 0 (zeroes) or spaces?
Thanks in advance,

André Queiroz

Try doing a reorg of the index (or reorg table with indexes) and then
running runtstats with full statistics. That will let DB2 know that the new
values of ColA are available. However, DB2 may still not use the index
unless there are at least 20 different values present, and even then it may
not use it.

When DB2 decides to use an index for performance reasons (which is different
than using an unique index to guarantee uniqueness) it does so to be able to
ignore certain pages of data rows. Data is normally stored in 4K pages, so
if the row length is 200 bytes, there are 20 rows per page. Assuming a
random distribution of ColA in the table (and the table is not clustered on
ColA), there would need to be more than 20 unique values of ColA in order
for DB2 to skip a page in the tablespace and benefit by using the index.
Otherwise, just scanning the tablespace is faster, especially since
sequential prefetch can be used.

If your reorg the data and run full statistics, DB2 will almost always chose
the most efficient access path, even if DB2 decides that using an index is
not the most efficient method.

If the index is not being used, you should drop it to improve insert
performance.

[Mark A]

"Blair Adamache" <ba*******@2muchspam.yahoo.com> wrote in message
news:bp**********@hanover.torolab.ibm.com...

DB2 on Linux/UNIX/Windows doesn't support hints that will force index
usage. The following things may help an index get chosen:

runstats (accurate statistics for the table and index, with distribution)

declaring a table volatile if it changes too often to rely on runstats
(search on volatile here to learn more:
http://publib.boulder.ibm.com/infoce...help/index.jsp)

You could also try SET CURRENT QUERY OPTIMIZATION=0

The question pertains to DB2 for OS/390 as mentioned in the subject line.

[Andr? Queiroz]

So even if I could force DB2 to use an index it would be very slow
until I had enough data so DB2 could really use the index.
I believe the only choice would be to wait till I have enough data
and cardinalitty and then run reorg and runstats, am I right?

Try doing a reorg of the index (or reorg table with indexes) and then
running runtstats with full statistics. That will let DB2 know that the new
values of ColA are available. However, DB2 may still not use the index
unless there are at least 20 different values present, and even then it may
not use it.

When DB2 decides to use an index for performance reasons (which is different
than using an unique index to guarantee uniqueness) it does so to be able to
ignore certain pages of data rows. Data is normally stored in 4K pages, so
if the row length is 200 bytes, there are 20 rows per page. Assuming a
random distribution of ColA in the table (and the table is not clustered on
ColA), there would need to be more than 20 unique values of ColA in order
for DB2 to skip a page in the tablespace and benefit by using the index.
Otherwise, just scanning the tablespace is faster, especially since
sequential prefetch can be used.

If your reorg the data and run full statistics, DB2 will almost always chose
the most efficient access path, even if DB2 decides that using an index is
not the most efficient method.

If the index is not being used, you should drop it to improve insert
performance.

[Mark A]

"Andr? Queiroz" <an***********@netcabo.pt> wrote in message
news:e5**************************@posting.google.c om...

So even if I could force DB2 to use an index it would be very slow
until I had enough data so DB2 could really use the index.
I believe the only choice would be to wait till I have enough data
and cardinalitty and then run reorg and runstats, am I right?

If you don't have more than about 7-10 4K pages worth of data in the table
(so if each row is 200 bytes, that would be about 140-200 rows), DB2 will
not likely use an index (except to enforce uniqueness). This is because it
is generally faster for DB2 to just read all the rows of the table instead
of read the index and then the appropriate table rows.

If your table will get much larger in the future and you want to know how
DB2 will access the data when it is much larger, you can update the cardf
stats in the catalog for the table/tablespace to the expected number of
rows. This may help DB2 to decide to use an index. Any manual updates to the
catalog statistics will be over-written when you run runstats.

Even though it may be faster to not use any indexes when the table is small,
forcing DB2 to use an index will not be significantly slower if you really
want to do that.

[Martin]

In article <e5**************************@posting.google.com >,
Andr? Queiroz <an***********@netcabo.pt> wrote:

So even if I could force DB2 to use an index it would be very slow
until I had enough data so DB2 could really use the index.
I believe the only choice would be to wait till I have enough data
and cardinalitty and then run reorg and runstats, am I right?

Do not forget to Bind or Rebind the plans/packages after the Runstats!!

--
Martin Avison
Note that emails to News@ will be junked. Use Martin instead of News

[Ramachandran Subramanian]

In my opinion , you have a few options.
I do have my doubts about the "cardinality concept" of the Index. For
example we have an index at our shop on sex . It's cardinality is 2 (
M and F). But that index is used by DB2. I think the more important
factor here is the FILTER FACTOR. What is the selectivity your index
is going to offer. If currently all the rows are the same , then your
filter factor is 1 . 100 % of the rows are going to qualify. That is
wht DB2 doesnt want to use the index. It has to read the entire index
and then get the RID s and then read the entire table.
If you are going to get more distinct values in the column , then
keep the index on it and run some thing called DSTATS. This collects
distribution statistics for non-uniformly distributed columns and
column correlation columns. Then you can update the catalog tables
with this value and DB2 will use the Index if you specify REOPT(VARS)
during run time depending on the Host variable value in the WHERE
clause.

Or

The easier method will be to just add an other column(s) to this
index to change it's distribution charecteristics.

[Mark A]

"Ramachandran Subramanian" <rr****@yahoo.com> wrote in message
news:c4**************************@posting.google.c om...

In my opinion , you have a few options.

I do have my doubts about the "cardinality concept" of the Index. For
example we have an index at our shop on sex . It's cardinality is 2 (
M and F). But that index is used by DB2. I think the more important
factor here is the FILTER FACTOR. What is the selectivity your index
is going to offer. If currently all the rows are the same , then your
filter factor is 1 . 100 % of the rows are going to qualify. That is
wht DB2 doesnt want to use the index. It has to read the entire index
and then get the RID s and then read the entire table.
If DB2 is using this index on sex then your distribution is severely skewed
(and the Civil Rights Division of the Justice Department will be visiting
you soon), or your stats are bogus, or the sex index is the clustering
index, or your rows are very long (probably at about 2000 - 4000 bytes
each).

Bottom line is the DB2 will use an index if it can avoid reading some
physical pages. If not, then DB2 usually will not use an index (unless DB2
is checking for uniqueness, or using an index in lieu of a sort, or using
multiple indexes in the same access path, or using index only access).

In the example I gave previously, if the row length is 200 then there are
about 20 rows per 4K page. That means to avoid reading the page (ASSUMING
EVEN DISTRIBUTION OF THE VALUES) there needs to be at least 20 unique
values. If the values are not evenly distributed, you can take the least
frequent value and use that as the inverse of the cardinality. So if one of
the values is 2% of the table, then the cardinality is 50. If the index
cardinality is greater than the rows per page, then DB2 is more likely to
use the index.

As stated above, DB2 "may" use a clustered index regardless of cardinality.
If you are going to get more distinct values in the column , then
keep the index on it and run some thing called DSTATS. This collects
distribution statistics for non-uniformly distributed columns and
column correlation columns. Then you can update the catalog tables
with this value and DB2 will use the Index if you specify REOPT(VARS)
during run time depending on the Host variable value in the WHERE
clause.
99 times out of 100, if DB2 does not choose to an index there is good reason
for that. Either the indexes are not defined on the correct columns or DB2
just should not use it.
Or

The easier method will be to just add an other column(s) to this
index to change it's distribution charecteristics.

Why? If the index is not being used, drop it.