Oracle - odd behavior with nested select/join
-
Here is a strange result that I am getting with Oracle. Unless I wrap the select from TABLE1 as shown (specifying "rownum > 0"), the query never seems to complete. However, when it is included, it only takes a second or two. It doesn't make any sense to me, because all that it seems to do is force Oracle to evaluate the nested select with the alias "a" before performing the join. I had thought that placing it in parenthesis would do that automatically. Perhaps the Oracle optimizer does something wonky that this compensates for..? Scratching my head here, because all that the "where" condition adds is basically saying "give me any rows that you find".
select COUNTRY_NAME, count(*) TALLY from
(
select * from (
select IP_SOURCE_NUM from TABLE1
where log_date between '01-JAN-2012' and date '01-FEB-2012'
and IP_SOURCE_NUM > 0
) where rownum > 0
) a
JOIN ip_geo b on b.ip_from =
(
select max(ip_from) as ip_from_match from ip_geo
where ip_from <= a.IP_SOURCE_NUM
)
group by COUNTRY_NAME
order by count(*) desc -
Here is a strange result that I am getting with Oracle. Unless I wrap the select from TABLE1 as shown (specifying "rownum > 0"), the query never seems to complete. However, when it is included, it only takes a second or two. It doesn't make any sense to me, because all that it seems to do is force Oracle to evaluate the nested select with the alias "a" before performing the join. I had thought that placing it in parenthesis would do that automatically. Perhaps the Oracle optimizer does something wonky that this compensates for..? Scratching my head here, because all that the "where" condition adds is basically saying "give me any rows that you find".
select COUNTRY_NAME, count(*) TALLY from
(
select * from (
select IP_SOURCE_NUM from TABLE1
where log_date between '01-JAN-2012' and date '01-FEB-2012'
and IP_SOURCE_NUM > 0
) where rownum > 0
) a
JOIN ip_geo b on b.ip_from =
(
select max(ip_from) as ip_from_match from ip_geo
where ip_from <= a.IP_SOURCE_NUM
)
group by COUNTRY_NAME
order by count(*) descWhile passing up the opportunity to make sarcasic remarks about a 'wonky' Oracle you might be better served by asking Tom[^] as it sounds like a rather deep and very specific Oracle issue.
Never underestimate the power of human stupidity RAH
-
Here is a strange result that I am getting with Oracle. Unless I wrap the select from TABLE1 as shown (specifying "rownum > 0"), the query never seems to complete. However, when it is included, it only takes a second or two. It doesn't make any sense to me, because all that it seems to do is force Oracle to evaluate the nested select with the alias "a" before performing the join. I had thought that placing it in parenthesis would do that automatically. Perhaps the Oracle optimizer does something wonky that this compensates for..? Scratching my head here, because all that the "where" condition adds is basically saying "give me any rows that you find".
select COUNTRY_NAME, count(*) TALLY from
(
select * from (
select IP_SOURCE_NUM from TABLE1
where log_date between '01-JAN-2012' and date '01-FEB-2012'
and IP_SOURCE_NUM > 0
) where rownum > 0
) a
JOIN ip_geo b on b.ip_from =
(
select max(ip_from) as ip_from_match from ip_geo
where ip_from <= a.IP_SOURCE_NUM
)
group by COUNTRY_NAME
order by count(*) descAs you already realized yourself, the paranthesis doesn't define in which order the query is executed, only what data the result is built from. But by specifying
Rownum > 0you're forcing the optimizer to change the order of execution. But I would use an optimizer hint instead. LikeSelect /* +materialize */ * from...Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
While passing up the opportunity to make sarcasic remarks about a 'wonky' Oracle you might be better served by asking Tom[^] as it sounds like a rather deep and very specific Oracle issue.
Never underestimate the power of human stupidity RAH
I think your link is broken.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
As you already realized yourself, the paranthesis doesn't define in which order the query is executed, only what data the result is built from. But by specifying
Rownum > 0you're forcing the optimizer to change the order of execution. But I would use an optimizer hint instead. LikeSelect /* +materialize */ * from...Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
I suspected something like that was happening. I've never used optimizer hints before; I will look into that. Thanks for the tip. :thumbsup: