Messieurs
J'ai une question concernant deux résultats de requette qui a priori devarit renvoyer les même résultats dans le même ordre.
Cependant ce n'est pas le cas est je ne comprends pas trop pourquoi.
1/ select * from schema.table where id_table >= '47003431387477' and id_table <= '47003431387501' for update with ur;
2 / select * from schema.table where id_table >= '47003431387477' and id_table <= '47003431387501' with ur;
Faite un test. ci joint les visual explain
______________________________________________________________
1/
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
******************** PACKAGE ***************************************
Package Name = "DB2ADMIN"."DYNEXPLN" Version = ""
Prep Date = 2008/08/18
Prep Time = 15:40:49
Bind Timestamp = 2008-08-18-15.44.13.659000
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "DB2ADMIN"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
DECLARE C1 CURSOR
FOR
select *
from schema.table
where id_table >='47003431387477'and id_table <='
47003431387501'
for update
with ur
Statement Isolation Level = Uncommitted Read
Section Code Page = 1252
Estimated Cost = 113,562294
Estimated Cardinality = 1,561668
( 5) Access Table Name = schema.table ID = 2,84
| Index Scan: Name = schema.ID_TABLE ID = 11
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: id_table (Ascending)
| #Columns = 0
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: '47003431387477'
| | Stop Key: Inclusive Value
| | | | 1: '47003431387501'
| Index-Only Access
| Index Prefetch: None
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
( 5) | | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 1
| | | #Sort Key Columns = 1
| | | | Key 1: (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 2
| | | | Row Width = 12
| | | Piped
| | | Duplicate Elimination
( 4) Sorted Temp Table Completion ID = t1
( 3) List Prefetch Preparation
( 3) | Access Table Name = schema.table ID = 2,84
| | #Columns = 17
| | Fetch Using Prefetched List
| | | Prefetch: 1 Pages
| | Lock Intents
| | | Table: Intent Exclusive
| | | Row : Update
| | Sargable Predicate(s)
| | | #Predicates = 2
( 1) Return Data to Application
| #Columns = 17
End of section
Optimizer Plan:
RETURN
( 1)
|
FETCH
(----)
/ \
RIDSCN Table:
( 3) schema.table
|
SORT
( 4)
|
IXSCAN
( 5)
/ \
Index: Table:
schema. schema.
id_table table
___________________________________________________________
2/
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
******************** PACKAGE ***************************************
Package Name = "DB2ADMIN"."DYNEXPLN" Version = ""
Prep Date = 2008/08/18
Prep Time = 15:41:11
Bind Timestamp = 2008-08-18-15.44.35.127001
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "DB2ADMIN"
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
DECLARE C1 CURSOR
FOR
select *
from schema.table
where ID_TABLE >='47003431387477'and ID_TABLE <='
47003431387501'
with ur
Statement Isolation Level = Uncommitted Read
Section Code Page = 1252
Estimated Cost = 100,035774
Estimated Cardinality = 1,561668
( 2) Access Table Name = schema.table ID = 2,84
| Index Scan: Name = schema. ID_TABLE ID = 11
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ID_TABLE (Ascending)
| #Columns = 17
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: '47003431387477'
| | Stop Key: Inclusive Value
| | | | 1: '47003431387501'
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
( 2) | | Return Data to Application
| | | #Columns = 17
( 1) Return Data Completion
End of section
Optimizer Plan:
RETURN
( 1)
|
FETCH
( 2)
/ \
IXSCAN Table:
( 2) schema.table
|
Index:
schema.ID_TABLE
__________________________________________________
__________________________________________________
ID_TABLE est la clé primaire de mon fichier.
Le sql for update n'as pas de raison d'être utilisé ainsi mais je le test car le probléme vient d'un curseur en mise a jour dans un programme.
d'ou le fait de testé le for update ici hors d'un curseur en m'apercevant que le résultat est le même qu'en debug sur mon pgm.
Si qqn a une explication, je suis preneur.
Merci d'avance.
Partager