大家都知道如果是一张表的分页语句我可以利用先分页再回表的方法 来提高效率 ，但是 如果是两张表做联合查询的话 我应该来怎么提高效率呢? 告诉我一个大概的思想就可以了
谢谢大家！在网上也没有查出合适的方法，解决这个问题后，我会把语句与方法分享给大家

在没有排序的情况下

select * from
    (select rownum rn , a.* from
        (select a.object_id a_object_id,a.object_name a_object_name,b.object_id,b.object_name
         from t1 a, t2 b
         where a.object_id = b.object_id) a
     where rownum < 10)
where rn > 5 ;

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
26 consistent gets
0 physical reads
0 redo size
806 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
这样的逻辑读开始很低，越往后翻页，逻辑读越高。

select * from
    (select rownum rn , a.* from
        (select a.object_id a_object_id,a.object_name a_object_name,b.object_id,b.object_name
        from t1 a, t2 b
        where a.object_id = b.object_id) a
    where rownum < 50000)
where rn > 49995 ;

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1401 consistent gets
0 physical reads
0 redo size
782 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
在有排序的情况下，需要在排序上建立索引（一个排序条件的话要not null 才能用索引）同样也存在越往后翻页，逻辑读越高。
在没有索引的情况下逻辑读很高

select * from (select rownum rn , a.* from (select a.object_id a_object_id,a.object_name a_object_name,b.object_id,b.object_name from t1 a, t2 b where a.object_id = b.object_id order by a.object_name) a where rownum < 10) where rn > 5 ;

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1416 consistent gets
0 physical reads
0 redo size
930 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
加上索引和not null，逻辑度降低了很多。

create index t1_object_name on t1(object_name);
alter table t1 modify object_name not null;
select * from (select rownum rn , a.* from (select a.object_id a_object_id,a.object_name a_object_name,b.object_id,b.object_name from t1 a, t2 b where a.object_id = b.object_id order by a.object_name) a where rownum < 10) where rn > 5 ;

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
930 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

select * from (select rownum rn , a.* from (select a.object_id a_object_id,a.object_name a_object_name,b.object_id,b.object_name from t1 a, t2 b where a.object_id = b.object_id order by a.object_name) a where rownum < 50000) where rn > 49995 ;

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1416 consistent gets
0 physical reads
0 redo size
886 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed