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 3 6011
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. 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.
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Justin Hennessy |
last post by:
Hi all,
I am working with a computer hardware asset database and I am trying to get
information out of it for each PC in my organisation.
Here is the basic table structure:
Table1 -> Table2...
|
by: Dam |
last post by:
Using SqlServer :
Query 1 :
SELECT def.lID as IdDefinition,
TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour
FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins"...
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins"...
|
by: Thomas Beutin |
last post by:
Hi,
i've a speed problem withe the following statement:
SELECT DISTINCT pz.l1_id, pz.l2_id, pz.l3_id, pz.l4_id
FROM ot_adresse AS a, ot_produkt AS p
LEFT OUTER JOIN ot_kat_prod AS pz ON (...
|
by: Ian Boyd |
last post by:
i know nothing about DB2, but i'm sure this must be possible.
i'm trying to get a client to create a view (which it turns out is called a
"Logical" in DB2). The query needs a LEFT OUTER JOIN, but...
|
by: mike |
last post by:
I'm using postgresl 7.3.2 and have a query that executes very slowly.
There are 2 tables: Item and LogEvent. ItemID (an int4) is the primary key
of Item, and is also a field in LogEvent. Some...
|
by: polycom |
last post by:
Assistance needed to optimize this query
SELECT SD.content_id AS Id,
SD.title AS Title,
CT.name AS Contenttype,
PV.content_id AS SponsorId,
...
|
by: nico3334 |
last post by:
I have a query that currently pulls data from a main table and a second table using LEFT OUTER JOIN. I know how to do make another LEFT OUTER JOIN with the main table, but I want to add another LEFT...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |