它是一个缓慢的查询吗?可以改进吗?

2022-01-23 00:00:00 sql subquery oracle query-optimization correlated-subquery

我正在浏览 SQLZOO "SELECT 内的 SELECT 教程"，这是执行以下操作的查询之一工作(任务7)

I was going through SQLZOO "SELECT within SELECT tutorial" and here's one of the queries that did the job (task 7)

世界(名称、大陆、地区、人口、gdp)

world(name, continent, area, population, gdp)

SELECT w1.name, w1.continent, w1.population 
FROM world w1
WHERE 25000000 >= ALL(SELECT w2.population FROM world w2 WHERE w2.continent=w1.continent)

我的问题是关于此类查询的有效性.子查询将针对主查询的每一行(国家)运行，因此重复地重新填充给定大陆的 ALL 列表.

My questions are about effectiveness of such query. The sub-query will run for each row (country) of the main query and thus repeatedly re-populating the ALL list for a given continent.

  1. 我应该担心还是 Oracle 优化会以某种方式解决它?
  2. 是否可以在没有相关子查询的情况下对其重新编程?

推荐答案

如果你想在没有关联子查询的情况下重写查询，这里有一种方法:

If you want to rewrite the query without a correalted subquery, here is one way:

SELECT w1.name, w1.continent, w1.population 
FROM world w1
  JOIN
    ( SELECT continent, MAX(population) AS max_population
      FROM world
      GROUP BY continent
    ) c
    ON c.continent = w1.continent
WHERE 25000000 >= c.max_population ;

我并不是说这会更快.Oracle 的优化器非常好，这是一个简单的整体查询，但是您编写它.这是另一个简化:

I do not imply that this will be faster. Oracle's optimizer is pretty good and this is a simple overall query, however you write it. Here's another simplification:

SELECT w1.name, w1.continent, w1.population 
FROM world w1
  JOIN
    ( SELECT continent
      FROM world
      GROUP BY continent
      HAVING MAX(population) <= 25000000 
    ) c
    ON c.continent = w1.continent ;

相关文章