Restart of rownumber based on count in other table in oracle sql
Continuing on Lennart's answer, here is how you might do it in a single query.

  with table1 as (
    select cus_id, rownum rn from cus_table
  ),
  table1_count as (
    select count(*) cnt from cus_table
  ),
  table2 as (
    select nv.name_val, mod(rownum, t1c.cnt) + 1 t1_rn
    from name_values nv, table1_count t1c
  )
  select t1.cus_id, t2.name_val
  from table1 t1
    inner join table2 t2 on t1.rn = t2.t1_rn;


First, since you don't have any windowing in your query, you don't have to use row_number() analytic function, you can just use the rownum pseudocolumn. Secondly, note the + 1 in mod(rownum, t1c.cnt) + 1 - mod(x, y) goes from 0 to y - 1, but rownum goes from 1 to y, hence we need to increase the modulo by 1. 

The complete script with which I tested is here. http://pastebin.com/DTGir8XK The query runs in well under a second on my home computer.

However, note that the query is non-deterministic due to lack of any ORDER BY! You might get different results each time, so perhaps you should reconsider what exactly you are trying to do.



(In response to a now deleted self-answer) If table1 contains 40k rows and table2 80k, then you get only 40k rows as a result because there are "ids" in table1 which are not present in table2, so inner join eliminates such rows from the result.

You could modify the query to reverse the roles of table1 and table2 to get all 80k rows, however, it might be better for you to try to explain your situation and what exactly your ultimate goal is and we might help you better.