Hi guys!
I want to create 100000 records of demo data, amongst them (valid) random foreign keys, but the result is rather strange: The subquery that selects the primary key gets massively restricted by the optimizer:
select distinct *
from (
select (select cus_id
from (
select ROWNUM r, cus_id from tbl_customers
)
where r = (
select ceil(dbms_random.value*(select count(1) from tbl_customers)) from dual
)
) cus_id
from dual
connect by level < 100000
);
If the table tbl_customers has 5 records, you might expect to get 5 distinct ids as a result, but you get only one!
I suspect it has something to do with the optimizer. So I trued to also select the rowid or the systimestamp to trick it out, but it made no difference. I am not the expert on optimizer hints, I tried several, but all with no effect. As of yet I also have not figured out a way to rewrite the query in a way to get the desired result.
Who can give me a clue in this matter?
Thanks and best regards,
tobi
PS: Eventually the actual statement is meant to look something like that:
insert into tbl_invoices (inv_id, invcus_id, inv_createdate /*, further fields */)
select inv_seq.nextVal
, (select cus_id
from (
select ROWNUM r, cus_id from tbl_customers
)
where r = (
select ceil(dbms_random.value*(select count(1) from tbl_customers)) from dual
)
) cus_id
, sysdate
-- , further foreigen keys selected same ways as above, and other values
from dual
connect by level < 100000
);