| Computers Forum Index » Computer - Databases - IBM DB2 » Strange optimizer behaviour when using OR... |
|
Page 1 of 1 |
|
| Author |
Message |
| Frederik Engelen... |
 Posted: Wed Oct 21, 2009 9:29 am |
|
|
|
Guest
|
I noticed some query behaviour that appears very strange to me. I want
to delete rows from a very big table that apply to one of these two
predicates:
delete from BIGTABLE bt
where
exists(
select 1
from small_temp_table stt
where bt.id = stt.id
)
OR
(
bigtable_parentid = ?
and exists (
select 1
from conf_bigtable cbt
where cbt.bigtable_recordtype = bt.recordtype
and cbt.policy = 'DELETE'
)
);
When using only one predicate, the optimizer gives a filter factor of
0.000007 for the first one and a filter factor of 0.0065 for the
second one. The order of magnitude for these estimations is correct.
But when OR'ing them, the optimizer predicts a combined filter factor
of no less than 0.5, resulting in a table scan of the very large
table. This in contrast with the seperate executions, where the
appropriate indexes are used.
Does anyone have an idea why the optimizer would increase the filter
factor so much by using an OR?
But when OR'ing them, the optimizer predicts a combined filter factor
of no less than 0.5, resulting in a table scan of the very large
table. This in contrast with the seperate executions, where the
appropriate indexes are used.
Does anyone have an idea why the optimizer would increase the filter
factor so much by using an OR? I'm on 9.5 FP4, btw. |
|
|
| Back to top |
|
|
|
| Lennart... |
| Posted: Wed Oct 21, 2009 4:11 pm |
|
|
|
Guest
|
On 21 Okt, 11:29, Frederik Engelen <engelenfrede... at (no spam) gmail.com> wrote:
Quote: I noticed some query behaviour that appears very strange to me. I want
to delete rows from a very big table that apply to one of these two
predicates:
delete from BIGTABLE bt
where
exists(
select 1
from small_temp_table stt
where bt.id = stt.id
)
OR
(
bigtable_parentid = ?
and exists (
select 1
from conf_bigtable cbt
where cbt.bigtable_recordtype = bt.recordtype
and cbt.policy = 'DELETE'
)
);
When using only one predicate, the optimizer gives a filter factor of
0.000007 for the first one and a filter factor of 0.0065 for the
second one. The order of magnitude for these estimations is correct.
But when OR'ing them, the optimizer predicts a combined filter factor
of no less than 0.5, resulting in a table scan of the very large
table. This in contrast with the seperate executions, where the
appropriate indexes are used.
Does anyone have an idea why the optimizer would increase the filter
factor so much by using an OR?
But when OR'ing them, the optimizer predicts a combined filter factor
of no less than 0.5, resulting in a table scan of the very large
table. This in contrast with the seperate executions, where the
appropriate indexes are used.
Does anyone have an idea why the optimizer would increase the filter
factor so much by using an OR? I'm on 9.5 FP4, btw.
Just curios, if you rewrite your predicates from:
a or b
to:
not ( not a and not b )
do you get another filter factor from the optimizer?
/Lennart |
|
|
| Back to top |
|
|
|
| Frederik Engelen... |
| Posted: Thu Oct 22, 2009 12:33 pm |
|
|
|
Guest
|
Quote: Just curios, if you rewrite your predicates from:
a or b
to:
not ( not a and not b )
do you get another filter factor from the optimizer?
/Lennart
It's the same, although the access plan is slightly different.
By executing both delete statements seperately, I fixed my problem.
But I still find it strange, though. I'm willing to give more details
if someone is interested (but rather not in public).
--
Frederik
Btw, Data Studio makes it quasi impossible to compare the access plans
for multiple statements since a new Visual Explain opens in the same
window. If someone from the Data Studio team is reading, having this
fixed would be one reason less to go back to db2cc! |
|
|
| Back to top |
|
|
|
| Frederik Engelen... |
| Posted: Fri Oct 23, 2009 11:24 am |
|
|
|
Guest
|
Quote: My brief experience with Data Studio 2.2 suggested to me that the new Visual
Explain feature is a pale imitation of the one in db2cc. Is that your
conclusion also? (and not just on the issue of multiple windows open).
Well, I only used it because db2cc gave me a CLI error on a specific
query :-)
Everything I need seem to be there, but the UI could be improved. The
node attributes tree might be a good idea but expanding it each time
to get to the predicates is very tiresome. Having some basic
information immediately available would be much more efficient. |
|
|
| Back to top |
|
|
|
| Mark A... |
| Posted: Fri Oct 23, 2009 2:22 pm |
|
|
|
Guest
|
"Frederik Engelen" <engelenfrederik at (no spam) gmail.com> wrote in message
news:0e762630-66a7-4331-b2bb-458be7d715dd at (no spam) s6g2000vbp.googlegroups.com...
Quote: Btw, Data Studio makes it quasi impossible to compare the access plans
for multiple statements since a new Visual Explain opens in the same
window. If someone from the Data Studio team is reading, having this
fixed would be one reason less to go back to db2cc!
My brief experience with Data Studio 2.2 suggested to me that the new Visual
Explain feature is a pale imitation of the one in db2cc. Is that your
conclusion also? (and not just on the issue of multiple windows open). |
|
|
| Back to top |
|
|
|
|
