select * from(select rownum no ,f_id,f_username from faccount order by f_id desc) where no<15000 and no>14500;
执行时间用了 Elapsed: 00:10:42.09在sql plus下运行的.faccount表有9千万行数据f_id是自动增长ID
执行时间用了 Elapsed: 00:10:42.09在sql plus下运行的.faccount表有9千万行数据f_id是自动增长ID
为什么要先执行select rownum no ,f_id,f_username from faccount order by f_id desc 这一句呢.而不是where no <15000 and no> 14500呢
select * from(select rownum no ,f_id,f_username from faccount where rownum <16000 order by f_id desc) where no<15000 and no>14500"这句执行用了0.3秒select * from(select rownum no ,f_id,f_username from faccount where rownum <16000 and rownum>14000 order by f_id desc) where no<15000 and no>14500而下面一句用了 15分钟
为什么差这么多?
from
(
select rownum no, t.*
from
(select f_id,f_username from faccount order by f_id desc) t
where rownum < 15000
)
where no> 14500;
select * from(select rownum no ,f_id,f_username from faccount where rownum <16000 and rownum> 14000 order by f_id desc) where no <15000 and no> 14500 这一句你15分钟 出结果了吗?不可能吧?
这是不可能的
最好测试一下结果是否正确,有可能where比order by先执行如果f_id有索引的话,可以试试 where f_id>0 order by f_id desc最后where no <15000 and no> 14500 改为 no>14500 and rownum<=500或许会快些
以上只是个人看法和建议,没有测试过^_^
顺便帮兄弟验证验证^_^
先过滤再排序的话排序的数据量也会小很多,就快了。
建议建索引
楼主需要的是先排序后再限14500 到15000当中的500条纪录
而用你的语句虽然最后取出来也是500条
但结果集肯定取出来的不一样的
楼主用的是fid desc
也就是说取的是最近的一条到最近15000条纪录
而这样一改
变成了最早的一条到最早的开始16000条纪录
这种情况只有数据里只有1600条纪录时两者才会一样,否则的话永远不一样比如说fid从1到60000
楼主要取的是从FID从45500到45000的纪录而把rownum<16000 放进去,取出来的数据就是fid从1500到1000的数据
其实我觉得楼主即然是根据fid最近到最远来取,不妨在最里的数据里加层过滤
where fid>某个当中的fid,可能会快点
或者增加个日期字段,这样,可以直接用日期过滤总而言之,只要有order by ,速度就会快了,但楼主又是要最近往前取数据,所以只能想办法尽可能的在最里面的SQL 多过滤掉数据,这样就会快很多
SQL> select * from emp where rownum<5 order by empno; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20SQL> select * from emp where rownum<5; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20SQL> select * from emp where rownum<5 order by empno desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20SQL> select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10已选择14行。SQL> select * from emp order by empno desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7839 KING PRESIDENT 17-11月-81 5000 10
7788 SCOTT ANALYST 7566 19-4月 -87 3000 20
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
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已选择14行。SQL>
SQL> select * from emp where rownum <5 order by empno desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20 我给看下我的一个表的数据
表结构(简要)
order_no order_date
order_date是递增的,从今年1.1号开始,到现在为止1、select a.ORDER_DATE,a.ORDER_NO
from a
where rownum<5结果是前四条纪录(最早的四条纪录),升序输出
Row# ORDER_DATE ORDER_NO1 2008/1/2 19:24:55 20080102000165
2 2008/1/2 19:31:58 20080102000166
3 2008/1/2 19:41:57 20080102000168
4 2008/1/2 20:23:25 20080102000174执行计划
Plan
SELECT STATEMENT ALL_ROWSCost: 2 Bytes: 92 Cardinality: 4
2 COUNT STOPKEY
1 TABLE ACCESS FULL TABLE A Cost: 2 Bytes: 92 Cardinality: 4
2、select a.ORDER_DATE,a.ORDER_NO
from a
where rownum<5
order by a.order_date desc结果是前四条纪录,降序输出
Row# ORDER_DATE ORDER_NO1 2008/1/2 20:23:25 20080102000174
2 2008/1/2 19:41:57 20080102000168
3 2008/1/2 19:31:58 20080102000166
4 2008/1/2 19:24:55 20080102000165执行计划
Plan
SELECT STATEMENT ALL_ROWSCost: 534 Bytes: 92 Cardinality: 4
3 SORT ORDER BY Cost: 534 Bytes: 92 Cardinality: 4
2 COUNT STOPKEY
1 TABLE ACCESS FULL TABLE EASYTVC.TORDERDETAIL Cost: 534 Bytes: 709,067 Cardinality: 30,829
3、SELECT *
FROM (SELECT a.order_date, a.order_no
FROM a
ORDER BY a.order_date DESC)
WHERE ROWNUM < 5再看下结果,就是最近的四条纪录,降序输出
Row# ORDER_DATE ORDER_NO1 2008/5/3 22:24:17 20080503005541
2 2008/5/3 22:24:11 20080503005539
3 2008/5/3 22:23:19 20080503005543
4 2008/5/3 22:22:14 20080503005542执行计划
Plan
SELECT STATEMENT ALL_ROWSCost: 534 Bytes: 72 Cardinality: 4
4 COUNT STOPKEY
3 VIEW EASYTVC. Cost: 534 Bytes: 554,922 Cardinality: 30,829
2 SORT ORDER BY STOPKEY Cost: 534 Bytes: 709,067 Cardinality: 30,829
1 TABLE ACCESS FULL TABLE EASYTVC.TORDERDETAIL Cost: 534 Bytes: 709,067 Cardinality: 30,829
SQL> select * from emp where rownum <5 order by empno 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
如果是这样的
where rownum<5
order by 条件
就是先取纪录的前五条,然后再排序输出如果是
select a.*,rownum rn
from a
where rownum <5
order by
这样同样是取前5条,然后再排序,显示列的rownum为排序后的rownum假设表A数据
a
1
2
3
4
5
6
7
8
9
10用
select a.a,rownum rn
from a
where rownum<5
输出
a rn
1 1
2 2
3 3
4 4用
select a.a,rownum rn
from a
where rownum<5
order by a.a
输出
a rn
4 1
3 2
2 3
1 4用select *
from
(
select a.a,rownum rn
from a
order by a.a
)
where rn<5输出结果
a rn
10 1
9 2
8 3
4 4
SQL> create table t(x int);表已创建。SQL> begin
2 for i in 1..10 loop
3 insert into t values(i);
4 end loop;
5 end;
6 /PL/SQL 过程已成功完成。
SQL> select * from t; X
----------
1
2
3
4
5
6
7
8
9
10已选择10行。
SQL> select t.*,rownum rn from t where rownum<5; X RN
---------- ----------
1 1
2 2
3 3
4 4
SQL> select t.x,rownum rn from t where rownum<5 order by t.x; X RN
---------- ----------
1 1
2 2
3 3
4 4SQL> edit
已写入 file afiedt.buf 1 select *
2 from
3 (
4 select t.x,rownum rn
5 from t
6 order by t.x
7 )
8* where rn <5
SQL> / X RN
---------- ----------
1 1
2 2
3 3
4 4SQL>
如果是这样的
where rownum <5
order by 条件
就是先取纪录的前五条,然后再排序输出 如果是
select a.*,rownum rn
from a
where rownum <5
order by
这样同样是取前5条,然后再排序,显示列的rownum为排序后的rownum
我实验的结果是:都是先排序再取内容,不有做过实验吗?
SQL> select * from emp where rownum<5; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20SQL> select * from emp where rownum<5 order by empno desc; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-1月 -82 1300 10
7902 FORD ANALYST 7566 03-12月-81 3000 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
我这是10G
select * from emp where rownum <5 order by empno desc;
肯定是先执行
rownum<5然后再排序,不是先排序再取rownum<5
只有
select *
from
(select * from emp order by empno desc
)
where rownum <5
这样才会先排序再取前五条的
11也是一样的
不可能执行顺序都改啊SQL> select price from titles where rownum<10 order by price desc; PRICE
---------- 22.95
20
19.99
19.99
19.99
11.95
2.99
2.99已选择9行。、
我的是11g
fid>=max(fid)-15000 and fid<=max(fid)-14500
如果你这样插入记录,又是什么结果呢?
create table t(x int);
insert into t values(1);
insert into t values(5);
insert into t values(8);
insert into t values(2);
insert into t values(-1);
insert into t values(1000);
insert into t values(-99);
insert into t values(12);
commit;
select x from t where rownum<=5 order by x desc;
SELECT *
FROM (SELECT a.order_date, a.order_no
FROM torderdetail a
ORDER BY a.order_no DESC)
WHERE ROWNUM < 5Plan
SELECT STATEMENT ALL_ROWSCost: 534 Bytes: 72 Cardinality: 4
4 COUNT STOPKEY
3 VIEW EASYTVC. Cost: 534 Bytes: 554,922 Cardinality: 30,829
2 SORT ORDER BY STOPKEY Cost: 534 Bytes: 709,067 Cardinality: 30,829
1 TABLE ACCESS FULL TABLE A Cost: 534 Bytes: 709,067 Cardinality: 30,829 结果 降序输出
Row# ORDER_DATE ORDER_NO1 2008/5/4 9:52:00 20080504005078
2 2008/5/4 9:44:00 20080504005077
3 2008/5/4 9:43:11 20080504005075
4 2008/5/4 9:39:16 20080504005074
SELECT a.order_date, a.order_no
FROM a,
(SELECT MAX (order_no) max_no
FROM a) b
WHERE a.order_no > b.max_no - 4 AND a.order_no <= b.max_no
Plan
SELECT STATEMENT ALL_ROWSCost: 15 Bytes: 2,464 Cardinality: 77
6 NESTED LOOPS Cost: 15 Bytes: 2,464 Cardinality: 77
3 VIEW A Cost: 2 Bytes: 9 Cardinality: 1
2 SORT AGGREGATE Bytes: 15 Cardinality: 1
1 INDEX FULL SCAN (MIN/MAX) INDEX (UNIQUE) PK_A Cost: 2 Bytes: 462,435 Cardinality: 30,829
5 TABLE ACCESS BY INDEX ROWID TABLE A Cost: 13 Bytes: 1,771 Cardinality: 77
4 INDEX RANGE SCAN INDEX (UNIQUE) PK_A Cost: 2 Cardinality: 14 结果,升序输出
Row# ORDER_DATE ORDER_NO1 2008/5/4 9:43:11 20080504005075
2 2008/5/4 9:44:00 20080504005077
3 2008/5/4 9:52:00 20080504005078
SELECT a.order_date, a.order_no
FROM a,
(SELECT MAX (order_no) max_no
FROM a) b
WHERE a.order_no > b.max_no - 4 AND a.order_no <= b.max_no
ORDER BY 1 DESC
Plan
SELECT STATEMENT ALL_ROWSCost: 16 Bytes: 2,464 Cardinality: 77
7 SORT ORDER BY Cost: 16 Bytes: 2,464 Cardinality: 77
6 NESTED LOOPS Cost: 15 Bytes: 2,464 Cardinality: 77
3 VIEW Cost: 2 Bytes: 9 Cardinality: 1
2 SORT AGGREGATE Bytes: 15 Cardinality: 1
1 INDEX FULL SCAN (MIN/MAX) INDEX (UNIQUE) PK_A Cost: 2 Bytes: 462,435 Cardinality: 30,829
5 TABLE ACCESS BY INDEX ROWID TABLE A Cost: 13 Bytes: 1,771 Cardinality: 77
4 INDEX RANGE SCAN INDEX (UNIQUE) PK_A Cost: 2 Cardinality: 14 结果,降序输出(因为有新的纪录,结果集和上面的有变化)
Row# ORDER_DATE ORDER_NO1 2008/5/4 10:05:20 20080504005081
2 2008/5/4 10:03:57 20080504005080
3 2008/5/4 9:52:00 20080504005078
明显是排序问题啊,你order别的表试试,一个记录只有几十条,一个几万条,不就知道了?
where rownum <16000 order by f_id desc)只对前面的16000条排序,后面是对14000条以后的所有数据排序
这个数据明显远大于16000条,(9千万条减去14000条你自己算)
如果ORADER BY 的那列不是PRAMIRY KEY的话 就是先执行ROWNUM<xx 在执行ORDER BY
我试了下,我的表order_no是PK,order_date不是的
所以两种结果不同
SELECT a.order_date, a.order_no
FROM a
WHERE ROWNUM < 5
ORDER BY a.order_no DESC执行计划
Plan
SELECT STATEMENT ALL_ROWSCost: 5 Bytes: 92 Cardinality: 4
3 COUNT STOPKEY
2 TABLE ACCESS BY INDEX ROWID TABLE A Cost: 5 Bytes: 709,067 Cardinality: 30,829
1 INDEX FULL SCAN DESCENDING INDEX (UNIQUE) PK_A Cost: 2 Cardinality: 4 结果
Row# ORDER_DATE ORDER_NO1 2008/5/4 11:06:30 20080504005104
2 2008/5/4 11:08:31 20080504005103
3 2008/5/4 11:03:29 20080504005102
4 2008/5/4 11:00:54 20080504005101
同样的语句,按order_date来取SELECT a.order_date, a.order_no
FROM a
WHERE ROWNUM < 5
ORDER BY a.order_date DESC执行计划
Plan
SELECT STATEMENT ALL_ROWSCost: 534 Bytes: 92 Cardinality: 4
3 SORT ORDER BY Cost: 534 Bytes: 92 Cardinality: 4
2 COUNT STOPKEY
1 TABLE ACCESS FULL TABLE EASYTVC.TORDERDETAIL Cost: 534 Bytes: 709,067 Cardinality: 30,829 结果
SELECT a.order_date, a.order_no
FROM torderdetail a
WHERE ROWNUM < 5
ORDER BY a.order_date DESC就是完全不同的结果
Row# ORDER_DATE ORDER_NO1 2008/1/2 20:23:25 20080102000174
2 2008/1/2 19:41:57 20080102000168
3 2008/1/2 19:31:58 20080102000166
4 2008/1/2 19:24:55 20080102000165
2 from (select month,sell from sale group by month,sell))
3 where row_id between 5 and 9; ROW_ID MONTH SELL
---------- ------ ----------
5 200005 1400
6 200006 1500
7 200007 1600
8 200008 1000
9 200101 1100
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 |
----------------------------------------------------------------------------