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)
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.
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.
"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 :
- 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.