我在Linux上装了Oracle数据库,其中有一个表,大约有90000条数据,用Oracle进行分页查询的时候,发现很慢
sql语句如下:
select *
from (select row_.*, rownum rownum_
from (select * from usercontract
order by CODE) row_)
where rownum_ <= 100
and rownum_ > 80
其中code不是主键,但是我按照code建了索引,
查询大约需要7s,
各位高手请帮忙分析一下
sql语句如下:
select *
from (select row_.*, rownum rownum_
from (select * from usercontract
order by CODE) row_)
where rownum_ <= 100
and rownum_ > 80
其中code不是主键,但是我按照code建了索引,
查询大约需要7s,
各位高手请帮忙分析一下
select rownum rn, t1.*
from usercontract t1
order by code) t2
where t2.rn between 80 and 100;
1、假如你创建的索引名字叫做:IDX_CONTRACT_CODE,那么你这样尝试一下方法1:SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT /*+INDEX_ASC(t1,IDX_CONTRACT_CODE)*/*
FROM USERCONTRACT T1
ORDER BY CODE) A
WHERE ROWNUM <= 100)
WHERE RN >= 80
2、方法2:SELECT /*+ordered use_nl(rt,st)*/*
FROM (SELECT RID
FROM (SELECT A.RID, ROWNUM RN
FROM (SELECT /*+INDEX_ASC(t1,IDX_CONTRACT_CODE)*/ROWID RID
FROM USERCONTRACT T1
ORDER BY CODE) A
WHERE ROWNUM <= 100)
WHERE RN >= 80) RT,USERCONTRACT ST
WHERE RT.RID = ST.ROWID
--试试看下面的语句怎么样
select * from
(
select u.*,row_number() over(order by code) rn
from usercontract u
)
where rn > 80 and rn <= 100;
--分页的实现,是利用rownum实现数据编号,再利用编号再查询出来
--因此,每执行一次都会重新取一遍数据,如果想要你的查询速度快一些,可以试试下面的方法
--1、清空临时表数据
truncate table tmp_table;
--2、插入数据
insert into tmp_table
select *
from (select row_.*, rownum rownum_
from (select * from usercontract
order by CODE) row_)
--3、查询数据
select * from tmp_table where rownum_ <= 100 and rownum_ > 80;/*******或者试试如下的sql*******/
select *
from (select t.*,row_number()over(order by CODE) rownum_ from usercontract t)
where rownum_ <= 100
and rownum_ > 80;