Helpful folks,

Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
the optimizer makes often seem flaky. But this last example really
floored me. I was hoping someone could explain why I get worse
response time when the optimizer uses two indexes, than when it uses
one. Some context:

Orders table: 5 million+ rows
This table contains
CLIENTID - integer column identifying the the client placing the
order
SEARCHCODE - two-character column defining a flavor of order
CLIENTID has it's own dedicated index, ORDERS02, and SEARCHCODE is the
high-order column of a three-column index, ORDERS13.(And yes, there
are 13 indexes on this %#$@!! table).
The cardinality of SEARCHCODE is extremely low, with roughly 10
distinct values spread across 5 mil rows.
The cardinality of CLIENTID is relatively high (and very high in
relation to SEARCHCODE), with thousands of distinct values.

When I EXPLAIN this SQL statement:

Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR

I'm told that both indexes are used to honor the query, and the
timeron resource number is very low. There are actually 7 rows that
match the criteria in the WHERE clause, and two are returned after the
DISTINCT is employed. However, clock response time is over 3 minutes,
which is unacceptable.


When I modify the query to avoid using the index on SEARCHCODE, using
a technique described by Bonnie Baker, the EXPLAIN does indeed show
only the CLIENTID index being used, and response time is under 10
seconds:

Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR


Why should this be? If the optimizer can determine that two indexes
would be useful, why can't it use them efficiently? Surely it has all
of the distribution statistics available to see the cardinality of the
SEARCHCODE index? And yes, runstats was run 4 days previously, and
then again just before retesting, using this command:
RUNSTATS ON TABLE DB2ADMIN.ORDERS WITH DISTRIBUTION AND DETAILED
INDEXES ALL SHRLEVEL CHANGE

I've included the output of both explains below.
Can anyone shed some light on this? Would a different index
arrangement improve this situation such that the optimizer would not
make such poor choices?

Any feedback would be appreciated.

Sean





Package Name = DB2ADMIN.DYNEXPLN
Prep Date = 2004/02/06
Prep Time = 10:59:53

Bind Timestamp = 2004-02-06-11.09.04.781000

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

Partition Parallel = No
Intra-Partition Parallel = No

Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"

-------------------- SECTION ---------------------------------------
Section = 1


SQL Statement:

Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR


Estimated Cost = 254
Estimated Cardinality = 3

( 5) Index ANDing
| Optimizer Estimate of Set Size: 3
| Index ANDing Bitmap Build
| | Optimizer Estimate of Set Size: 3
( 6) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | | Index Scan: Name = DB2ADMIN.ORDERS02 ID = 3
| | | | Index Columns:
| | | | | 1: CLIENTID (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: 277475
| | | | | Stop Key: Inclusive Value
| | | | | | 1: 277475
| | | | Index-Only Access
| | | | Index Prefetch: None
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| Index ANDing Bitmap Probe
| | Optimizer Estimate of Set Size: 3
( 7) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| | | #Columns = 1
| | | Index Scan: Name = DB2ADMIN.ORDERS13 ID = 14
| | | | Index Columns:
| | | | | 1: SEARCHCODE (Ascending)
| | | | | 2: STATUS (Ascending)
| | | | | 3: TNUMBER (Ascending)
| | | | #Key Columns = 1
| | | | | Start Key: Inclusive Value
| | | | | | 1: '04'
| | | | | Stop Key: Inclusive Value
| | | | | | 1: '04'
| | | | Index-Only Access
| | | | Index Prefetch: Eligible 59
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
( 4) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| #Columns = 3
| Fetch Direct
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Residual Predicate(s)
| | #Predicates = 2
( 3) Insert Into Sorted Temp Table ID = t1
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: DATA2 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 4
| | Row Width = 16
| Piped
| Duplicate Elimination
( 2) Access Temp Table ID = t1
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
( 2) | Return Data to Application
| | #Columns = 1
( 1) Return Data Completion

End of section


Optimizer Plan:

RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
FETCH
( 4)
/--/ \
IXAND Table:
( 5) DB2ADMIN
/--/ \--\ ORDERS
IXSCAN IXSCAN
( 6) ( 7)
/ \ / \
Index: Table: Index: Table:
DB2ADMIN DB2ADMIN DB2ADMIN DB2ADMIN
ORDERS02 ORDERS ORDERS13 ORDERS




DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 SQL Explain Tool

******************** PACKAGE ***************************************

Package Name = DB2ADMIN.DYNEXPLN
Prep Date = 2004/02/06
Prep Time = 11:04:05

Bind Timestamp = 2004-02-06-11.13.16.859000

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

Partition Parallel = No
Intra-Partition Parallel = No

Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"

-------------------- SECTION ---------------------------------------
Section = 1


SQL Statement:

Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR


Estimated Cost = 274
Estimated Cardinality = 6

( 7) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
| #Columns = 1
| Index Scan: Name = DB2ADMIN.ORDERS02 ID = 3
| | Index Columns:
| | | 1: CLIENTID (Ascending)
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | 1: 277475
| | | Stop Key: Inclusive Value
| | | | 1: 277475
| | Index-Only Access
| | Index Prefetch: None
( 7) | | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 410
| | | | | Row Width = 12
| | | | Piped
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
( 6) Sorted Temp Table Completion ID = t1
( 5) List Prefetch RID Preparation
( 3) Insert Into Sorted Temp Table ID = t2
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: DATA2 (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 6
| | Row Width = 16
| Piped
| Duplicate Elimination
( 2) Access Temp Table ID = t2
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
( 2) | Return Data to Application
| | #Columns = 1
( 1) Return Data Completion

End of section


Optimizer Plan:

RETURN
( 1)
|
TBSCAN
( 2)
|
SORT
( 3)
|
RIDSCN
( 5)
|
SORT
( 6)
|
IXSCAN
( 7)
/ \
Index: Table:
DB2ADMIN DB2ADMIN
ORDERS02 ORDERS

"Sean C." <dba_fla@yahoo.com> wrote in message
news:2f9c2d92.0402061427.6b094825@posting.google.c om...[color=blue]
> Helpful folks,
>
> Most of my previous experience with DB2 was on s390 mainframe systems
> and the optimizer on this platform always seemed very predictable and
> consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
> the optimizer makes often seem flaky. But this last example really
> floored me. I was hoping someone could explain why I get worse
> response time when the optimizer uses two indexes, than when it uses
> one. Some context:
>
> Orders table: 5 million+ rows
> This table contains
> CLIENTID - integer column identifying the the client placing the
> order
> SEARCHCODE - two-character column defining a flavor of order
> CLIENTID has it's own dedicated index, ORDERS02, and SEARCHCODE is the
> high-order column of a three-column index, ORDERS13.(And yes, there
> are 13 indexes on this %#$@!! table).
> The cardinality of SEARCHCODE is extremely low, with roughly 10
> distinct values spread across 5 mil rows.
> The cardinality of CLIENTID is relatively high (and very high in
> relation to SEARCHCODE), with thousands of distinct values.
>
> When I EXPLAIN this SQL statement:
>
> Select Distinct DATA2
> from ORDERS
> where SEARCHCODE = '04' and CLIENTID = 277475
> With UR
>
> I'm told that both indexes are used to honor the query, and the
> timeron resource number is very low. There are actually 7 rows that
> match the criteria in the WHERE clause, and two are returned after the
> DISTINCT is employed. However, clock response time is over 3 minutes,
> which is unacceptable.
>
>
> When I modify the query to avoid using the index on SEARCHCODE, using
> a technique described by Bonnie Baker, the EXPLAIN does indeed show
> only the CLIENTID index being used, and response time is under 10
> seconds:
>
> Select Distinct DATA2
> from ORDERS
> where SEARCHCODE = '04' || '' and CLIENTID = 277475
> With UR
>
>
> Why should this be? If the optimizer can determine that two indexes
> would be useful, why can't it use them efficiently? Surely it has all
> of the distribution statistics available to see the cardinality of the
> SEARCHCODE index? And yes, runstats was run 4 days previously, and
> then again just before retesting, using this command:
> RUNSTATS ON TABLE DB2ADMIN.ORDERS WITH DISTRIBUTION AND DETAILED
> INDEXES ALL SHRLEVEL CHANGE
>
> I've included the output of both explains below.
> Can anyone shed some light on this? Would a different index
> arrangement improve this situation such that the optimizer would not
> make such poor choices?
>
> Any feedback would be appreciated.
>
> Sean
>[/color]
When you execute the runstats utility it would be preferable to use the
following options:

RUNSTATS ON TABLE table_name
WITH DISTRIBUTION ON ALL COLUMNS
AND DETAILED INDEXES ALL

This will tell DB2 about the low cardinality of the SEARCHCODE.

For some insane reason (I think IBM is sometimes suicidal), none of the
examples in the Command Reference shows using all the above options. It
should be the default.

This will tell DB2 about the low cardinality of the SEARCHCODE.

Another thing you could try is change the query optimization level. The
default is usually set to 5, but sometimes 7 does better.

To remove the index anding, you have to lower the opt level...


You may see sort problems (sheapthres, sortheap) if it's trying index anding
on 500,000 rows/searchcode. (5M/10)
There is obviously something 'wrong' with the stats.
SEARCHCODE doesn't have a good filter factor. We know but the optimizer
doesn't seem to know.
gui explain should provide more info about the perceived cost of each
operation.

In explain no 2, there is a sort allocation of 410 rows. (for an equality
predicate)
5M/400 +- = 12500 Do you really have +- 400 records per CLIENTID?
Do you have roughly 12500 CLIENTID's? (possible from what you said)

When something weird happens, see the sysstat.* tables and try to find
what obvious things the optimizer may not see.


Mark A did the research on how to fix the sysstat data so i won't redo it.

PM

In article <uF0Vb.42044$9U5.1579668@news20.bellglobal.com>, ("PM
\(pm3iinc-nospam\)" <PM(pm3iinc-nospam)@sympatico.ca>) says...[color=blue]
> To remove the index anding, you have to lower the opt level...
>
>
> You may see sort problems (sheapthres, sortheap) if it's trying index anding
> on 500,000 rows/searchcode. (5M/10)
> There is obviously something 'wrong' with the stats.
> SEARCHCODE doesn't have a good filter factor. We know but the optimizer
> doesn't seem to know.
> gui explain should provide more info about the perceived cost of each
> operation.
>
> In explain no 2, there is a sort allocation of 410 rows. (for an equality
> predicate)
> 5M/400 +- = 12500 Do you really have +- 400 records per CLIENTID?
> Do you have roughly 12500 CLIENTID's? (possible from what you said)
>
> When something weird happens, see the sysstat.* tables and try to find
> what obvious things the optimizer may not see.
>
>
> Mark A did the research on how to fix the sysstat data so i won't redo it.
>
> PM
>
>
>[/color]


Another trick that changes the search path is to add the 'optimize
for x rows' option.
If the queries are executed from a client it might help to specify
the number of rows which fits in one requester block (DBM CFG -->
RQRIOBLK), although I'm not sure if it still makes any difference.
Most likely it depends on the network speed.

dba_fla@yahoo.com (Sean C.) wrote in message news:<2f9c2d92.0402061427.6b094825@posting.google. com>...
[color=blue]
> I've included the output of both explains below.
> Can anyone shed some light on this? Would a different index
> arrangement improve this situation such that the optimizer would not
> make such poor choices?[/color]

db2exfmt of the problem query would give much more detail, including
cardinality estimates at each step, which might help us see better
what DB2 thinks it's doing.


DG

replacing an index on (CLIENTID) with
an index on (CLIENTID, SEARCHCODE) might help, and yes, this is an
expensive solution

P.S. Oracle's bitmap indexes might be very useful in this situation

"AK" <ak_tiredofspam@yahoo.com> wrote in message
news:46e627da.0402070629.76367a6d@posting.google.c om...[color=blue]
> replacing an index on (CLIENTID) with
> an index on (CLIENTID, SEARCHCODE) might help, and yes, this is an
> expensive solution
>
> P.S. Oracle's bitmap indexes might be very useful in this situation[/color]

DB2 will sometimes use bitmaps (automatically) when multiple indexes are
used. In this situation, better statistics are the answer.

Very good observation ...

I don't know the exact cardinality of CLIENTID. This is one of the most
importants thing the optimizer will determine to use INDEX (ONLY) SCAN or
use INDEX ANDing for this case.

If I were you, I would
- Apply the latest FixPak for UDB Version 7.2.
- Runstats again (full collect, not updatable collect)
- Capture the access plan and compare them. (Please don't change dbm/db cfg,
optimization level ...)
- Run both queries ...
- If it is still the same problem, please CONTACT IBM Support ...


"Sean C." <dba_fla@yahoo.com> wrote in message
news:2f9c2d92.0402061427.6b094825@posting.google.c om...[color=blue]
> Helpful folks,
>
> Most of my previous experience with DB2 was on s390 mainframe systems
> and the optimizer on this platform always seemed very predictable and
> consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
> the optimizer makes often seem flaky. But this last example really
> floored me. I was hoping someone could explain why I get worse
> response time when the optimizer uses two indexes, than when it uses
> one. Some context:.
>
> Orders table: 5 million+ rows
> This table contains
> CLIENTID - integer column identifying the the client placing the
> order
> SEARCHCODE - two-character column defining a flavor of order
> CLIENTID has it's own dedicated index, ORDERS02, and SEARCHCODE is the
> high-order column of a three-column index, ORDERS13.(And yes, there
> are 13 indexes on this %#$@!! table).
> The cardinality of SEARCHCODE is extremely low, with roughly 10
> distinct values spread across 5 mil rows.
> The cardinality of CLIENTID is relatively high (and very high in
> relation to SEARCHCODE), with thousands of distinct values.
>
> When I EXPLAIN this SQL statement:
>
> Select Distinct DATA2
> from ORDERS
> where SEARCHCODE = '04' and CLIENTID = 277475
> With UR
>
> I'm told that both indexes are used to honor the query, and the
> timeron resource number is very low. There are actually 7 rows that
> match the criteria in the WHERE clause, and two are returned after the
> DISTINCT is employed. However, clock response time is over 3 minutes,
> which is unacceptable.
>
>
> When I modify the query to avoid using the index on SEARCHCODE, using
> a technique described by Bonnie Baker, the EXPLAIN does indeed show
> only the CLIENTID index being used, and response time is under 10
> seconds:
>
> Select Distinct DATA2
> from ORDERS
> where SEARCHCODE = '04' || '' and CLIENTID = 277475
> With UR
>
>
> Why should this be? If the optimizer can determine that two indexes
> would be useful, why can't it use them efficiently? Surely it has all
> of the distribution statistics available to see the cardinality of the
> SEARCHCODE index? And yes, runstats was run 4 days previously, and
> then again just before retesting, using this command:
> RUNSTATS ON TABLE DB2ADMIN.ORDERS WITH DISTRIBUTION AND DETAILED
> INDEXES ALL SHRLEVEL CHANGE
>
> I've included the output of both explains below.
> Can anyone shed some light on this? Would a different index
> arrangement improve this situation such that the optimizer would not
> make such poor choices?
>
> Any feedback would be appreciated.
>
> Sean
>
>
>
>
>
> Package Name = DB2ADMIN.DYNEXPLN
> Prep Date = 2004/02/06
> Prep Time = 10:59:53
>
> Bind Timestamp = 2004-02-06-11.09.04.781000
>
> Isolation Level = Cursor Stability
> Blocking = Block Unambiguous Cursors
> Query Optimization Class = 5
>
> Partition Parallel = No
> Intra-Partition Parallel = No
>
> Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
>
> -------------------- SECTION ---------------------------------------
> Section = 1
>
>
> SQL Statement:
>
> Select Distinct DATA2
> from ORDERS
> where SEARCHCODE = '04' and CLIENTID = 277475
> With UR
>
>
> Estimated Cost = 254
> Estimated Cardinality = 3
>
> ( 5) Index ANDing
> | Optimizer Estimate of Set Size: 3
> | Index ANDing Bitmap Build
> | | Optimizer Estimate of Set Size: 3
> ( 6) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> | | | #Columns = 1
> | | | Index Scan: Name = DB2ADMIN.ORDERS02 ID = 3
> | | | | Index Columns:
> | | | | | 1: CLIENTID (Ascending)
> | | | | #Key Columns = 1
> | | | | | Start Key: Inclusive Value
> | | | | | | 1: 277475
> | | | | | Stop Key: Inclusive Value
> | | | | | | 1: 277475
> | | | | Index-Only Access
> | | | | Index Prefetch: None
> | | | Isolation Level: Uncommitted Read
> | | | Lock Intents
> | | | | Table: Intent None
> | | | | Row : None
> | Index ANDing Bitmap Probe
> | | Optimizer Estimate of Set Size: 3
> ( 7) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> | | | #Columns = 1
> | | | Index Scan: Name = DB2ADMIN.ORDERS13 ID = 14
> | | | | Index Columns:
> | | | | | 1: SEARCHCODE (Ascending)
> | | | | | 2: STATUS (Ascending)
> | | | | | 3: TNUMBER (Ascending)
> | | | | #Key Columns = 1
> | | | | | Start Key: Inclusive Value
> | | | | | | 1: '04'
> | | | | | Stop Key: Inclusive Value
> | | | | | | 1: '04'
> | | | | Index-Only Access
> | | | | Index Prefetch: Eligible 59
> | | | Isolation Level: Uncommitted Read
> | | | Lock Intents
> | | | | Table: Intent None
> | | | | Row : None
> ( 4) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> | #Columns = 3
> | Fetch Direct
> | Isolation Level: Uncommitted Read
> | Lock Intents
> | | Table: Intent None
> | | Row : None
> | Residual Predicate(s)
> | | #Predicates = 2
> ( 3) Insert Into Sorted Temp Table ID = t1
> | #Columns = 1
> | #Sort Key Columns = 1
> | | Key 1: DATA2 (Ascending)
> | Sortheap Allocation Parameters:
> | | #Rows = 4
> | | Row Width = 16
> | Piped
> | Duplicate Elimination
> ( 2) Access Temp Table ID = t1
> | #Columns = 1
> | Relation Scan
> | | Prefetch: Eligible
> ( 2) | Return Data to Application
> | | #Columns = 1
> ( 1) Return Data Completion
>
> End of section
>
>
> Optimizer Plan:
>
> RETURN
> ( 1)
> |
> TBSCAN
> ( 2)
> |
> SORT
> ( 3)
> |
> FETCH
> ( 4)
> /--/ \
> IXAND Table:
> ( 5) DB2ADMIN
> /--/ \--\ ORDERS
> IXSCAN IXSCAN
> ( 6) ( 7)
> / \ / \
> Index: Table: Index: Table:
> DB2ADMIN DB2ADMIN DB2ADMIN DB2ADMIN
> ORDERS02 ORDERS ORDERS13 ORDERS
>
>
>
>
> DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
> 1991, 2001
> Licensed Material - Program Property of IBM
> IBM DATABASE 2 SQL Explain Tool
>
> ******************** PACKAGE ***************************************
>
> Package Name = DB2ADMIN.DYNEXPLN
> Prep Date = 2004/02/06
> Prep Time = 11:04:05
>
> Bind Timestamp = 2004-02-06-11.13.16.859000
>
> Isolation Level = Cursor Stability
> Blocking = Block Unambiguous Cursors
> Query Optimization Class = 5
>
> Partition Parallel = No
> Intra-Partition Parallel = No
>
> Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
>
> -------------------- SECTION ---------------------------------------
> Section = 1
>
>
> SQL Statement:
>
> Select Distinct DATA2
> from ORDERS
> where SEARCHCODE = '04' || '' and CLIENTID = 277475
> With UR
>
>
> Estimated Cost = 274
> Estimated Cardinality = 6
>
> ( 7) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> | #Columns = 1
> | Index Scan: Name = DB2ADMIN.ORDERS02 ID = 3
> | | Index Columns:
> | | | 1: CLIENTID (Ascending)
> | | #Key Columns = 1
> | | | Start Key: Inclusive Value
> | | | | 1: 277475
> | | | Stop Key: Inclusive Value
> | | | | 1: 277475
> | | Index-Only Access
> | | Index Prefetch: None
> ( 7) | | | Insert Into Sorted Temp Table ID = t1
> | | | | #Columns = 1
> | | | | #Sort Key Columns = 1
> | | | | | Key 1: (Ascending)
> | | | | Sortheap Allocation Parameters:
> | | | | | #Rows = 410
> | | | | | Row Width = 12
> | | | | Piped
> | Isolation Level: Uncommitted Read
> | Lock Intents
> | | Table: Intent None
> | | Row : None
> ( 6) Sorted Temp Table Completion ID = t1
> ( 5) List Prefetch RID Preparation
> ( 3) Insert Into Sorted Temp Table ID = t2
> | #Columns = 1
> | #Sort Key Columns = 1
> | | Key 1: DATA2 (Ascending)
> | Sortheap Allocation Parameters:
> | | #Rows = 6
> | | Row Width = 16
> | Piped
> | Duplicate Elimination
> ( 2) Access Temp Table ID = t2
> | #Columns = 1
> | Relation Scan
> | | Prefetch: Eligible
> ( 2) | Return Data to Application
> | | #Columns = 1
> ( 1) Return Data Completion
>
> End of section
>
>
> Optimizer Plan:
>
> RETURN
> ( 1)
> |
> TBSCAN
> ( 2)
> |
> SORT
> ( 3)
> |
> RIDSCN
> ( 5)
> |
> SORT
> ( 6)
> |
> IXSCAN
> ( 7)
> / \
> Index: Table:
> DB2ADMIN DB2ADMIN
> ORDERS02 ORDERS[/color]

Just think of another point:
Since you said -[color=blue][color=green]
> > The cardinality of CLIENTID is relatively high (and very high in
> > relation to SEARCHCODE), with thousands of distinct values.[/color][/color]
The card(CLIENTID) is RELATIVELY HIGH ... and another case is that it is
possible
"count(*) where CLIENTID = 277475" far less than "COUNT(*) where clientid =
#####"

It is impossible for db2 optimizer that it will choose use one index (only)
scan for searching clientid=277475 and choose index anding for searching
clientid=###### -:(
The same thing for DB2 optimizer on OS/390.



"Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message
news:UiaVb.1821$PY.1013@newssvr26.news.prodigy.com ...[color=blue]
> Very good observation ...
>
> I don't know the exact cardinality of CLIENTID. This is one of the most
> importants thing the optimizer will determine to use INDEX (ONLY) SCAN or
> use INDEX ANDing for this case.
>
> If I were you, I would
> - Apply the latest FixPak for UDB Version 7.2.
> - Runstats again (full collect, not updatable collect)
> - Capture the access plan and compare them. (Please don't change dbm/db[/color]
cfg,[color=blue]
> optimization level ...)
> - Run both queries ...
> - If it is still the same problem, please CONTACT IBM Support ...
>
>
> "Sean C." <dba_fla@yahoo.com> wrote in message
> news:2f9c2d92.0402061427.6b094825@posting.google.c om...[color=green]
> > Helpful folks,
> >
> > Most of my previous experience with DB2 was on s390 mainframe systems
> > and the optimizer on this platform always seemed very predictable and
> > consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
> > the optimizer makes often seem flaky. But this last example really
> > floored me. I was hoping someone could explain why I get worse
> > response time when the optimizer uses two indexes, than when it uses
> > one. Some context:.
> >
> > Orders table: 5 million+ rows
> > This table contains
> > CLIENTID - integer column identifying the the client placing the
> > order
> > SEARCHCODE - two-character column defining a flavor of order
> > CLIENTID has it's own dedicated index, ORDERS02, and SEARCHCODE is the
> > high-order column of a three-column index, ORDERS13.(And yes, there
> > are 13 indexes on this %#$@!! table).
> > The cardinality of SEARCHCODE is extremely low, with roughly 10
> > distinct values spread across 5 mil rows.
> > The cardinality of CLIENTID is relatively high (and very high in
> > relation to SEARCHCODE), with thousands of distinct values.
> >
> > When I EXPLAIN this SQL statement:
> >
> > Select Distinct DATA2
> > from ORDERS
> > where SEARCHCODE = '04' and CLIENTID = 277475
> > With UR
> >
> > I'm told that both indexes are used to honor the query, and the
> > timeron resource number is very low. There are actually 7 rows that
> > match the criteria in the WHERE clause, and two are returned after the
> > DISTINCT is employed. However, clock response time is over 3 minutes,
> > which is unacceptable.
> >
> >
> > When I modify the query to avoid using the index on SEARCHCODE, using
> > a technique described by Bonnie Baker, the EXPLAIN does indeed show
> > only the CLIENTID index being used, and response time is under 10
> > seconds:
> >
> > Select Distinct DATA2
> > from ORDERS
> > where SEARCHCODE = '04' || '' and CLIENTID = 277475
> > With UR
> >
> >
> > Why should this be? If the optimizer can determine that two indexes
> > would be useful, why can't it use them efficiently? Surely it has all
> > of the distribution statistics available to see the cardinality of the
> > SEARCHCODE index? And yes, runstats was run 4 days previously, and
> > then again just before retesting, using this command:
> > RUNSTATS ON TABLE DB2ADMIN.ORDERS WITH DISTRIBUTION AND DETAILED
> > INDEXES ALL SHRLEVEL CHANGE
> >
> > I've included the output of both explains below.
> > Can anyone shed some light on this? Would a different index
> > arrangement improve this situation such that the optimizer would not
> > make such poor choices?
> >
> > Any feedback would be appreciated.
> >
> > Sean
> >
> >
> >
> >
> >
> > Package Name = DB2ADMIN.DYNEXPLN
> > Prep Date = 2004/02/06
> > Prep Time = 10:59:53
> >
> > Bind Timestamp = 2004-02-06-11.09.04.781000
> >
> > Isolation Level = Cursor Stability
> > Blocking = Block Unambiguous Cursors
> > Query Optimization Class = 5
> >
> > Partition Parallel = No
> > Intra-Partition Parallel = No
> >
> > Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
> >
> > -------------------- SECTION ---------------------------------------
> > Section = 1
> >
> >
> > SQL Statement:
> >
> > Select Distinct DATA2
> > from ORDERS
> > where SEARCHCODE = '04' and CLIENTID = 277475
> > With UR
> >
> >
> > Estimated Cost = 254
> > Estimated Cardinality = 3
> >
> > ( 5) Index ANDing
> > | Optimizer Estimate of Set Size: 3
> > | Index ANDing Bitmap Build
> > | | Optimizer Estimate of Set Size: 3
> > ( 6) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> > | | | #Columns = 1
> > | | | Index Scan: Name = DB2ADMIN.ORDERS02 ID = 3
> > | | | | Index Columns:
> > | | | | | 1: CLIENTID (Ascending)
> > | | | | #Key Columns = 1
> > | | | | | Start Key: Inclusive Value
> > | | | | | | 1: 277475
> > | | | | | Stop Key: Inclusive Value
> > | | | | | | 1: 277475
> > | | | | Index-Only Access
> > | | | | Index Prefetch: None
> > | | | Isolation Level: Uncommitted Read
> > | | | Lock Intents
> > | | | | Table: Intent None
> > | | | | Row : None
> > | Index ANDing Bitmap Probe
> > | | Optimizer Estimate of Set Size: 3
> > ( 7) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> > | | | #Columns = 1
> > | | | Index Scan: Name = DB2ADMIN.ORDERS13 ID = 14
> > | | | | Index Columns:
> > | | | | | 1: SEARCHCODE (Ascending)
> > | | | | | 2: STATUS (Ascending)
> > | | | | | 3: TNUMBER (Ascending)
> > | | | | #Key Columns = 1
> > | | | | | Start Key: Inclusive Value
> > | | | | | | 1: '04'
> > | | | | | Stop Key: Inclusive Value
> > | | | | | | 1: '04'
> > | | | | Index-Only Access
> > | | | | Index Prefetch: Eligible 59
> > | | | Isolation Level: Uncommitted Read
> > | | | Lock Intents
> > | | | | Table: Intent None
> > | | | | Row : None
> > ( 4) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> > | #Columns = 3
> > | Fetch Direct
> > | Isolation Level: Uncommitted Read
> > | Lock Intents
> > | | Table: Intent None
> > | | Row : None
> > | Residual Predicate(s)
> > | | #Predicates = 2
> > ( 3) Insert Into Sorted Temp Table ID = t1
> > | #Columns = 1
> > | #Sort Key Columns = 1
> > | | Key 1: DATA2 (Ascending)
> > | Sortheap Allocation Parameters:
> > | | #Rows = 4
> > | | Row Width = 16
> > | Piped
> > | Duplicate Elimination
> > ( 2) Access Temp Table ID = t1
> > | #Columns = 1
> > | Relation Scan
> > | | Prefetch: Eligible
> > ( 2) | Return Data to Application
> > | | #Columns = 1
> > ( 1) Return Data Completion
> >
> > End of section
> >
> >
> > Optimizer Plan:
> >
> > RETURN
> > ( 1)
> > |
> > TBSCAN
> > ( 2)
> > |
> > SORT
> > ( 3)
> > |
> > FETCH
> > ( 4)
> > /--/ \
> > IXAND Table:
> > ( 5) DB2ADMIN
> > /--/ \--\ ORDERS
> > IXSCAN IXSCAN
> > ( 6) ( 7)
> > / \ / \
> > Index: Table: Index: Table:
> > DB2ADMIN DB2ADMIN DB2ADMIN DB2ADMIN
> > ORDERS02 ORDERS ORDERS13 ORDERS
> >
> >
> >
> >
> > DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
> > 1991, 2001
> > Licensed Material - Program Property of IBM
> > IBM DATABASE 2 SQL Explain Tool
> >
> > ******************** PACKAGE ***************************************
> >
> > Package Name = DB2ADMIN.DYNEXPLN
> > Prep Date = 2004/02/06
> > Prep Time = 11:04:05
> >
> > Bind Timestamp = 2004-02-06-11.13.16.859000
> >
> > Isolation Level = Cursor Stability
> > Blocking = Block Unambiguous Cursors
> > Query Optimization Class = 5
> >
> > Partition Parallel = No
> > Intra-Partition Parallel = No
> >
> > Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
> >
> > -------------------- SECTION ---------------------------------------
> > Section = 1
> >
> >
> > SQL Statement:
> >
> > Select Distinct DATA2
> > from ORDERS
> > where SEARCHCODE = '04' || '' and CLIENTID = 277475
> > With UR
> >
> >
> > Estimated Cost = 274
> > Estimated Cardinality = 6
> >
> > ( 7) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> > | #Columns = 1
> > | Index Scan: Name = DB2ADMIN.ORDERS02 ID = 3
> > | | Index Columns:
> > | | | 1: CLIENTID (Ascending)
> > | | #Key Columns = 1
> > | | | Start Key: Inclusive Value
> > | | | | 1: 277475
> > | | | Stop Key: Inclusive Value
> > | | | | 1: 277475
> > | | Index-Only Access
> > | | Index Prefetch: None
> > ( 7) | | | Insert Into Sorted Temp Table ID = t1
> > | | | | #Columns = 1
> > | | | | #Sort Key Columns = 1
> > | | | | | Key 1: (Ascending)
> > | | | | Sortheap Allocation Parameters:
> > | | | | | #Rows = 410
> > | | | | | Row Width = 12
> > | | | | Piped
> > | Isolation Level: Uncommitted Read
> > | Lock Intents
> > | | Table: Intent None
> > | | Row : None
> > ( 6) Sorted Temp Table Completion ID = t1
> > ( 5) List Prefetch RID Preparation
> > ( 3) Insert Into Sorted Temp Table ID = t2
> > | #Columns = 1
> > | #Sort Key Columns = 1
> > | | Key 1: DATA2 (Ascending)
> > | Sortheap Allocation Parameters:
> > | | #Rows = 6
> > | | Row Width = 16
> > | Piped
> > | Duplicate Elimination
> > ( 2) Access Temp Table ID = t2
> > | #Columns = 1
> > | Relation Scan
> > | | Prefetch: Eligible
> > ( 2) | Return Data to Application
> > | | #Columns = 1
> > ( 1) Return Data Completion
> >
> > End of section
> >
> >
> > Optimizer Plan:
> >
> > RETURN
> > ( 1)
> > |
> > TBSCAN
> > ( 2)
> > |
> > SORT
> > ( 3)
> > |
> > RIDSCN
> > ( 5)
> > |
> > SORT
> > ( 6)
> > |
> > IXSCAN
> > ( 7)
> > / \
> > Index: Table:
> > DB2ADMIN DB2ADMIN
> > ORDERS02 ORDERS[/color]
>
>[/color]

> >[color=blue][color=green]
> > P.S. Oracle's bitmap indexes might be very useful in this situation[/color]
>
> DB2 will sometimes use bitmaps (automatically) when multiple indexes are
> used. In this situation, better statistics are the answer.[/color]

there seems to be a big performance difference between using a
pre-existing Oracle's bitmap index and DB2 creating a similar
structure on the fly nad discarding it after the query has completed

"AK" <ak_tiredofspam@yahoo.com> wrote in message
news:46e627da.0402071148.7f15137d@posting.google.c om...[color=blue][color=green][color=darkred]
> > >
> > > P.S. Oracle's bitmap indexes might be very useful in this situation[/color]
> >
> > DB2 will sometimes use bitmaps (automatically) when multiple indexes are
> > used. In this situation, better statistics are the answer.[/color]
>
> there seems to be a big performance difference between using a
> pre-existing Oracle's bitmap index and DB2 creating a similar
> structure on the fly nad discarding it after the query has completed[/color]

Actually there is no need for a bitmap index here. The problem is that DB2
is using two indexes, when it should use only 1 index. The likely problem is
that the statistics are not accurate and DB2 is does not know about the low
cardinality of SEARCHCODE. The person who started the tread has already
confirmed that the query runs very quickly when only one index is used.

With regard to the pre-existing bitmap index, I am no expert on this
feature, but I assume it costs more to maintain such an index in real-time,
so there is no free lunch. But again, the bottom line is that one index
works quite well if he can DB2 to do that.

Even you gave us a lot of description. There is still one thing that no one
here can know clearly - the distribution of data values for column -
CLIENTID. I thought the value of this column might rather unevenly
distribute. You can change the default db parameter ***NUM_QUANTILES*** , to
help the optimizer to understand the data distribution.
BTW, I don't think INDEX-ANDing is always the better index access.

"Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message
news:tGaVb.1826$PY.940@newssvr26.news.prodigy.com. ..[color=blue]
> Just think of another point:
> Since you said -[color=green][color=darkred]
> > > The cardinality of CLIENTID is relatively high (and very high in
> > > relation to SEARCHCODE), with thousands of distinct values.[/color][/color]
> The card(CLIENTID) is RELATIVELY HIGH ... and another case is that it is
> possible
> "count(*) where CLIENTID = 277475" far less than "COUNT(*) where clientid[/color]
=[color=blue]
> #####"
>
> It is impossible for db2 optimizer that it will choose use one index[/color]
(only)[color=blue]
> scan for searching clientid=277475 and choose index anding for searching
> clientid=###### -:(
> The same thing for DB2 optimizer on OS/390.
>
>
>
> "Fan Ruo Xin" <fanruox@sbcglobal.net> wrote in message
> news:UiaVb.1821$PY.1013@newssvr26.news.prodigy.com ...[color=green]
> > Very good observation ...
> >
> > I don't know the exact cardinality of CLIENTID. This is one of the most
> > importants thing the optimizer will determine to use INDEX (ONLY) SCAN[/color][/color]
or[color=blue][color=green]
> > use INDEX ANDing for this case.
> >
> > If I were you, I would
> > - Apply the latest FixPak for UDB Version 7.2.
> > - Runstats again (full collect, not updatable collect)
> > - Capture the access plan and compare them. (Please don't change dbm/db[/color]
> cfg,[color=green]
> > optimization level ...)
> > - Run both queries ...
> > - If it is still the same problem, please CONTACT IBM Support ...
> >
> >
> > "Sean C." <dba_fla@yahoo.com> wrote in message
> > news:2f9c2d92.0402061427.6b094825@posting.google.c om...[color=darkred]
> > > Helpful folks,
> > >
> > > Most of my previous experience with DB2 was on s390 mainframe systems
> > > and the optimizer on this platform always seemed very predictable and
> > > consistent. Since moving to a WinNT/UDB 7.2 environment, the choices
> > > the optimizer makes often seem flaky. But this last example really
> > > floored me. I was hoping someone could explain why I get worse
> > > response time when the optimizer uses two indexes, than when it uses
> > > one. Some context:.
> > >
> > > Orders table: 5 million+ rows
> > > This table contains
> > > CLIENTID - integer column identifying the the client placing the
> > > order
> > > SEARCHCODE - two-character column defining a flavor of order
> > > CLIENTID has it's own dedicated index, ORDERS02, and SEARCHCODE is the
> > > high-order column of a three-column index, ORDERS13.(And yes, there
> > > are 13 indexes on this %#$@!! table).
> > > The cardinality of SEARCHCODE is extremely low, with roughly 10
> > > distinct values spread across 5 mil rows.
> > > The cardinality of CLIENTID is relatively high (and very high in
> > > relation to SEARCHCODE), with thousands of distinct values.
> > >
> > > When I EXPLAIN this SQL statement:
> > >
> > > Select Distinct DATA2
> > > from ORDERS
> > > where SEARCHCODE = '04' and CLIENTID = 277475
> > > With UR
> > >
> > > I'm told that both indexes are used to honor the query, and the
> > > timeron resource number is very low. There are actually 7 rows that
> > > match the criteria in the WHERE clause, and two are returned after the
> > > DISTINCT is employed. However, clock response time is over 3 minutes,
> > > which is unacceptable.
> > >
> > >
> > > When I modify the query to avoid using the index on SEARCHCODE, using
> > > a technique described by Bonnie Baker, the EXPLAIN does indeed show
> > > only the CLIENTID index being used, and response time is under 10
> > > seconds:
> > >
> > > Select Distinct DATA2
> > > from ORDERS
> > > where SEARCHCODE = '04' || '' and CLIENTID = 277475
> > > With UR
> > >
> > >
> > > Why should this be? If the optimizer can determine that two indexes
> > > would be useful, why can't it use them efficiently? Surely it has all
> > > of the distribution statistics available to see the cardinality of the
> > > SEARCHCODE index? And yes, runstats was run 4 days previously, and
> > > then again just before retesting, using this command:
> > > RUNSTATS ON TABLE DB2ADMIN.ORDERS WITH DISTRIBUTION AND DETAILED
> > > INDEXES ALL SHRLEVEL CHANGE
> > >
> > > I've included the output of both explains below.
> > > Can anyone shed some light on this? Would a different index
> > > arrangement improve this situation such that the optimizer would not
> > > make such poor choices?
> > >
> > > Any feedback would be appreciated.
> > >
> > > Sean
> > >
> > >
> > >
> > >
> > >
> > > Package Name = DB2ADMIN.DYNEXPLN
> > > Prep Date = 2004/02/06
> > > Prep Time = 10:59:53
> > >
> > > Bind Timestamp = 2004-02-06-11.09.04.781000
> > >
> > > Isolation Level = Cursor Stability
> > > Blocking = Block Unambiguous Cursors
> > > Query Optimization Class = 5
> > >
> > > Partition Parallel = No
> > > Intra-Partition Parallel = No
> > >
> > > Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
> > >
> > > -------------------- SECTION ---------------------------------------
> > > Section = 1
> > >
> > >
> > > SQL Statement:
> > >
> > > Select Distinct DATA2
> > > from ORDERS
> > > where SEARCHCODE = '04' and CLIENTID = 277475
> > > With UR
> > >
> > >
> > > Estimated Cost = 254
> > > Estimated Cardinality = 3
> > >
> > > ( 5) Index ANDing
> > > | Optimizer Estimate of Set Size: 3
> > > | Index ANDing Bitmap Build
> > > | | Optimizer Estimate of Set Size: 3
> > > ( 6) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> > > | | | #Columns = 1
> > > | | | Index Scan: Name = DB2ADMIN.ORDERS02 ID = 3
> > > | | | | Index Columns:
> > > | | | | | 1: CLIENTID (Ascending)
> > > | | | | #Key Columns = 1
> > > | | | | | Start Key: Inclusive Value
> > > | | | | | | 1: 277475
> > > | | | | | Stop Key: Inclusive Value
> > > | | | | | | 1: 277475
> > > | | | | Index-Only Access
> > > | | | | Index Prefetch: None
> > > | | | Isolation Level: Uncommitted Read
> > > | | | Lock Intents
> > > | | | | Table: Intent None
> > > | | | | Row : None
> > > | Index ANDing Bitmap Probe
> > > | | Optimizer Estimate of Set Size: 3
> > > ( 7) | | Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> > > | | | #Columns = 1
> > > | | | Index Scan: Name = DB2ADMIN.ORDERS13 ID = 14
> > > | | | | Index Columns:
> > > | | | | | 1: SEARCHCODE (Ascending)
> > > | | | | | 2: STATUS (Ascending)
> > > | | | | | 3: TNUMBER (Ascending)
> > > | | | | #Key Columns = 1
> > > | | | | | Start Key: Inclusive Value
> > > | | | | | | 1: '04'
> > > | | | | | Stop Key: Inclusive Value
> > > | | | | | | 1: '04'
> > > | | | | Index-Only Access
> > > | | | | Index Prefetch: Eligible 59
> > > | | | Isolation Level: Uncommitted Read
> > > | | | Lock Intents
> > > | | | | Table: Intent None
> > > | | | | Row : None
> > > ( 4) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> > > | #Columns = 3
> > > | Fetch Direct
> > > | Isolation Level: Uncommitted Read
> > > | Lock Intents
> > > | | Table: Intent None
> > > | | Row : None
> > > | Residual Predicate(s)
> > > | | #Predicates = 2
> > > ( 3) Insert Into Sorted Temp Table ID = t1
> > > | #Columns = 1
> > > | #Sort Key Columns = 1
> > > | | Key 1: DATA2 (Ascending)
> > > | Sortheap Allocation Parameters:
> > > | | #Rows = 4
> > > | | Row Width = 16
> > > | Piped
> > > | Duplicate Elimination
> > > ( 2) Access Temp Table ID = t1
> > > | #Columns = 1
> > > | Relation Scan
> > > | | Prefetch: Eligible
> > > ( 2) | Return Data to Application
> > > | | #Columns = 1
> > > ( 1) Return Data Completion
> > >
> > > End of section
> > >
> > >
> > > Optimizer Plan:
> > >
> > > RETURN
> > > ( 1)
> > > |
> > > TBSCAN
> > > ( 2)
> > > |
> > > SORT
> > > ( 3)
> > > |
> > > FETCH
> > > ( 4)
> > > /--/ \
> > > IXAND Table:
> > > ( 5) DB2ADMIN
> > > /--/ \--\ ORDERS
> > > IXSCAN IXSCAN
> > > ( 6) ( 7)
> > > / \ / \
> > > Index: Table: Index: Table:
> > > DB2ADMIN DB2ADMIN DB2ADMIN DB2ADMIN
> > > ORDERS02 ORDERS ORDERS13 ORDERS
> > >
> > >
> > >
> > >
> > > DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
> > > 1991, 2001
> > > Licensed Material - Program Property of IBM
> > > IBM DATABASE 2 SQL Explain Tool
> > >
> > > ******************** PACKAGE ***************************************
> > >
> > > Package Name = DB2ADMIN.DYNEXPLN
> > > Prep Date = 2004/02/06
> > > Prep Time = 11:04:05
> > >
> > > Bind Timestamp = 2004-02-06-11.13.16.859000
> > >
> > > Isolation Level = Cursor Stability
> > > Blocking = Block Unambiguous Cursors
> > > Query Optimization Class = 5
> > >
> > > Partition Parallel = No
> > > Intra-Partition Parallel = No
> > >
> > > Function Path = "SYSIBM", "SYSFUN", "DB2ADMIN"
> > >
> > > -------------------- SECTION ---------------------------------------
> > > Section = 1
> > >
> > >
> > > SQL Statement:
> > >
> > > Select Distinct DATA2
> > > from ORDERS
> > > where SEARCHCODE = '04' || '' and CLIENTID = 277475
> > > With UR
> > >
> > >
> > > Estimated Cost = 274
> > > Estimated Cardinality = 6
> > >
> > > ( 7) Access Table Name = DB2ADMIN.ORDERS ID = 6,4
> > > | #Columns = 1
> > > | Index Scan: Name = DB2ADMIN.ORDERS02 ID = 3
> > > | | Index Columns:
> > > | | | 1: CLIENTID (Ascending)
> > > | | #Key Columns = 1
> > > | | | Start Key: Inclusive Value
> > > | | | | 1: 277475
> > > | | | Stop Key: Inclusive Value
> > > | | | | 1: 277475
> > > | | Index-Only Access
> > > | | Index Prefetch: None
> > > ( 7) | | | Insert Into Sorted Temp Table ID = t1
> > > | | | | #Columns = 1
> > > | | | | #Sort Key Columns = 1
> > > | | | | | Key 1: (Ascending)
> > > | | | | Sortheap Allocation Parameters:
> > > | | | | | #Rows = 410
> > > | | | | | Row Width = 12
> > > | | | | Piped
> > > | Isolation Level: Uncommitted Read
> > > | Lock Intents
> > > | | Table: Intent None
> > > | | Row : None
> > > ( 6) Sorted Temp Table Completion ID = t1
> > > ( 5) List Prefetch RID Preparation
> > > ( 3) Insert Into Sorted Temp Table ID = t2
> > > | #Columns = 1
> > > | #Sort Key Columns = 1
> > > | | Key 1: DATA2 (Ascending)
> > > | Sortheap Allocation Parameters:
> > > | | #Rows = 6
> > > | | Row Width = 16
> > > | Piped
> > > | Duplicate Elimination
> > > ( 2) Access Temp Table ID = t2
> > > | #Columns = 1
> > > | Relation Scan
> > > | | Prefetch: Eligible
> > > ( 2) | Return Data to Application
> > > | | #Columns = 1
> > > ( 1) Return Data Completion
> > >
> > > End of section
> > >
> > >
> > > Optimizer Plan:
> > >
> > > RETURN
> > > ( 1)
> > > |
> > > TBSCAN
> > > ( 2)
> > > |
> > > SORT
> > > ( 3)
> > > |
> > > RIDSCN
> > > ( 5)
> > > |
> > > SORT
> > > ( 6)
> > > |
> > > IXSCAN
> > > ( 7)
> > > / \
> > > Index: Table:
> > > DB2ADMIN DB2ADMIN
> > > ORDERS02 ORDERS[/color]
> >
> >[/color]
>
>
>[/color]

"Mark A" <ma@switchboard.net> wrote in message news:<A7_Ub.480$an2.77922@news.uswest.net>...[color=blue]
> RUNSTATS ON TABLE table_name
> WITH DISTRIBUTION ON ALL COLUMNS
> AND DETAILED INDEXES ALL[/color]

Am I right that the option 'ON ALL COLUMNS' is only available in UDB
v8, not v7? It doesn't work on my DB (v7).

"Almund Sebi" <nyook@gmx.net> wrote in message
news:947ecaa1.0402090000.4708d41a@posting.google.c om...[color=blue]
> "Mark A" <ma@switchboard.net> wrote in message[/color]
news:<A7_Ub.480$an2.77922@news.uswest.net>...[color=blue][color=green]
> > RUNSTATS ON TABLE table_name
> > WITH DISTRIBUTION ON ALL COLUMNS
> > AND DETAILED INDEXES ALL[/color]
>
> Am I right that the option 'ON ALL COLUMNS' is only available in UDB
> v8, not v7? It doesn't work on my DB (v7).[/color]

That is correct. Just leave out the phrase 'ON ALL COLUMNS' for version 7.

Wow, thanks for all the feedback, guys. The latest fixpack applied on
the server is FP5, which technically makes it V7.3, I suppose.
Unfortunately, this version does not allow the 'ON ALL COLUMNS' clause
on the RUNSTATS command. From the doc for V7 found here:

http://www-306.ibm.com/cgi-bin/db2ww....htm#HDRCMD098

I have to assume the runstats command I listed above is the most
comprehensive data gathering flavor of the command. Is this correct?
Many of the responses implied that not all of the necessary statistics
had been gathered, but I am unaware of any means beyond fudging the
stats tables directly, that could give me more accurate statistics.

Below I've tried to supply some of the info mentioned in replies so
far. I was surprised to see so few entries for CLIENTID in the sysstat
table (see below). Could this be the problem?

Here is a more detailed description of the data distribution:

Number of distinct CLIENTID's: 9223

Number ORDERs with a CLIENTID of '277475': 11730

Number of distinct SEARCHCODEs: 73
I was obviously off the mark with my estimate of this cardinality,
however tha majority of the distribution is across a small subset of
the values. The following data is from a query directly against the
table, with count(*) and group by.

Breakdown of SEARCHCODE distribution:
SEARCHCODE 2
---------- -----------
08 2039829
11 649924
12 608037
03 433443
41 307586
60 215766
05 111580
10 89033
51 64315
01 59991
09 54754
88 40005
04 38697
78 37235
81 34786
50 28932
83 26164
68 20312
74 20242
06 17615
80 15499
37 12063
48 10271
71 10217
47 9288
62 9093
33 9048
02 8133
34 8042
28 7392
13 6687
63 4969
27 4778
66 4201
07 2710
35 2547
61 2044
39 1605
52 1558
32 1150
44 1068
15 793
92 779
36 667
53 606
38 605
67 534
86 439
77 281
64 259
16 183
49 180
14 174
73 147
65 119
21 99
23 98
76 85
89 70
25 63
17 49
99 42
20 32
69 30
19 27
24 13
87 13
56 10
18 9
22 7
84 4
54 3
91 3



Below are the two output reports from the DB2EXFMT utility for the two
queries in question, followed by a query against the SYSSTAT table for
the releveant columns:


Here is the output of the db2exfmt utility for the first query:

DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 07.02.3
SOURCE_NAME: SQLLF000
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2004-02-09-09.34.00.750001
EXPLAIN_REQUESTER: DB2ADMIN

Database Context:
----------------
Parallelism: None
CPU Speed: 6.179837e-007
Comm Speed: 0
Buffer Pool size: 151136
Sort Heap size: 1024
Database Heap size: 4096
Lock List size: 1024
Maximum Lock List: 20
Average Applications: 50
Locks Available: 23142

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability



---------------- STATEMENT 1 SECTION 65 ----------------
QUERYNO: 100
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' and CLIENTID = 277475
With UR


Optimized Statement:
-------------------
SELECT DISTINCT Q1.DATA2 AS "DATA2"
FROM DB2ADMIN.ORDERS AS Q1
WHERE (Q1.CLIENTID = 277475) AND (Q1.SEARCHCODE = '04')

Access Plan:
-----------
Total Cost: 265.69
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
3.16741
TBSCAN
( 2)
265.69
68.747
|
3.16741
SORT
( 3)
265.688
68.747
|
3.16741
FETCH
( 4)
265.686
68.747
/----+---\
3.16741 5.0301e+006
IXAND TABLE: DB2ADMIN
( 5) ORDERS
218.723
64.0549
/------+-----\
412.105 38661
IXSCAN IXSCAN
( 6) ( 7)
20.4841 197.392
2 62.0549
| |
5.0301e+006 5.0301e+006
INDEX: DB2ADMIN INDEX: DB2ADMIN
ORDERS02 ORDERS13




1) RETURN: (Return Result)
Cumulative Total Cost: 265.69
Cumulative CPU Cost: 6.92502e+007
Cumulative I/O Cost: 68.747
Cumulative Re-Total Cost: 42.4248
Cumulative Re-CPU Cost: 6.86503e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 265.689
Estimated Bufferpool Buffers: 0

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v7.1.0.55 : n011211
ENVVAR : (Environment Variable)
DB2_HASH_JOIN = ON

Input Streams:
-------------
9) From Operator #2

Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+DATA2(A)


2) TBSCAN: (Table Scan)
Cumulative Total Cost: 265.69
Cumulative CPU Cost: 6.92502e+007
Cumulative I/O Cost: 68.747
Cumulative Re-Total Cost: 42.4248
Cumulative Re-CPU Cost: 6.86503e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 265.689
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD

Input Streams:
-------------
8) From Operator #3

Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+DATA2(A)


Output Streams:
--------------
9) To Operator #1

Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+DATA2(A)


3) SORT : (Sort)
Cumulative Total Cost: 265.688
Cumulative CPU Cost: 6.92479e+007
Cumulative I/O Cost: 68.747
Cumulative Re-Total Cost: 42.4233
Cumulative Re-CPU Cost: 6.8648e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 265.688
Estimated Bufferpool Buffers: 305.376

Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
TRUE
NUMROWS : (Estimated number of rows)
4
ROWWIDTH: (Estimated width of rows)
16
SORTKEY : (Sort Key column)
1: DATA2(A)
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
TRUE

Input Streams:
-------------
7) From Operator #4

Estimated number of rows: 3.16741
Number of columns: 4
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$+DATA2+CLIENTID+SEARCHCODE


Output Streams:
--------------
8) To Operator #2

Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+DATA2(A)


4) FETCH : (Fetch)
Cumulative Total Cost: 265.686
Cumulative CPU Cost: 6.92433e+007
Cumulative I/O Cost: 68.747
Cumulative Re-Total Cost: 42.4233
Cumulative Re-CPU Cost: 6.8648e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 106.805
Estimated Bufferpool Buffers: 305.376

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
4
MAXPAGES: (Maximum pages for prefetch)
4
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
TABLOCK : (Table Lock intent)
INTENT NONE

Predicates:
----------
2) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005

Predicate Text:
--------------
(Q1.CLIENTID = 277475)

3) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0.00768594

Predicate Text:
--------------
(Q1.SEARCHCODE = '04')


Input Streams:
-------------
5) From Operator #5

Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$

6) From Object DB2ADMIN.ORDERS

Estimated number of rows: 5.0301e+006
Number of columns: 3
Subquery predicate ID: Not Applicable

Column Names:
------------
+DATA2+CLIENTID+SEARCHCODE


Output Streams:
--------------
7) To Operator #3

Estimated number of rows: 3.16741
Number of columns: 4
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$+DATA2+CLIENTID+SEARCHCODE


5) IXAND : (Index ANDing)
Cumulative Total Cost: 218.723
Cumulative CPU Cost: 6.91745e+007
Cumulative I/O Cost: 64.0549
Cumulative Re-Total Cost: 42.4185
Cumulative Re-CPU Cost: 6.86402e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 96.7953
Estimated Bufferpool Buffers: 143.435

Input Streams:
-------------
2) From Operator #6

Estimated number of rows: 412.105
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$+CLIENTID

4) From Operator #7

Estimated number of rows: 38661
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$+SEARCHCODE


Output Streams:
--------------
5) To Operator #4

Estimated number of rows: 3.16741
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$


6) IXSCAN: (Index Scan)
Cumulative Total Cost: 20.4841
Cumulative CPU Cost: 783315
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.446094
Cumulative Re-CPU Cost: 721854
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 20.0458
Estimated Bufferpool Buffers: 3

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE

Predicates:
----------
2) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005

Predicate Text:
--------------
(Q1.CLIENTID = 277475)

2) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005

Predicate Text:
--------------
(Q1.CLIENTID = 277475)


Input Streams:
-------------
1) From Object DB2ADMIN.ORDERS02

Estimated number of rows: 5.0301e+006
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$+CLIENTID


Output Streams:
--------------
2) To Operator #5

Estimated number of rows: 412.105
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$+CLIENTID


7) IXSCAN: (Index Scan)
Cumulative Total Cost: 197.392
Cumulative CPU Cost: 6.70214e+007
Cumulative I/O Cost: 62.0549
Cumulative Re-Total Cost: 41.1259
Cumulative Re-CPU Cost: 6.65486e+007
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 20.046
Estimated Bufferpool Buffers: 140.435

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
60
PREFETCH: (Type of Prefetch)
SEQUENTIAL
ROWLOCK : (Row Lock intent)
NONE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT NONE

Predicates:
----------
3) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0.00768594

Predicate Text:
--------------
(Q1.SEARCHCODE = '04')

3) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0.00768594

Predicate Text:
--------------
(Q1.SEARCHCODE = '04')


Input Streams:
-------------
3) From Object DB2ADMIN.ORDERS13

Estimated number of rows: 5.0301e+006
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$+SEARCHCODE


Output Streams:
--------------
4) To Operator #5

Estimated number of rows: 38661
Number of columns: 2
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$+SEARCHCODE


Objects Used in Access Plan:
---------------------------

Schema: DB2ADMIN
Name: ORDERS02
Type: Index
Time of creation: 2003-11-30-12.17.19.593001
Last statistics update: 2004-02-06-12.58.42.468000
Number of columns: 1
Number of rows: 5030097
Width of rows: -1
Number of buffer pool pages: 1134599
Distinct row values: No
Tablespace name: ORDERSI
Tablespace overhead: 9.700000
Tablespace transfer rate: 0.300000
Prefetch page count: 8
Container extent page count: 64
Index clustering statistic: 0.738917
Index leaf pages: 7149
Index tree levels: 3
Index full key cardinality: 9217
Index first key cardinality: 9217
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 7073
Index page density: 99
Base Table Schema: DB2ADMIN
Base Table Name: ORDERS
Columns in index:
CLIENTID

Schema: DB2ADMIN
Name: ORDERS13
Type: Index
Time of creation: 2003-11-30-12.36.55.343001
Last statistics update: 2004-02-06-12.58.42.468000
Number of columns: 3
Number of rows: 5030097
Width of rows: -1
Number of buffer pool pages: 1134599
Distinct row values: No
Tablespace name: ORDERSI
Tablespace overhead: 9.700000
Tablespace transfer rate: 0.300000
Prefetch page count: 8
Container extent page count: 64
Index clustering statistic: 0.342897
Index leaf pages: 7871
Index tree levels: 3
Index full key cardinality: 184626
Index first key cardinality: 73
Index first 2 keys cardinality: 408
Index first 3 keys cardinality: 184626
Index first 4 keys cardinality: -1
Index sequential pages: 7720
Index page density: 99
Base Table Schema: DB2ADMIN
Base Table Name: ORDERS
Columns in index:
SEARCHCODE
STATUS
TNUMBER

Schema: DB2ADMIN
Name: ORDERS
Type: Table
Time of creation: 2002-09-22-10.50.24.437001
Last statistics update: 2004-02-06-12.58.42.468000
Number of columns: 89
Number of rows: 5030097
Width of rows: 30
Number of buffer pool pages: 1134599
Distinct row values: No
Tablespace name: ORDERST
Tablespace overhead: 9.700000
Tablespace transfer rate: 0.300000
Prefetch page count: 128
Container extent page count: 64
Table overflow record count: 567




And here's the output of DB2EXFMT for the second query:




DB2 Universal Database Version 7.2, 5622-044 (c) Copyright IBM Corp.
1991, 2001
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool



******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 07.02.3
SOURCE_NAME: SQLLF000
SOURCE_SCHEMA: NULLID
EXPLAIN_TIME: 2004-02-09-09.34.41.203001
EXPLAIN_REQUESTER: DB2ADMIN

Database Context:
----------------
Parallelism: None
CPU Speed: 6.179837e-007
Comm Speed: 0
Buffer Pool size: 151136
Sort Heap size: 1024
Database Heap size: 4096
Lock List size: 1024
Maximum Lock List: 20
Average Applications: 50
Locks Available: 23142

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability



---------------- STATEMENT 1 SECTION 65 ----------------
QUERYNO: 200
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
Select Distinct DATA2
from ORDERS
where SEARCHCODE = '04' || '' and CLIENTID = 277475
With UR


Optimized Statement:
-------------------
SELECT DISTINCT Q1.DATA2 AS "DATA2"
FROM DB2ADMIN.ORDERS AS Q1
WHERE (Q1.CLIENTID = 277475) AND (Q1.SEARCHCODE = ('04' || ''))

Access Plan:
-----------
Total Cost: 274.691
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
5.64527
TBSCAN
( 2)
274.691
163.773
|
5.64527
SORT
( 3)
274.689
163.773
|
5.64527
FETCH
( 4)
274.685
163.773
/----+---\
412.105 5.0301e+006
RIDSCN TABLE: DB2ADMIN
( 5) ORDERS
20.7172
2
|
412.105
SORT
( 6)
20.7164
2
|
412.105
IXSCAN
( 7)
20.4841
2
|
5.0301e+006
INDEX: DB2ADMIN
ORDERS02




1) RETURN: (Return Result)
Cumulative Total Cost: 274.691
Cumulative CPU Cost: 3.24999e+006
Cumulative I/O Cost: 163.773
Cumulative Re-Total Cost: 1.17398
Cumulative Re-CPU Cost: 1.89969e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 274.69
Estimated Bufferpool Buffers: 0

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v7.1.0.55 : n011211
ENVVAR : (Environment Variable)
DB2_HASH_JOIN = ON

Input Streams:
-------------
8) From Operator #2

Estimated number of rows: 5.64527
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+DATA2(A)


2) TBSCAN: (Table Scan)
Cumulative Total Cost: 274.691
Cumulative CPU Cost: 3.24999e+006
Cumulative I/O Cost: 163.773
Cumulative Re-Total Cost: 1.17398
Cumulative Re-CPU Cost: 1.89969e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 274.69
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
SCANDIR : (Scan Direction)
FORWARD

Input Streams:
-------------
7) From Operator #3

Estimated number of rows: 5.64527
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+DATA2(A)


Output Streams:
--------------
8) To Operator #1

Estimated number of rows: 5.64527
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+DATA2(A)


3) SORT : (Sort)
Cumulative Total Cost: 274.689
Cumulative CPU Cost: 3.24704e+006
Cumulative I/O Cost: 163.773
Cumulative Re-Total Cost: 1.17215
Cumulative Re-CPU Cost: 1.89674e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 274.689
Estimated Bufferpool Buffers: 178.431

Arguments:
---------
DUPLWARN: (Duplicates Warning flag)
TRUE
NUMROWS : (Estimated number of rows)
6
ROWWIDTH: (Estimated width of rows)
16
SORTKEY : (Sort Key column)
1: DATA2(A)
TEMPSIZE: (Temporary Table Page Size)
4096
UNIQUE : (Uniqueness required flag)
TRUE

Input Streams:
-------------
6) From Operator #4

Estimated number of rows: 5.64527
Number of columns: 4
Subquery predicate ID: Not Applicable

Column Names:
------------
+$RID$+DATA2+CLIENTID+SEARCHCODE


Output Streams:
--------------
7) To Operator #2

Estimated number of rows: 5.64527
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------
+DATA2(A)


4) FETCH : (Fetch)
Cumulative Total Cost: 274.685
Cumulative CPU Cost: 3.24046e+006
Cumulative I/O Cost: 163.773
Cumulative Re-Total Cost: 1.17215
Cumulative Re-CPU Cost: 1.89674e+006
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 73.3488
Estimated Bufferpool Buffers: 178.431

Arguments:
---------
MAX RIDS: (Maximum RIDs per list prefetch request)
512
MAXPAGES: (Maximum pages for prefetch)
149
PREFETCH: (Type of Prefetch)
LIST
ROWLOCK : (Row Lock intent)
NONE
TABLOCK : (Table Lock intent)
INTENT NONE

Predicates:
----------
2) Sargable Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 8.19278e-005

Predicate Text:
--------------
(Q1.CLIENTID = 277475)

3) Sargable Predicate
Relational Operator: Equal (=)
Subquery Input Required: Yes
Filter Factor: 0.0136986

Predicate Text:
--------------
(Q1.SEARCHCODE = ('04' || ''))


Input Streams:
-------------
4) From Operator #5

Estimated number of rows: 412.105
Number of columns: 1
Subquery predicate ID: Not Applicable

Column Names:
------------