I have a query that joins two tables. The optimizer plan looks like
this:

RETURN
( 1)
( 2)
( 3)
( 4)
( 5)
/ \
TBSCAN TBSCAN
( 6) ( 10)
( 7) ( 11)
( 8) ( 12)
DB2ADMIN DB2ADMIN
SCL_ITEM SCL


The primary keys for the two tables are:
SCL_ITEM SCL
------------ ------------
SCL_ID SCL_ID
PAGE_NBR PAGE_NBR
ITEM_ID


The query is:
select scl.scl_id, scl.page_nbr, count(*), sum(item.qty)
from scl scl join scl_item item
on scl.scl_id = item.scl_id
and scl.page_nbr = item.page_nbr
group by scl.scl_id, scl.page_nbr

The statistics are up to date, but this query runs slowly. I always
thought that the optimizer would choose to use an index path for a
join such as this, but it seems to prefer the table scan. Is there
another trick that I could try out to convince the optimizer to use
the index path? (Platform is AIX 4.3.3 UDB 7.2 fp5)

Thanks,
Mike

Mike, How about,

select scl.scl_id, scl.page_nbr, count(scl.scl_id), sum(item.qty)
from scl scl join scl_item item
on scl.scl_id = item.scl_id and
scl.page_nbr = item.page_nbr
group by scl.scl_id, scl.page_nbr


Powell.



mikelbell2000 (AT) yahoo (DOT) com (Mike L. Bell) wrote in message news:<49d7474a.0308151159.7457baaf (AT) posting (DOT) google.com>...

you could use OPTIMIZE FOR xx ROWS clause.
As a result, most probably the query would run slower.

reorganizing both tables on the indexes on SCL_ID would speed the
query up and maybe eliminate slow sorts

you could also try to add all the involved columns to the indexes to
use index only access

"Mike L. Bell" <mikelbell2000 (AT) yahoo (DOT) com> wrote

The problem is that the index on SCL_ITEM is not useful for this query.
Yes, it contains the two columns used in the join, but it also contains a
third column (ITEM_ID) which is _not_ used in the query and thus DB2 ignores
the index.

If you create an index on SCL_ITEM that contains just the SCL_ID and
PAGE_NBR columns, then DB2 will use an index scan on both tables.

--
Matt Emmerton

mikelbell2000 (AT) yahoo (DOT) com (Mike L. Bell) wrote in message
If (SCL_ID, PAGE_NBR) in SCL_ITEM table is a foreign key referencing
SCL table,
it will be not neccesary to join scl table.
Like this:
select item.scl_id, item.page_nbr, count(*), sum(item.qty)
from scl_item item
group by item.scl_id, item.page_nbr

Anyway, optimizer may not choose index access same as your original
query, because of efficiency of table scan only as Philip wrote.

Not a good idea to hide the real predicates to us.
It's just like going to the garage and giving car-a for repair when it's
actually car-b that's broken.

Clustering index/table may help for the sort. (check also sort
parms/snapshots)
Your (!!**[--]Hidden[--]**!!) predicates are probably not part of the
index(es).


PM