T-SQL - wrong query execution plan behaviour

it fixes the issue Ok, finally i got to it myself.
The two query plans are different in a small detail which i missed at first. the slow one uses a nested loops operator to join two subqueries together. And that results in the high number at current row count on the index scan operator which is simply the result of multiplicating the number of rows of input a with number of rows of input b.

To fix this issue I found that the answer is to not use "Execution Plan Analysis". Instead, after clicking "Display Estimated Execution Plan", hove your mouse over the first icon in the execution plan and look at the value in "Estimated Subtree Cost".

I wish this helpful for you The reason for the different execution paths is because Oracle thinks that there are about 6 million rows with hotel=128 but only 591,000 with hotel=201. In the case of the bigger intermediate set, Oracle chose a hash join over nested loops.
What I don't get is this:

hope this fix your issue The sql query in my question was never needed.
All I needed is to write to the admin team to grant me the SELECT premissions on:

it should still fix some issue With the literal, the histogram stats are used because the value is known at compile time. The value is unknown at compile time with the local variable so the average density statistics are used instead, resulting in different row count estimates.
You could either add the OPTION RECOMPILE query hint to "sniff" the run time variable value or use a parameterized query. These methods will use the histogram for the row count estimates. The latter will also cache the plan so that subsequent executions will use the same plan regardless of the value as long as the plan remains in cache.