I must admit: I've no idea what you're trying to achieve with this query.
The reason the queries give different results is due to the different plans.
Adding OR V.ROUTE_ID IS NOT NULL prevents the optimizer from using a hash join. So it uses nested loops to join the tables. This is because there's no longer a guaranteed equijoin (an = comparison) between the tables.
Without this predicate it can use a hash join to combine them. This also applies to conditions that are guaranteed to be false such as 1 = 0. The optimizer can safely remove this because it has no effect.
So why does this matter?
With a hash join the database reads each table once. So every row from TMP_TABLE_TEST has the same random value because this is only executed once.
When executing nested loops, the database queries the inner table once for each row from the outer query. W_TABLE2 returns four rows, so TMP_TABLE_TEST is accessed four times. This means there are four calls to dbms_random, each of which can return a different value.
This means you'll get different results from these. Here's a simplified example:
create table tmp_table_test as
select rownum as rid,
1 as operator_id,
cast(null as number) as route_id
from dual t
connect by level <= 5;
with w_table1 as (
select t.rid,
t.operator_id,
t.route_id,
trunc(dbms_random.value, 2) as ratio
from tmp_table_test t
), w_table2 as (
select 1 as operator_id,
level as route_id,
case when mod(level,2) = 0 then 0 else 0.5 end as ratio_from,
case when mod(level,2) = 0 then 0.4999 else 1 end as ratio_to,
case when level > 2 then 1 else 2 end gate
from dual
connect by level <= 4
)
select *
from (
select ratio,
v.rid,
v.operator_id,
s.gate,
s.route_id,
row_number() over(partition by v.rid order by v.operator_id) rn
from w_table1 v
inner join w_table2 s
on v.ratio between s.ratio_from and s.ratio_to
and (s.operator_id = v.operator_id or v.route_id is not null )
) tb;
RATIO RID OPERATOR_ID GATE ROUTE_ID RN
---------- ---------- ----------- ---------- ---------- ----------
.58 1 1 2 1 1
.83 1 1 1 3 2
.15 2 1 1 4 1
.98 2 1 1 3 2
.98 3 1 2 1 1
.44 3 1 2 2 2
.22 4 1 2 2 1
.91 5 1 1 3 1
.39 5 1 1 4 2
.43 5 1 2 2 3
select *
from dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST');
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |
| 1 | VIEW | | 1 | 1 | 10 |
| 2 | WINDOW SORT | | 1 | 1 | 10 |
| 3 | NESTED LOOPS | | 1 | 1 | 10 |
| 4 | VIEW | | 1 | 1 | 4 |
| 5 | CONNECT BY WITHOUT FILTERING| | 1 | | 4 |
| 6 | FAST DUAL | | 1 | 1 | 1 |
|* 7 | VIEW | | 4 | 1 | 10 |
| 8 | TABLE ACCESS FULL | TMP_TABLE_TEST | 4 | 5 | 20 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter(("V"."RATIO">="S"."RATIO_FROM" AND "V"."RATIO"<="S"."RATIO_TO"
AND ("S"."OPERATOR_ID"="V"."OPERATOR_ID" OR "V"."ROUTE_ID" IS NOT NULL)))
with w_table1 as (
select t.rid,
t.operator_id,
t.route_id,
trunc(dbms_random.value, 2) as ratio
from tmp_table_test t
), w_table2 as (
select 1 as operator_id,
level as route_id,
case when mod(level,2) = 0 then 0 else 0.5 end as ratio_from,
case when mod(level,2) = 0 then 0.4999 else 1 end as ratio_to,
case when level > 2 then 1 else 2 end gate
from dual
connect by level <= 4
)
select *
from (
select ratio,
v.rid,
v.operator_id,
s.gate,
s.route_id,
row_number() over(partition by v.rid order by v.operator_id) rn
from w_table1 v
inner join w_table2 s
on v.ratio between s.ratio_from and s.ratio_to
and (s.operator_id = v.operator_id or 1=0)
) tb;
RATIO RID OPERATOR_ID GATE ROUTE_ID RN
---------- ---------- ----------- ---------- ---------- ----------
.63 1 1 2 1 1
.63 1 1 1 3 2
.16 2 1 2 2 1
.16 2 1 1 4 2
.78 3 1 2 1 1
.78 3 1 1 3 2
.82 4 1 2 1 1
.82 4 1 1 3 2
.62 5 1 2 1 1
.62 5 1 1 3 2
select *
from dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST');
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |
| 1 | VIEW | | 1 | 1 | 10 |
| 2 | WINDOW SORT | | 1 | 1 | 10 |
|* 3 | HASH JOIN | | 1 | 1 | 10 |
| 4 | VIEW | | 1 | 1 | 4 |
| 5 | CONNECT BY WITHOUT FILTERING| | 1 | | 4 |
| 6 | FAST DUAL | | 1 | 1 | 1 |
| 7 | VIEW | | 1 | 5 | 5 |
| 8 | TABLE ACCESS FULL | TMP_TABLE_TEST | 1 | 5 | 5 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S"."OPERATOR_ID"="V"."OPERATOR_ID")
filter(("V"."RATIO">="S"."RATIO_FROM" AND "V"."RATIO"<="S"."RATIO_TO"))If you explain what you're trying to do we'll see how we can help rewrite the query to avoid this problem.