Hi Tom

Having tow tables with a 0-to-many relationship, I'm looking for a query joining these tables that generates me, inter alia, a constant value per row in the first table.
In the example on LiveSQL I would expect the values c_value beeing the same for each group regarding one row of t1. (I used DBMS_RANDOM as value generator, in reality this will be done using SYS_GUID in a PL/SQL environment.) When I use the /*+ NO_QUERY_TRANSFORMATION */ hint, the result is as expected but the query far too slow.

Any help is appreciated - Thanks in advance

SQL>   select t1.*, t2.*, min(sys_guid()) over ( partition by t1.id ) guid
  2  from t1
  3  left outer join t2
  4    on t2.t1_id = t1.id;

        ID         ID      T1_ID GUID
---------- ---------- ---------- --------------------------------
         1          1          1 5B6F22A8ABF2473DABF4196D261DEAC2
         1          2          1 5B6F22A8ABF2473DABF4196D261DEAC2
         2          3          2 1718A412C83E4B78A389A7A815DAE53E