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

大家都知道如果是一张表的分页语句我可以利用先分页再回表的方法 来提高效率 ，但是 如果是两张表做联合查询的话 我应该来怎么提高效率呢? 告诉我一个大概的思想就可以了

谢谢大家！在网上也没有查出合适的方法，解决这个问题后，我会把语句与方法分享给大家

在没有排序的情况下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 processedselect * 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