数据库查询优化

 本文转载：http://www.itpub.net/thread-1450753-1-1.html

问题：现在又这样一张表，里面有100万条学生成绩信息，怎么以高效的性能获取成绩最高的那前十条数据？

自己去感受速度的加快吧
SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS, DBA_SEQUENCES;

Table created

SQL>
SQL> SELECT OBJECT_ID, OBJECT_NAME
  2    FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
  3            FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T))
  4   WHERE RN BETWEEN 11 AND 20;

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
        36 I_OBJ1
        44 I_USER1
        53 I_CDEF4
        52 I_CDEF3
        51 I_CDEF2
        39 I_IND1
        26 I_PROXY_ROLE_DATA$_1
         7 I_TS#
        50 I_CDEF1
        43 I_TS1

10 rows selected

19秒

SQL>
SQL> SELECT OBJECT_ID, OBJECT_NAME
  2    FROM (SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME
  3            FROM (SELECT OBJECT_ID, OBJECT_NAME FROM T)
  4           WHERE ROWNUM <= 20)
  5   WHERE RN >= 11;

OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------
        36 I_OBJ1
        44 I_USER1
        53 I_CDEF4
        52 I_CDEF3
        51 I_CDEF2
        39 I_IND1
        26 I_PROXY_ROLE_DATA$_1
         7 I_TS#
        50 I_CDEF1
        43 I_TS1

10 rows selected

0.2秒