关于oracle的多表连接查询，分页的问题

blovekyo

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