Effective use of DB2 Query.

Parth · New User Joined: 25 Mar 2010 Posts: 1 Location: Mumbai

Hi,

In One of my DB2 table has two index,

Let's suppose,
1). System-id (Only two different values 'T' and 'C' for this column)
2). Cycle-no (25 Different values available for this column which are '01' ,'02'......,'25')

Note: Guess we have equal probability of getting System-id 'T' or 'C', same as equal probability for getting '01' or '02' or .... '25'
Means, If table is having 10 Lacs records then System-id = 'T' records will be 5 lacs(10 Lacs/2 different values) and System-id = 'C' records will be 5 lacs .
Same as for each cycle-no, the number of records will be around 40,000 (10 Lacs / 25 different values).

Let's suppose, we want to extract rows for which System-id = 'C' and Cycle-no = '10'

For above conditions, which where clause will be Time effective and cost effective.
1). Select * from table v1 where System-id = 'C' and Cycle-no = '10'
2). Select * from table v1 where Cycle-no = '10' and System-id = 'C' (Means sequence changed from 1st)

Also,

In above query ,will it use both the indexes or will it use the first index specified in where clause.

Kindly advice on this for the optimization of query.

Please let me know in case of more information.

Thanks,
Parth Patel

guptae · Posted: Thu Mar 25, 2010 3:14 pm

Parth,

I think for first query it will use first index and for second one it will choose second one.

Why dont you just run the query using Explain & check the result.

GuyC · Posted: Thu Mar 25, 2010 4:08 pm

my guesses :
If he only uses one index : it will be the one with the highest filtering factor, ie. Cycle-no
It also depends on the Clustering factor of each index.
likely DB2 will do a multi-ix AND
but even a Tablescan is a possibility.

An index with two different values (fullkeycard) for 1.000.000 rows is just stupid.

why not expand the indexes with the other column?
one index on (cycle-no, system-id) and one on (system-id,cycleno)
I guess you need both indexes for R.I. or other queries.

Gopal Tripathi · New User Joined: 16 Sep 2009 Posts: 15 Location: Hyd,Ind

Did you consider any one of your indexes to be clustered, i think it will be preferred over if db2 does a multi-ix scan.

Guy C could u please elaborate a bit-

"An index with two different values (fullkeycard) for 1,000,000 rows is just stupid."

GuyC · Posted: Fri Mar 26, 2010 1:56 pm

"An index with two different values (fullkeycard) for 1,000,000 rows is just stupid."

The index would contain 2 values, each followed by a huge RID-list.

with 2 distinct values you have to read 50% of rows. Unless it is highly clustered, you will need to read 90% of the pages.
If you would do it via an index you would need to read 50% of the index leaf-pages + 90% of the data pages.

you could only use that index for three purposes :
- R.I.
- clustering
- select count(*) from tab where code = ?
select code,count(*) from tab group by code

With some investigation, you could expand that index with one or more other data columns so that some of your queries are index-only, or more matching, and still keep the functionality of R.I ,clustering,select count(*) .
It would only increase the size of the index a bit.

PeterHolland · Posted: Fri Mar 26, 2010 2:02 pm

Redbook : DB2 9 for z/OS Performance Topics has a lot of info about this.