access plan with sequence in params

[bughunter]

Hmm... Simple select through primary key

1. SELECT REFERENCE FROM CCTL.DOCSTSREG WHERE DOCID=?
2. SELECT REFERENCE FROM CCTL.DOCSTSREG WHERE DOCID=(PREVVAL FOR
CCTL.sq_docidstsreg)

1 - no problems, 2 - TABLESCAN! Why?!!

1.Access Plan:
-----------
Total Cost: 50.0199
Query Degree: 0

Rows
RETURN
( 1)
Cost
I/O
|
1
FETCH
( 2)
50.0199
2
/---+--\
1 23411
IXSCAN TABLE: CCTL
( 3) DOCSTSREG
25.017
1
|
23411
INDEX: CCTL
PK_DOCSTSREG

2. Optimized Statement:
-------------------
SELECT Q1.REFERENCE AS "REFERENCE"
FROM CCTL.DOCSTSREG AS Q1
WHERE (Q1.DOCID = INTEGER($INTERNAL_FUNC$()))

Access Plan:
-----------
Total Cost: 4869.35
Query Degree: 0

Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
4869.35
1236
|
23411
TABLE: CCTL
DOCSTSREG

-----------------------------------------------------
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08020"
with
level identifier "03010106".
Informational tokens are "DB2 v8.1.7.445", "s040812", "WR21342", and
FixPak
"7".

Andy

[Serge Rielau]

bughunter@ru wrote:

Hmm... Simple select through primary key

1. SELECT REFERENCE FROM CCTL.DOCSTSREG WHERE DOCID=?
2. SELECT REFERENCE FROM CCTL.DOCSTSREG WHERE DOCID=(PREVVAL FOR
CCTL.sq_docidstsreg)

1 - no problems, 2 - TABLESCAN! Why?!!

I think(!) this will be fixed in FP9. Call support to be sure.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab