Left Outer Join Using Views

[rjaw]

Hi there,

we have a problem using the left outer join-operation on a db2
database, v7, running on z/os.

Defined are two tables, table1 and table2. Both tables have a primary
key of field1, field2 and field3. There two tables exist in two
different schemas. Also, there are two view, view1 and view2. View1
joins the table1 on both of the schemas, also view2 for the table2 on
both schemas.

If running a sql-statement on each table (it does not matter on which
schema), there is an index access into the table. Running the following
sql-statement on the views, only view1 will use the index access for
its tables, view2 uses a full table scan:

select a.*
from schema.view1 a
right outer join schema.view2 b
on b.field1= a.field1
and b.field2 = a.field2
and b.field3 = a.field3
where b.field1 = ?
and b.field2 = ?
and b.field3 = ?
with ur;

Does anyone know what happens on the database? Why is there not an
index access on both views, althought both view use tables where the
where-statement has an index access to?

Thanx for your answers.

Ralf

[Mark Barinstein]

Hi.

we have a problem using the left outer join-operation on a db2
database, v7, running on z/os.

From my experience v7 (udb for LUW, not z/OS) doesn't like outer views.
I had such schema:
table t1(c1, c2, ...),
table t2(c1, c2, ...),
view v2 (select * from t2 where <fairly_simple_ condition>).

select ...
from t1
left join t2 on ...
where t1.pk = ...
produced index scans,

select ...
from t1
left join v2 on <join_clause>
where "t1.pk = <pk_expr>"
produced full scan of one of tables (I don't remember which),

finally, I had to do this:

select t1.c1, ..., v2.c1, ...
from t1
join v2 on <join_expr>
where "t1.pk = <pk_expr>"
union all
select t1.c1, ..., cast(NULL as ...), ...
from t1
where "t1.pk = <pk_expr>"
and not exists (select 1
from v2
where <join_expr>)

Sincerely,
Mark.

[pz]

rj**@gmx.de wrote:

If running a sql-statement on each table (it does not matter on which
schema), there is an index access into the table. Running the following
sql-statement on the views, only view1 will use the index access for
its tables, view2 uses a full table scan:

select a.*
from schema.view1 a
right outer join schema.view2 b
on b.field1= a.field1
and b.field2 = a.field2
and b.field3 = a.field3
where b.field1 = ?
and b.field2 = ?
and b.field3 = ?
with ur;

There was an article some time ago on the IBM Developerworks site regarding joins in DB2.
Apparently, if you specify conditions in the WHERE clause they will be applied after the join,
whereas if you put them in the ON clause they will be processed before the join. Try rewriting your
query as follows:

select a.*
from schema.view1 a
right outer join schema.view2 b
on b.field1= a.field1
and b.field2 = a.field2
and b.field3 = a.field3
and b.field1 = ?
and b.field2 = ?
and b.field3 = ?

and see if it makes any difference.

[Mark Barinstein]

Hi.

There was an article some time ago on the IBM Developerworks site regarding joins in DB2. Apparently, if you specify conditions in the WHERE clause they will be applied after the join, whereas if you put them in the ON clause they will be processed before the join. Try rewriting your query as follows:

select a.*
from schema.view1 a
right outer join schema.view2 b
on b.field1= a.field1
and b.field2 = a.field2
and b.field3 = a.field3
and b.field1 = ?
and b.field2 = ?
and b.field3 = ?

and see if it makes any difference.

I tried this on my scheme (see posting above) and result is quite strange:
Table scanned by index became to be full scanned and vice versa.

Sincerely,
Mark.