Hi

We're running DB2 V8 on AIX.

We've got a table, containing about 3200000 rows, PK=ENS4009_SR_NR, clustered by ENS4009_SR_NR.

We're experimenting with the query as below. The aim is to give back the first 10 rows to the user. The problem is when the volume of rows associated with ENS4009_PARTY_ID is high (like in this case 32000+), the query is very slow.

I've created an index, similar to test.IDX811271104310000, but with the SR_NR as well
(ens4009_party_id asc, ens4301_pty_tp_ind asc, ens4009_sr_nr desc), but for some reason db2 doesn't want to use that.

Any ideas would be greatly appreciated!

Thanx
am
==================== 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", "srcdb1"


SQL Statement:

select *
from test.ENS4009_SRV_REQ this_
where this_.ENS4009_PARTY_ID='AAXOK123'and this_.ENS4301_PTY_TP_IND=
'C'and this_.ENS4009_SR_NR <=2147483647
order by this_.ENS4009_SR_NR desc
fetch first 10 rows only


Section Code Page = 1208

Estimated Cost = 5399.725586
Estimated Cardinality = 10.000000

Index ANDing
| Optimizer Estimate of Set Size: 32782
| Index ANDing Bitmap Build Using Row IDs
| | Optimizer Estimate of Set Size: 32782
| | Access Table Name = test.ENS4009_SRV_REQ ID = 4,4
| | | Index Scan: Name = test.IDX811271104310000 ID = 36
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: ENS4009_PARTY_ID (Ascending)
| | | | | 2: ENS4301_PTY_TP_IND (Descending)
| | | #Columns = 0
| | | #Key Columns = 2
| | | | Start Key: Inclusive Value
| | | | | | 1: 'AAXOK123'
| | | | | | 2: 'C'
| | | | Stop Key: Inclusive Value
| | | | | | 1: 'AAXOK123'
| | | | | | 2: 'C'
| | | Index-Only Access
| | | Index Prefetch: Eligible 84
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| Index ANDing Bitmap Probe Using Row IDs
| | Optimizer Estimate of Set Size: 32782
| | Access Table Name = test.ENS4009_SRV_REQ ID = 4,4
| | | Index Scan: Name = test.ENS4009_PTY_STS_SR ID = 8
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: ENS4009_PARTY_ID (Ascending)
| | | | | 2: ENS4006_SR_STS_CD (Ascending)
| | | | | 3: ENS4009_SR_NR (Descending)
| | | #Columns = 0
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: 'AAXOK123'
| | | | Stop Key: Inclusive Value
| | | | | | 1: 'AAXOK123'
| | | Index-Only Access
| | | Index Prefetch: Eligible 261
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Index Predicate(s)
| | | | #Predicates = 1
Insert Into Sorted Temp Table ID = t1
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 32783
| | Row Width = 12
| Piped
| Duplicate Elimination
List Prefetch Preparation
| Access Table Name = test.ENS4009_SRV_REQ ID = 4,4
| | #Columns = 64
| | Fetch Using Prefetched List
| | | Prefetch: 1172 Pages
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 3
| | | Insert Into Sorted Temp Table ID = t2
| | | | #Columns = 64
| | | | #Sort Key Columns = 1
| | | | | Key 1: ENS4009_SR_NR (Descending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 10
| | | | | Row Width = 408
| | | | | Sort Limited To Estimated Row Count
| | | | Piped
Sorted Temp Table Completion ID = t2
Access Temp Table ID = t2
| #Columns = 64
| Relation Scan
| | Prefetch: Eligible
Return Data to Application
| #Columns = 66

End of section

ENS4009_PARTY_ID should be the clustering index, not the PK (I would probably try it with this one column by itself or maybe first 2 columns only (leaving out DESC). It is very rare that a PK that is only a single column would be the proper clustering index. Change the clustering index and reorg the table and try again. Be warned that if this table has a lot of insert activity, you will need to reorg it on a regular basis.

Sorry. I should consider more.

Hi Marcus

Thanx for the advise. I agree and will test this to see the impact on other queries as well. Unfortunately this table is being accessed in different ways with different criteria. It is one of the main tables used in a call centre application.

I would have thought that db2 would first identify the rows by using all columns in the new index (party_id, party_tp_cd, sr_nr) and then fetch the data for the first 10 rows only. But it looks like all the data is fetched first (33000 rows), and then only the first 10 rows are returned.

I've changed the query as follows to get the best performance:

with temp1 as
(select ens4009_sr_nr
from test.ENS4009_SRV_REQ
where ENS4009_PARTY_ID='AAXOK123' and ENS4301_PTY_TP_IND='C'
and ENS4009_SR_NR <= 2147483647
order by ENS4009_SR_NR desc fetch first 10 rows only)select
this_.*
from test.ENS4009_SRV_REQ this_, temp1 tmp
where this_.ENS4009_SR_NR = tmp.ens4009_sr_nr

The challenge is to get Hibernate to generate this type of sql!

Regards
am

The fetch first 10 rows only applies only after the order by, so DB2 has to formulate the entire answer-set first.

Easy, create your userviews like that and expose that to hibernate instead of bare-tables. A userview is nothing more or less than a SQL-subroutine.