【SQL优化】Oracle中的Top-N与分页匹配查询

Oracle ALLSTARS II夯实基础

加入dbDao.com Oracle技术学习QQ群:171092051

以下罗列一些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
/




关注dbDao.com的新浪微博

扫码关注dbDao.com 微信公众号: