single table query multiple indexes

[sgdbprog1]

Please can someone help me with the following question.
Using mainframe db2 ie on MVS OS/390 for a select against a single
table will db2 use mutiple indexes?

ex:

select *
from table1
where field1 = 'AAA'
and field2 = '222'

assuming there is a seperate index on filed1 and field2 does DB2 have
the capability to use both the field1 and field2 index by searching
through both index and then combining the result of searching both
indexes, rather than using just one of these indexes for one query?
Thank you in advance.

[Mark A]

<sg*******@sbcglobal.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Please can someone help me with the following question.
Using mainframe db2 ie on MVS OS/390 for a select against a single
table will db2 use mutiple indexes?

ex:

select *
from table1
where field1 = 'AAA'
and field2 = '222'

assuming there is a seperate index on filed1 and field2 does DB2 have
the capability to use both the field1 and field2 index by searching
through both index and then combining the result of searching both
indexes, rather than using just one of these indexes for one query?
Thank you in advance.

Yes it has that capability. But whether it does depends on whether DB2
thinks it is faster to do it that way rather than some other way.

[sgdbprog1]

Mark, Thank You,

Please, I have searched IBM.com for info on this question and found
little, if you are aware can you please tell me where ibm may have
documented this capability?

Thanks Again, Stan
Mark A wrote:

<sg*******@sbcglobal.net> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Please can someone help me with the following question.
Using mainframe db2 ie on MVS OS/390 for a select against a single
table will db2 use mutiple indexes?

ex:

select *
from table1
where field1 = 'AAA'
and field2 = '222'

assuming there is a seperate index on filed1 and field2 does DB2 have the capability to use both the field1 and field2 index by searching
through both index and then combining the result of searching both
indexes, rather than using just one of these indexes for one query?
Thank you in advance.
Yes it has that capability. But whether it does depends on whether

DB2 thinks it is faster to do it that way rather than some other way.

[Mark A]

> Mark, Thank You,

Please, I have searched IBM.com for info on this question and found
little, if you are aware can you please tell me where ibm may have
documented this capability?

Thanks Again, Stan

Try the manuals. The Explain facility shows the access path of an SQL
statement and there should be some information about what happens in the
explain output (rows in the PLAN_TABLE) if multiple indexes are used on one
table.

Using the explain is the only way you can know for sure if DB2 will use both
indexes for a particular query, even if DB2 is capable of doing so. Make
sure you first do a table reorg and then run runstats utility and ask for
detailed specifications. Then do the explain.

[Mark A]

> > Please, I have searched IBM.com for info on this question and found

little, if you are aware can you please tell me where ibm may have
documented this capability?

Thanks Again, Stan
Try the manuals. The Explain facility shows the access path of an SQL
statement and there should be some information about what happens in the
explain output (rows in the PLAN_TABLE) if multiple indexes are used on

one table.

Using the explain is the only way you can know for sure if DB2 will use both indexes for a particular query, even if DB2 is capable of doing so. Make
sure you first do a table reorg and then run runstats utility and ask for
detailed specifications. Then do the explain.

I found it for you. In the PLAN_TABLE (updated when you do an explain),
there is column called ACCESSTYPE. Here the values of ACCESSTYPE that
pertain to multiple index access on a single table:

Value Meaning
--- ------------------------------------------
M - Start of multiple index access processing
MX - Indexes are to be scanned for later union or intersection
MI - An intersection (AND) is performed
MU - A union (OR) is performed

[sgdbprog1]

Mark,

This does help, I will persue using the advise you have given.
I Wish you luck, Stan