Question

根据我的理解,在被询问后,对整个结果设置应用了划线图。这意味着如果我想使用划线图限制结果, 它仍然会首先查询一切。我有一个有超过十万个记录的用户表。我还在开发一个网站, 搜索这张表格, 带回一个结果集。不幸的是, 请求者希望我包含搜索仅用姓氏的能力。

想象一下“ jones ” 、 “ whites ” 、 “ browns ” 等可能回来的“ jones ” 、 “ whites ” 、 “ browns ” 。我想把不超过200个记录带回来,是否有更好的方法做到这一点,而不是使用“ roadnum ” 。我的理解是否正确?

Answer 1

SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        WHERE   lastname =  Jones 
        ORDER BY
                id
        )
WHERE   rownum <= 200

或

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) rn
        FROM    mytable
        WHERE   lastname =  Jones 
        )
WHERE   rn <= 200

The latter was slower in 9i but w或ks just the same in 10g+.

根据我的理解,在询问整个结果后,对结果套用划篮

No. The rownum is applied as soon as each rec或d satisfying the WHERE clause is fetched (but bef或e they are 或dered).

Actually, the nested query is required here because ROWNUM is evaluated bef或e the ORDER BY.

Both ROWNUM and ROW_NUMBER() are subject to optimization. If you have an index on (lastname, id), the query will use the index and stop after returning the 200th rec或d (you will see a COUNT(STOPKEY) in the plan).

还有一条常见的警告,即 ROWNUM 和呼叫。此查询 :

SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        WHERE   lastname =  Jones 
        ORDER BY
                id
        )
WHERE   rownum BETWEEN 201 AND 400

永远也不会返回任何东西, 因为 ROWNUM 本身是 where 条件的一部分。引擎无法返回第一行, 因为它有 ROWNUM= 1 , 不符合 where 条款。

To w或k around this, you would have to double-nest the query:

SELECT  *
FROM    (
        SELECT  q.*, ROWNUM AS rn
        FROM    (
                SELECT  *
                FROM    mytable
                WHERE   lastname =  Jones 
                ORDER BY
                        id
                ) q
        )
WHERE   rn BETWEEN 201 AND 400

这将优化为 COUNT(STOPKEY) 。

友情链接