表中有50多万数据 查询速度极慢 请oracle高手帮忙解决下, 哪种方案比较合理 先谢谢啦。。方案一:
String sql = "select a,b,c,d from abcd order by updatetime desc"; //50多万数据全部查出来了。
List newList = intser.getlistBySQL(sql);
List newUpdateList = newList .subList(0,17); //页面只显示最新的16条数据。
request.setAttribute("newUpdateList",newUpdateList); // 最新更新的16条数据 向页面传值
方案二:
String sql = "select a,b,c,d from (select a,b,c,d from abcd order by updatetime desc) where rownum<=16 ";
//50多万数据按更新时间全部查出来之后,再去前16条数据。
List newList = intser.getlistBySQL(sql);
request.setAttribute("newList",newList); // 最新更新的16条数据 向页面传值
String sql = "select a,b,c,d from abcd order by updatetime desc"; //50多万数据全部查出来了。
List newList = intser.getlistBySQL(sql);
List newUpdateList = newList .subList(0,17); //页面只显示最新的16条数据。
request.setAttribute("newUpdateList",newUpdateList); // 最新更新的16条数据 向页面传值
方案二:
String sql = "select a,b,c,d from (select a,b,c,d from abcd order by updatetime desc) where rownum<=16 ";
//50多万数据按更新时间全部查出来之后,再去前16条数据。
List newList = intser.getlistBySQL(sql);
request.setAttribute("newList",newList); // 最新更新的16条数据 向页面传值
select a,b,c,d from abcd where rownum <=16 order by updatetime desc;
select a,b,c,d from abcd where rownum <=16 order by updatetime desc; 两个结果肯定是不会一样的啊
执行顺序不一样啊
1.下面使用了INDEX FULL SCAN DESCENDING ¦ PK_EMP ,那么就会先排序再取值了,
SQL> set autotrace on
SQL> select * from emp where rownum <5 order by empno desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT
---------- ---------- --------- ---------- -------------- ---------- ---------- --------
7934 MILLER CLERK 7782 23-1月 -82 1300
7902 FORD ANALYST 7566 03-12月-81 3000
7900 JAMES CLERK 7698 03-12月-81 950
7876 ADAMS CLERK 7788 23-5月 -87 1100
执行计划
----------------------------------------------------------
Plan hash value: 3610665443 ---------------------------------------------------------------------------------------
¦ Id ¦ Operation ¦ Name ¦ Rows ¦ Bytes ¦ Cost (%CPU) ¦ Time ¦
---------------------------------------------------------------------------------------
¦ 0 ¦ SELECT STATEMENT ¦ ¦ 4 ¦ 148 ¦ 2 (0) ¦ 00:00:01 ¦
¦* 1 ¦ COUNT STOPKEY ¦ ¦ ¦ ¦ ¦ ¦
¦ 2 ¦ TABLE ACCESS BY INDEX ROWID ¦ EMP ¦ 14 ¦ 518 ¦ 2 (0) ¦ 00:00:01 ¦
¦ 3 ¦ INDEX FULL SCAN DESCENDING ¦ PK_EMP ¦ 4 ¦ ¦ 1 (0) ¦ 00:00:01 ¦
---------------------------------------------------------------------------------------
SQL> select * from emp where rownum <5 order by empno+1 desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7369 SMITH CLERK 7902 17-12月-80 800 20
执行计划
----------------------------------------------------------
Plan hash value: 691404987 ----------------------------------------------------------------------------
¦ Id ¦ Operation ¦ Name ¦ Rows ¦ Bytes ¦ Cost (%CPU) ¦ Time ¦
----------------------------------------------------------------------------
¦ 0 ¦ SELECT STATEMENT ¦ ¦ 4 ¦ 148 ¦ 4 (25) ¦ 00:00:01 ¦
¦ 1 ¦ SORT ORDER BY ¦ ¦ 4 ¦ 148 ¦ 4 (25) ¦ 00:00:01 ¦
¦* 2 ¦ COUNT STOPKEY ¦ ¦ ¦ ¦ ¦ ¦
¦ 3 ¦ TABLE ACCESS FULL ¦ EMP ¦ 14 ¦ 518 ¦ 3 (0) ¦ 00:00:01 ¦
----------------------------------------------------------------------------
方案一违反了原则,在未充分利用数据服务器的同时增加了应用服务器的负担;方案二很好利用了这个原则.
从sql语句优化的角度来将,方案二采用了虚拟表的形式,其优化效率是很高的.
由于楼主的数据量还不是很大,如果是1亿条记录呢?<当然也可以考虑分区及分区索引>.
请思考...
select row_count() over (updatetime desc ) RN, a,b,c,d
from abcd order by updatetime desc)
where RN <=16
如何?多多提些意见或建议。