473,325 Members | 2,608 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

select for update access plan

I've got maybe a stupid question, concerning the difference between a
simple select and a select for update ( otherwise identical ).
The plan for such selects - in a DPF enviropnment - are completely
different. Hash joins are used and the select without update returns
fairly quickly, while the select for update involves large nested loop
joins.

Am I missing some obvious point about why a hj would not be used in a
select for update?

Thanks in advance for any guidance - also any pointers to good docs.

Charles
Nov 12 '05 #1
3 5566
"charles" <so*********@yahoo.com> wrote in message
news:8b**************************@posting.google.c om...
I've got maybe a stupid question, concerning the difference between a
simple select and a select for update ( otherwise identical ).
The plan for such selects - in a DPF enviropnment - are completely
different. Hash joins are used and the select without update returns
fairly quickly, while the select for update involves large nested loop
joins.

Am I missing some obvious point about why a hj would not be used in a
select for update?

Thanks in advance for any guidance - also any pointers to good docs.

Charles


Probably related to the different locking that DB2 needs to take on the
rows.
Nov 12 '05 #2

"charles" <so*********@yahoo.com> wrote in message
news:8b**************************@posting.google.c om...
I've got maybe a stupid question, concerning the difference between a
simple select and a select for update ( otherwise identical ).
The plan for such selects - in a DPF enviropnment - are completely
different. Hash joins are used and the select without update returns
fairly quickly, while the select for update involves large nested loop
joins.

Am I missing some obvious point about why a hj would not be used in a
select for update? ===========
Well. It is interesting. It is a very good question. Do you mind posting the
sql stmt? Would you tell us the number of rows of each joined table? And how
do you collect the statitics?

Thanks in advance for any guidance - also any pointers to good docs.

Charles

Nov 12 '05 #3
One reason why the plan will look different is that DB2 may loos ethe
cursor position due to a SORT or TEMP. In this case DB2 needs to
reposition the cursor.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

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

Similar topics

198
by: Michael N. Christoff | last post by:
Java, the software developed by Sun Microsystems in the mid-1990s as a universal operating system for Internet applications, gave NASA a low-cost and easy-to-use option for running Spirit, the...
17
by: Felix | last post by:
Dear Sql Server experts: First off, I am no sql server expert :) A few months ago I put a database into a production environment. Recently, It was brought to my attention that a particular...
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
12
by: TP | last post by:
Here is my problem. I need to display a table about which I have no information except the table name. Using metadata I can somehow show the column names and record values. But my table has 1...
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
29
by: pb648174 | last post by:
I have the following basic statements being executed: Create a temp table, #TempPaging Insert Into #TempPaging (Col1, Col2) Select Col1, Col2 From SomeOtherTable Order By Col2, Col1 Select...
33
by: Peter | last post by:
People are telling me it is bad to put select * from <atable> in a view. I better should list all fields of the table inside the definition of the view. I dont know exactly why but some...
1
by: bughunter | last post by:
simple query select * from "Result" res where (res."QID" = 51541 or res."QID" = 51542) works fine ("SRV-BL"."Result" ~ 900000 rows) and returns 36 rows but update - no! update...
2
by: DuncanIdaho | last post by:
Hi Apologies if this is similar to a (very) recent post. I was wondering if it is possible to execute an update query that contains a select statement on an MS access 2000 database. I have...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.