who can explain this?

[radhika]

ENVIRONMENT:

DB2 OS/390 V6 Peoplesoft v8
I have a strange situation:

I have a table called TABLEA and it has indexes
Ind1
Ind2
Ind3

Now when I do this on the table

Select * from table where NAME='XYZ'

It is using an index IND1 and it is very slow.

But if I do this

Select * from table where substr(name,1,8)='XYZ' now it using
IND2 and it is very fast.

Now my question is why is this behaviour? And what caused substr to
use another index and efficiently.

Thanks

[Mark A]

"radhika" <ra************@hotmail.com> wrote in message
news:91**************************@posting.google.c om...

ENVIRONMENT:

DB2 OS/390 V6 Peoplesoft v8
I have a strange situation:

I have a table called TABLEA and it has indexes
Ind1
Ind2
Ind3

Now when I do this on the table

Select * from table where NAME='XYZ'

It is using an index IND1 and it is very slow.

But if I do this

Select * from table where substr(name,1,8)='XYZ' now it using
IND2 and it is very fast.

Now my question is why is this behaviour? And what caused substr to
use another index and efficiently.

Thanks

Please show the DDL of the indexes.

But often times, unexpected behavior of indexes is caused by disorganization
of the table and indexes. To resolve this, you should reorg the table and
indexes, then run runstats utility with full statistics captured, and then
rebind the plan (if static).

[Blair Adamache]

Can you post the CREATE statements for the tables and indexes? You might
be able to do this with db2look from a client workstation on Unix,
Windows or Linux?

radhika wrote:

ENVIRONMENT:

DB2 OS/390 V6 Peoplesoft v8
I have a strange situation:

I have a table called TABLEA and it has indexes
Ind1
Ind2
Ind3

Now when I do this on the table

Select * from table where NAME='XYZ'

It is using an index IND1 and it is very slow.

But if I do this

Select * from table where substr(name,1,8)='XYZ' now it using
IND2 and it is very fast.

Now my question is why is this behaviour? And what caused substr to
use another index and efficiently.

Thanks

[radhika]

O.K Here is the statements.

These are the CREATE INDEX statements.

Create unique index ind1 on table
(col1 , col2, col3, col4, col5)

create index ind2 on table (col1, col2, col3, col5, col6)

create index ind3(col1, col3)

create index ind4(col4)

Now without the substr it is using ind3 and it is very bad.
With the substr it is using ind4 and it is very good.

why is that?

This is on os/390 db2 v6

Thanks


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

Can you post the CREATE statements for the tables and indexes? You might
be able to do this with db2look from a client workstation on Unix,
Windows or Linux?

radhika wrote:

ENVIRONMENT:

DB2 OS/390 V6 Peoplesoft v8
I have a strange situation:

I have a table called TABLEA and it has indexes
Ind1
Ind2
Ind3

Now when I do this on the table

Select * from table where NAME='XYZ'

It is using an index IND1 and it is very slow.

But if I do this

Select * from table where substr(name,1,8)='XYZ' now it using
IND2 and it is very fast.

Now my question is why is this behaviour? And what caused substr to
use another index and efficiently.

Thanks

[Mark A]

> > radhika wrote:

ENVIRONMENT:

DB2 OS/390 V6 Peoplesoft v8
I have a strange situation:

I have a table called TABLEA and it has indexes
Ind1
Ind2
Ind3

Now when I do this on the table

Select * from table where NAME='XYZ'

It is using an index IND1 and it is very slow.

But if I do this

Select * from table where substr(name,1,8)='XYZ' now it using
IND2 and it is very fast.

Now my question is why is this behaviour? And what caused substr to
use another index and efficiently.

Thanks

"radhika" <ra************@hotmail.com> wrote in message
news:91**************************@posting.google.c om... O.K Here is the statements.

These are the CREATE INDEX statements.

Create unique index ind1 on table
(col1 , col2, col3, col4, col5)

create index ind2 on table (col1, col2, col3, col5, col6)

create index ind3(col1, col3)

create index ind4(col4)

Now without the substr it is using ind3 and it is very bad.
With the substr it is using ind4 and it is very good.

why is that?

This is on os/390 db2 v6

Thanks

1. Which column is NAME (col1, col2, col3, col4, col 5, or col6). I think
you made a mistake because ind3 and ind4 have completely different columns
in their indexes as described above.
2. I need the exact SQL statement and working storage definitions of any
host variables in that SQL statement. Host variables start with a colon.
3. You can also use NAME LIKE 'XYZ%' which may be better performing than
substr. In any case it is no worse and more widely accepted method.
4. It would be better if you disclosed which indexes are unique and which is
the clustered index. If no index is explicitly defined as clustered, then
the first index created is the clustered index. BTW, the employee table
should probably be clustered on last name or department (i.e., in a
multi-column index these columns need to be the first column in the
clustered index)..

[PM-pm3iinc-nospam]

maybe there is a variable on mvs/os390/zos that is similar to the luw db2
udb DB2_LIKE_VARCHAR?

PM