Geek asks and answers

Is this a slow request? Can it be improved?

I went through SQLZOO " SELECT in SELECT tutorial ", and here is one of the queries that completed the task (task 7 )

world (name, continent, region, 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)

My questions are about the effectiveness of such a request. A subquery will be performed for each row (country) of the main query and, thus, re-populates the ALL list for this continent.

  • Should I be worried or will Oracle optimization take care of this somehow?
  • Can it be reprogrammed without a correlated subquery?
+5
source share
3 answers

, :

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 , , . :

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 ;
+1

, , .

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 ALL, , ,

  SELECT w1.name
        , w1.continent
       , w1.population 
  FROM world w1
   WHERE NOT(25000000 < ANY (SELECT w2.population 
                        FROM world w2 
                      WHERE w2.continent=w1.continent)
          );

, ANY- , :

  SELECT w1.name
       , w1.continent
       , w1.population 
   FROM world w1
  WHERE
     NOT EXISTS (SELECT w2.population 
                  FROM world w2 
                 WHERE w2.continent=w1.continent
                   AND 25000000 < w2.population
                );

:

  • , , , . , .
  • , , , , . [, , ]
+3

you can simplify this without having to scan the table twice:

select a.name, a.continent, a.population, a.max_pop
  from (select w1.name, w1.continent, w1.population, 
               max(w1.population) over (partition by w1.continent) max_pop
          from world w1
       ) a 
where 25000000 >= a.max_pop;
+1
source

More articles:


All Articles