以下罗列一些Oracle中的Top-N与分页匹配查询优化写法：
CREATE INDEX i_pop ON cities(population);
TOP-N
SELECT * FROM (
SELECT name, population
FROM cities
ORDER BY population DESC
) WHERE rownum <= 5;
SELECT * FROM (
SELECT name, population
FROM cities
WHERE state='Florida'
ORDER BY population DESC
) WHERE rownum <= 5;
分页
SELECT * FROM (
SELECT * FROM (
SELECT name, population,
rownum AS rn
FROM cities
WHERE state='Florida'
ORDER BY population DESC
) WHERE rownum <= 20
) WHERE rn > 10;
Top-N with joins
SELECT * FROM (
SELECT c.name as city, c.population, s.capital
FROM cities c, states s
WHERE c.state_id = s.id
AND c.state='Florida'
ORDER BY c.population DESC
) WHERE rownum <= 5
/
