Restart of rownumber based on count in other table in oracle sql I have two tables which do not have a common key. I want to do a join on these two tables without a cartesian join. Table 1 has around 40,000 rows (the count varies from day to day in production) while table2 presently has 80,000 rows (row count varies from day to day).

TABLE1 :- NAME_VALUES
NAME_VAL
--------
TOM
DICK
HARRY 

TABLE2 :- CUS_TABLE

CUS_ID
---------
401795480  
201134211 
137643082 
876450821 
777290153 
111035791 
579865552 


I want to have something like below as output

401795480 TOM
201134211 DICK
137643082 HARRY
876450821 DICK
777290153 HARRY
111035791 TOM
579865552 DICK


My idea was to assign rownumber for each table. For table 2 I would like to restart the rownumber once the maximum count of table 1 is reached, something like below, but I am unable to figure out how can I perform this

Table1
    NAME_VAL   TABLE1_RN 
    --------------------- 
    TOM          1
    DICK         2
    HARRY        3

Table2
    CUS_ID    TABLE2_RN
    --------------------
    401795480 1 
    201134211 2
    137643082 3
    876450821 1
    777290153 2
    111035791 3
    579865552 1 


Now I have a key and can easily map to fetch the details I require.

Please suggest if any method can satisfy my requirement.
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.