-- 只能这样,没有其他方法啦! 在 name 字段创建排序索引,然后使用 FIRST_ROWS 提示,看会不会快一点,
select emp.empno,emp.sal,rownum rum from emp order by sal; EMPNO SAL RUM ----- --------- ---------- 7369 800.00 1 7900 950.00 12 7876 1100.00 11 7521 1250.00 3 7654 1250.00 5 7934 1300.00 14 7844 1500.00 10 7499 1600.00 2 7782 2450.00 7 7698 2850.00 6 7566 2975.00 4 7788 3000.00 8 7902 3000.00 13 7839 5000.00 9
14 rows selectedselect emp.empno,emp.sal,rownum rum from emp order by empno; EMPNO SAL RUM ----- --------- ---------- 7369 800.00 1 7499 1600.00 2 7521 1250.00 3 7566 2975.00 4 7654 1250.00 5 7698 2850.00 6 7782 2450.00 7 7788 3000.00 8 7839 5000.00 9 7844 1500.00 10 7876 1100.00 11 7900 950.00 12 7902 3000.00 13 7934 1300.00 14
14 rows selected
不知道你要表达的是什么意思??第二条SQL语句的字段加了索引还是加了主键???
select * from (select * from tabname where order by name) where rownum<20
总结:看oracle官方介绍: If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example: SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;
select * from tabname where rownum<20 order by name只是先按name字段排序,
然后去先20行
"select * from (select * from tabname order by name) where rownum<20"
但是这样效率会很低
-- 只能这样,没有其他方法啦! 在 name 字段创建排序索引,然后使用 FIRST_ROWS 提示,看会不会快一点,
select emp.empno,emp.sal,rownum rum from emp order by sal;
EMPNO SAL RUM
----- --------- ----------
7369 800.00 1
7900 950.00 12
7876 1100.00 11
7521 1250.00 3
7654 1250.00 5
7934 1300.00 14
7844 1500.00 10
7499 1600.00 2
7782 2450.00 7
7698 2850.00 6
7566 2975.00 4
7788 3000.00 8
7902 3000.00 13
7839 5000.00 9
14 rows selectedselect emp.empno,emp.sal,rownum rum from emp order by empno;
EMPNO SAL RUM
----- --------- ----------
7369 800.00 1
7499 1600.00 2
7521 1250.00 3
7566 2975.00 4
7654 1250.00 5
7698 2850.00 6
7782 2450.00 7
7788 3000.00 8
7839 5000.00 9
7844 1500.00 10
7876 1100.00 11
7900 950.00 12
7902 3000.00 13
7934 1300.00 14
14 rows selected
from
(select * from tabname where order by name)
where rownum<20
总结:看oracle官方介绍:
If an ORDER BY clause follows ROWNUM in the same query, then the rows will be
reordered by the ORDER BY clause. The results can vary depending on the way the rows
are accessed. For example, if the ORDER BY clause causes Oracle to use an index
to access the data, then Oracle may retrieve the rows in a different order than
without the index. Therefore, the following statement will not have the same effect
as the preceding example:
SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;