Do not join 2 tables but paging with them-CodePudding

I have two oracle table:users and logs,as follow,

users:
---------
id   number,
name varchar2(50)

logs:
-----
user_id  number，
visit_date     date

I need to find out the first 10 users who never appears in the logs table (means they never visit the page),I can write a sql:

select * 
from users u 
where  not exists (
 select 1 from logs o 
 where o.user_id = u.id) 
and rownum<=10

But now the problem is these two table are not in the same schema,they could not be accessed for each other,so I can not join them. Because there are two microservices,one microservice should query its own schema If I fetch 10 records from users,maybe they all never visit the page,I need to fetch 10 more,then after that still 5 users did not visit that page,I need to fetch 5 more,and so on.So I am not sure how many record I should fetch at the first time.Is it a good idea to do so?And how to write the code?

If I fetch all the record of the 2 tables,then do join using Java code,I'm worried about the amount of data being too large.Or should I just make these two tables accessible to each other?

So,how to do paging with 2 table but not join them?

CodePudding user response：

But now the problem is these two table are not in the same schema,they could not be accessed for each other,so I can not join them.

Create a user that can see both schemas and log in to that user and access the tables using schema_name.table_name:

SELECT * 
FROM   schema1.users u 
WHERE  NOT EXISTS (
         SELECT 1
         FROM   schema2.logs l
         WHERE  l.user_id = u.id
       )
ORDER BY u.something
FETCH FIRST 10 ROWS ONLY

SELECT *
FROM   (
  SELECT * 
  FROM   schema1.users u 
  WHERE  NOT EXISTS (
           SELECT 1
           FROM   schema2.logs l
           WHERE  l.user_id = u.id
         )
  ORDER BY u.something
)
WHERE  ROWNUM <= 10

Alternatively, create a third schema and GRANT the SELECT privilege on the USERS and LOGS tables in the first two schemas and then create a view in that third schema and a new micro-service and use that.