473,698 Members | 2,490 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Strange access plan

Hello.

W2K, db2 v7, fp11.

Given:

create table pays (
acode integer not null,
packno smallint not null,
sum decimal(15, 2) not null
);
create index pays1 on pays(packno);

create table abonents (
acode integer not null primary key,
name varchar(80) not null,
specauth smallint not null
);

create view abonents_spec (acode, name) as
select acode, case when specauth<2 then name else '*' end
from abonents;

I have to issue this query:

(1)
select p.acode, p.sum, a.name
from pays p
left join abonents_spec a on p.acode=a.acode
where p.packno=:packn o;

In this case I have index scan of 'pays' (as expected) and TABLE scan of
'abonents', but in case of using in (1) inner join or left join with table
'abonents' instead of view 'abonents_spec' I get expected INDEX scan of
tabel 'abonents'!!!
Tables are about 200 000 rows. Statistics is OK. There is no special
distirbution of data in these tables.
I have tried all optimization levels.
I think it is very strange behavior of the optimizer...

I had to rewrite (1) with emulation of the left join to make optimizer use
index scan of table 'abonents':

(2)
with p (acode, sum) as (
select acode, sum
from pays where packno=:packno
)
select p.acode, p.sum, a.name as name
from p
join abonents_spec a on p.acode=a.acode
union all
select p.acode, p.sum, cast(NULL as varchar(80)) as name
from p
where not exists (select 1 from abonents_spec a on p.acode=a.acode );

With (2) I had ~ 2 or 3 times worse performance then (1) with using table
'abonents' instead of view 'abonents_spec' , but it is definitely faster then
(1).
What do you think about this?

Thanks in advance,
Mark.
Nov 12 '05 #1
10 2095

"Mark Barinstein" <ma**@NO.SPAM.A NY.MOREcrk.vsi. ru> wrote in message
news:c6******** ***@serv.vrn.ru ...
Hello.

W2K, db2 v7, fp11.

Given:

create table pays (
acode integer not null,
packno smallint not null,
sum decimal(15, 2) not null
);
create index pays1 on pays(packno);

create table abonents (
acode integer not null primary key,
name varchar(80) not null,
specauth smallint not null
);

create view abonents_spec (acode, name) as
select acode, case when specauth<2 then name else '*' end
from abonents;

I have to issue this query:

(1)
select p.acode, p.sum, a.name
from pays p
left join abonents_spec a on p.acode=a.acode
where p.packno=:packn o;

In this case I have index scan of 'pays' (as expected) and TABLE scan of
'abonents', but in case of using in (1) inner join or left join with table 'abonents' instead of view 'abonents_spec' I get expected INDEX scan of
tabel 'abonents'!!!
Tables are about 200 000 rows. Statistics is OK. There is no special
distirbution of data in these tables.
I have tried all optimization levels.
I think it is very strange behavior of the optimizer...

I had to rewrite (1) with emulation of the left join to make optimizer use
index scan of table 'abonents':

(2)
with p (acode, sum) as (
select acode, sum
from pays where packno=:packno
)
select p.acode, p.sum, a.name as name
from p
join abonents_spec a on p.acode=a.acode
union all
select p.acode, p.sum, cast(NULL as varchar(80)) as name
from p
where not exists (select 1 from abonents_spec a on p.acode=a.acode );

With (2) I had ~ 2 or 3 times worse performance then (1) with using table
'abonents' instead of view 'abonents_spec' , but it is definitely faster then (1).
What do you think about this?

Thanks in advance,
Mark.


Mark,

It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.

I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the
view.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

Nov 12 '05 #2

"Mark Barinstein" <ma**@NO.SPAM.A NY.MOREcrk.vsi. ru> wrote in message
news:c6******** ***@serv.vrn.ru ...
Hello.

W2K, db2 v7, fp11.

Given:

create table pays (
acode integer not null,
packno smallint not null,
sum decimal(15, 2) not null
);
create index pays1 on pays(packno);

create table abonents (
acode integer not null primary key,
name varchar(80) not null,
specauth smallint not null
);

create view abonents_spec (acode, name) as
select acode, case when specauth<2 then name else '*' end
from abonents;

I have to issue this query:

(1)
select p.acode, p.sum, a.name
from pays p
left join abonents_spec a on p.acode=a.acode
where p.packno=:packn o;

In this case I have index scan of 'pays' (as expected) and TABLE scan of
'abonents', but in case of using in (1) inner join or left join with table 'abonents' instead of view 'abonents_spec' I get expected INDEX scan of
tabel 'abonents'!!!
Tables are about 200 000 rows. Statistics is OK. There is no special
distirbution of data in these tables.
I have tried all optimization levels.
I think it is very strange behavior of the optimizer...

I had to rewrite (1) with emulation of the left join to make optimizer use
index scan of table 'abonents':

(2)
with p (acode, sum) as (
select acode, sum
from pays where packno=:packno
)
select p.acode, p.sum, a.name as name
from p
join abonents_spec a on p.acode=a.acode
union all
select p.acode, p.sum, cast(NULL as varchar(80)) as name
from p
where not exists (select 1 from abonents_spec a on p.acode=a.acode );

With (2) I had ~ 2 or 3 times worse performance then (1) with using table
'abonents' instead of view 'abonents_spec' , but it is definitely faster then (1).
What do you think about this?

Thanks in advance,
Mark.


Mark,

It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.

I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the
view.

--
Matt Emmerton
DB2 OLTP Performance
IBM Toronto Lab

Nov 12 '05 #3
It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.

I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the view.


Matt,

What shall I do, wait for fp12 or forget about v7 at all?
In our project there are a lot of such queries...

Mark.
Nov 12 '05 #4
It appears that the unique index on abonets (implicitly created because of
the primary key definition) isn't seen by the optimizer through the view.

I was able to reproduce this bizarre behaviour on v72 FP11.
On v81 FP5 the correct behaviour (IXSCAN on abonents) is seen when using the view.


Matt,

What shall I do, wait for fp12 or forget about v7 at all?
In our project there are a lot of such queries...

Mark.
Nov 12 '05 #5
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7
Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c6******* ***@hanover.tor olab.ibm.com>.. .
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge


Do you know when FP12 will come out for V7?
Scot
Nov 12 '05 #8
Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c6******* ***@hanover.tor olab.ibm.com>.. .
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge


Do you know when FP12 will come out for V7?
Scot
Nov 12 '05 #9
Probably late May or early June.

Scot wrote:
Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c6******* ***@hanover.tor olab.ibm.com>.. .
If you have the choice you should go to V8.1 anyway.
V7.2 will go out of support pretty soon.

Cheers
Serge

Do you know when FP12 will come out for V7?
Scot


Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2350
by: Ryan | last post by:
Bit of an obscure one here, so please bear with me. I have two copies of a database which should be identical. Both have a complex view which is identical. I can open the views and the data is as expected and match. I can query it in several ways as detailed below. The 5th version of the simple query below based on the second copy of the view fails, but works under the first copy. /*1 Statement below works*/ SELECT * FROM AgentHierarchy
2
2099
by: Carlo Paccanoni | last post by:
I tried this: use northwind go SELECT OrderDate FROM Orders WHERE OrderDate > '19950101' see the query plan? ok
1
2013
by: Vinny | last post by:
Can anyone help me with this strange problem please? I have a stored procedure, with a parameter defined as a uniqueidentifier. The procedure does a select with a number of joins, and filters within the Where clause using this parameter. (@orderHeader_id uniqueidentifier) SELECT *
5
6647
by: Adam Kucharski | last post by:
Hello !!! IBM AIX 5.1 DB2 UDB WSE v.8.1.4 (with FP4) (Polish) DiagLevel 4 Client: Windows XP (Polish) Problem: 2003-12-17-17.58.50.853952 Instance:db2inst1 Node:000 PID:33528(db2tcpcm) TID:1 Appid:none
14
5409
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB 7.2 environment, the choices the optimizer makes often seem flaky. But this last example really floored me. I was hoping someone could explain why I get worse response time when the optimizer uses two indexes, than when it uses one. Some context:
5
6336
by: sql-db2-dba | last post by:
We have DB2 UDB v8.1 fixpak3 on AIX 5. Production and Development configuarations (at least for DB2) are identical albeit production is a 2-way server while development has only one processor. Tables and indexes have the same schema. In fact, the dev database was taken from a prod backup recently. Size of the tables differ slightly. Yet, on a given query (with 4 tables joined), it took 30-50 times longer to run in prod than on development....
22
3304
by: Marc Mones | last post by:
Hello, I'working with IBM DB2 V8.1 and CLI/ODBC. I've got a problem with the following statement: ******************************************************************************** SELECT S_ART, S_SPRACHE, S_MANDANT, S_NR, S_SUB, S_OWNER, S_SATZ FROM SY0001_00005 WHERE S_ART = ? AND S_SPRACHE = ? AND S_MANDANT = ? AND S_NR = ? AND
6
4560
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too slow. I copied the SQL statement from the query and tried executing it from code which then ran in 1 second. To make sure that I didn't miss anything, I copied the SQL statement back into a query and tried running it again. It now also only took 1...
4
7348
by: Praveen_db2 | last post by:
Hi All I am getting strange errors in my db2diag.log can any one tell me what these errors mean?? Following is the code from my db2diag.log ********************************************************************************************* 2006-02-23-17.53.12.253000 Instance:DB2 Node:000 PID:1600(db2syscs.exe) TID:440 Appid:AC10E010.J70A.00E883122250 base sys utilities sqleagnt_sigsegvh Probe:1 Database:DEVM_DB Error in agent...
0
8685
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8612
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8880
tracyyun
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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7743
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6532
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5869
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4625
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3053
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2008
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.