Is this access path correct?

Harry Garagoski

Is this access path correct?
We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined
with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------
01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski




------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Balachandran Chandrasekaran1

Re: Is this access path correct?
(in response to Harry Garagoski)
Hi Harry,

also, give us the cardinality, frequency information of these indexes and
the columns... from SYSIBM.SYSCOLDIST (If collected), SYSIBM.SYSCOLUMNS
for these two indexes..

Thanks
Balachandran Chandrasekaran




Harry Garagoski <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
02/12/2009 18:40
Please respond to
IDUG DB2-L <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] Is this access path correct?







We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined
with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------

01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski




------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L