Range Join Performance

[Lionel Horn]

I have a query that executes in 1 second in Oracle but executes in 16 seconds in MySQL. The query performs a range "join" instead of an equijoin.

Here is the query:

--------- cut here --------------

SELECT COUNT(*)
FROM
(
SELECT
O.ID, O.ARBBASE ARBBASE_FROM, D.ARBBASE ARBBASE_TO, O.W, O.C
FROM
(
select
_batch_load.id ID, _batch_load.ozip O, _batch_load.W W, _batch_load.C C, _arbrnge.arbbase ARBBASE
from
rateware._batch_load,
rateware._arbrnge
where
_batch_load.ozip between _arbrnge.zip_lo and _arbrnge.zip_hi
group by
_batch_load.id, _batch_load.ozip, _batch_load.dzip, _batch_load.W, _batch_load.C, _arbrnge.arbbase
order by
_batch_load.id, _arbrnge.arbbase
) O,
(
select
_batch_load.id ID, _batch_load.dzip D, _batch_load.W W, _batch_load.C C, _arbrnge.arbbase ARBBASE
from
rateware._batch_load,
rateware._arbrnge
where
_batch_load.dzip between _arbrnge.zip_lo and _arbrnge.zip_hi
group by
_batch_load.id, _batch_load.ozip, _batch_load.dzip, _batch_load.W, _batch_load.C, _arbrnge.arbbase
order by
_batch_load.id, _arbrnge.arbbase
) D
WHERE
O.ID = D.ID
and o.arbbase != d.arbbase
ORDER BY
O.ID,
O.ARBBASE,
D.ARBBASE
) T

--------- cut here --------------

The _arbrnge table cannot be directly joined to the _batch_load table because _arbrgne contains a range of values. For each record in _batch_load, I have to find if it falls within a range defined in _arbrnge. This is why I used the BETWEEN clause.