Hi,

The following SQL:
after reorg and runstats the above SQL uses 'table scan' (seeing from explain).

But adding a condition "AND B.ParentCol = COALESCE(B.ParentCol, B.ParentCol)" makes DB2 uses the 'index scan' which is in my case 3-times faster. Index is on column B.ParentColPK - this column is PK of ParentTable.

Target SQL looks like this:
Are there any general rules to enforce 'index scan' instread of 'table scan'? I am just looking is some general tips...

Thanks,
Grofaty

There is no direct way of "enforcing the optimizer" (well its evolving in DB2) like in some of the other RDBMSes (e.g Oracle).

What you can do is enable the "volatile" property of the table as
ALTER TABLE X VOLATILE

The volatile property tells the optimizer to prefer index scan or table scan.
Read up the documentation and see if it helps.

If you are on DB2 v8.2 or above (8.1 fixpack 7 or above), then you can try to use the DB2 Advisor - it may shed some light on what will help your query.

Jayesh

Have you looked into using an optimization hint?

What if you use an inner join from the parent to the child instead of an outer join from the child to the parent?

ex:
SELECT A.DependentCol1, B.ParentCol1 FROM
ParentTable B INNER JOIN DependentTable A
ON (A.DependentColFK = B.ParentColPK)

It should be functionally equivalent, assuming there is a true parent-child relationship between the tables. The optimizer might be smarter about it.