468,110 Members | 1,553 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,110 developers. It's quick & easy.

Browsing whole table?


Folks,

Our client has a program that browses whole table from begining to end.
The table has 1 million rows in it. REORGCHK does not show any problems.
It has unique index defined on KEY0 column.

If I use SELECT statement without OPTIMIZE FOR clause, then it uses
temporary table to sort the data, but if I use OPTIMIZE clause then it
uses index access without temporary table. If I use OPTIMIZE FOR more than
700 rows then it uses temporary table again.

How can I influence the optimizer not to use temporary table?

If I use OPTIMIZE FOR clause then what will happen to the performance
after 700th row?

I will not be able to modify the program.

Thanks.

# db2expln -d test -f /home/db2modl/tmp/test.ddl -t

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

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

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "DB2MODL"
SQL Statement:

SELECT REC
FROM MODL.EADMFN00
WHERE KEY0 >=?
ORDER BY KEY0 ASC
FOR
FETCH ONLY
Section Code Page = 819

Estimated Cost = 107.394234
Estimated Cardinality = 1856.888184

Access Table Name = MODL.EADMFN00 ID = 6,22
| Index Scan: Name = MODL.EADMFN00_ASC ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: KEY0 (Ascending)
| #Columns = 0
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: None
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 1
| | | #Sort Key Columns = 1
| | | | Key 1: (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 1857
| | | | Row Width = 12
| | | Piped
| | | Duplicate Elimination
Sorted Temp Table Completion ID = t1
List Prefetch Preparation
| Access Table Name = MODL.EADMFN00 ID = 6,22
| | #Columns = 2
| | Fetch Using Prefetched List
| | | Prefetch: 50 Pages
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
| | | Insert Into Sorted Temp Table ID = t2
| | | | #Columns = 2
| | | | #Sort Key Columns = 1
| | | | | Key 1: KEY0 (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 1857
| | | | | Row Width = 744
| | | | Piped
Sorted Temp Table Completion ID = t2
Access Temp Table ID = t2
| #Columns = 2
| Relation Scan
| | Prefetch: Eligible
| Sargable Predicate(s)
| | Return Data to Application
| | | #Columns = 1
Return Data Completion

End of section
# db2expln -d test -f /home/db2modl/tmp/test1.ddl -t

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

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

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = No
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "DB2MODL"
SQL Statement:

SELECT REC
FROM MODL.EADMFN00
WHERE KEY0 >=?
ORDER BY KEY0 ASC
OPTIMIZE
FOR 700 ROWS
FOR
FETCH ONLY
Section Code Page = 819

Estimated Cost = 238.005768
Estimated Cardinality = 1856.888184

Access Table Name = MODL.EADMFN00 ID = 6,22
| Index Scan: Name = MODL.EADMFN00_ASC ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: KEY0 (Ascending)
| #Columns = 2
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: End of Index
| Data Prefetch: Eligible 20
| Index Prefetch: Eligible 20
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Return Data to Application
| | | #Columns = 1
Return Data Completion

End of section
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Apr 30 '07 #1
4 3382
WHERE KEY0 >=?
The optimizer ha to assume that on average you are selecting half the rows.
There are two way for you to improve that.
1. Use the REOPT(ONCE) or REOPT(ALWAYS) attributes to either train or
recompile teh query with real values.
2. use SELECTIVITY e.g: WHERE KEY0 >= ? SELECTIVITY 0.01 if you want to
bias the the assumption.
Note that you need to set db2set DB2_SELECTIVITY=ALL to get this to work

OPTIMIZE FOR n ROWS discourages the optimizer from using TEMPs or SORTs
because they delay the time it takes for teh first row to be returned.
In your case (possibly because your key isn't linked to a clustering
index) the optimizer figures that after hundreds or so random I/Os over
the table it has expended more resources than if it fetched the whole
table and then sorted it so it might as well..

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 30 '07 #2
Thank Serge, I will give it a try.

While stranded on information super highway Serge Rielau wrote:
WHERE KEY0 >=?
The optimizer ha to assume that on average you are selecting half the rows.
There are two way for you to improve that.
1. Use the REOPT(ONCE) or REOPT(ALWAYS) attributes to either train or
recompile teh query with real values.
2. use SELECTIVITY e.g: WHERE KEY0 >= ? SELECTIVITY 0.01 if you want to
bias the the assumption.
Note that you need to set db2set DB2_SELECTIVITY=ALL to get this to work

OPTIMIZE FOR n ROWS discourages the optimizer from using TEMPs or SORTs
because they delay the time it takes for teh first row to be returned.
In your case (possibly because your key isn't linked to a clustering
index) the optimizer figures that after hundreds or so random I/Os over
the table it has expended more resources than if it fetched the whole
table and then sorted it so it might as well..

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
Apr 30 '07 #3

Serge,

When I use the SELECTIVITY clause with dynamic SQL it works, but when I
add it to my C program that uses host variable I get error while
running db2 PREP command:

LINE MESSAGES FOR BTRERE00.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
179 SQL20046N SELECTIVITY clause following "H00001
" can only be specified for a valid user-defined predicate.
SQLSTATE=428E5


While stranded on information super highway Serge Rielau wrote:
WHERE KEY0 >=?
The optimizer ha to assume that on average you are selecting half the rows.
There are two way for you to improve that.
1. Use the REOPT(ONCE) or REOPT(ALWAYS) attributes to either train or
recompile teh query with real values.
2. use SELECTIVITY e.g: WHERE KEY0 >= ? SELECTIVITY 0.01 if you want to
bias the the assumption.
Note that you need to set db2set DB2_SELECTIVITY=ALL to get this to work

OPTIMIZE FOR n ROWS discourages the optimizer from using TEMPs or SORTs
because they delay the time it takes for teh first row to be returned.
In your case (possibly because your key isn't linked to a clustering
index) the optimizer figures that after hundreds or so random I/Os over
the table it has expended more resources than if it fetched the whole
table and then sorted it so it might as well..

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.
May 3 '07 #4
Hemant Shah wrote:
Serge,

When I use the SELECTIVITY clause with dynamic SQL it works, but when I
add it to my C program that uses host variable I get error while
running db2 PREP command:

LINE MESSAGES FOR BTRERE00.sqc
------ --------------------------------------------------------------------
SQL0060W The "C" precompiler is in progress.
179 SQL20046N SELECTIVITY clause following "H00001
" can only be specified for a valid user-defined predicate.
SQLSTATE=428E5
db2set DB2_SELECTIVITY=ALL
db2stop
db2start
Try again..
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
May 3 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by redneck_kiwi | last post: by
4 posts views Thread by Jurgen Haan | last post: by
19 posts views Thread by Martin Eyles | last post: by
reply views Thread by Frnak McKenney | last post: by
2 posts views Thread by Joe Kovac | last post: by
1 post views Thread by martens | last post: by
3 posts views Thread by Dan99 | last post: by
1 post views Thread by didacticone | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.