在某些版本的Oracle中，相关子查询是否有嵌套限制? [英] Is there a nesting limit for correlated subqueries in some versions of Oracle?

问题描述

以下代码可帮助您理解我的问题:

Here is the code that will help you understand my question:

create table con ( content_id number);
create table mat ( material_id number, content_id number, resolution number, file_location varchar2(50));
create table con_groups (content_group_id number, content_id number);

insert into con values (99);
insert into mat values (1, 99, 7, 'C:\foo.jpg');
insert into mat values (2, 99, 2, '\\server\xyz.mov');
insert into mat values (3, 99, 5, '\\server2\xyz.wav');
insert into con values (100);
insert into mat values (4, 100, 5, 'C:\bar.png');
insert into mat values (5, 100, 3, '\\server\xyz.mov');
insert into mat values (6, 100, 7, '\\server2\xyz.wav');

insert into con_groups values (10, 99);
insert into con_groups values (10, 100);

commit;

SELECT m.material_id,
       (SELECT file_location 
          FROM (SELECT file_location
                  FROM mat
                 WHERE mat.content_id = m.content_id
              ORDER BY resolution DESC) special_mats_for_this_content            
         WHERE rownum = 1) special_mat_file_location                                     
  FROM mat m
 WHERE m.material_id IN (select material_id 
                           from mat
                     inner join con on con.content_id = mat.content_id
                     inner join con_groups on con_groups.content_id = con.content_id
                          where con_groups.content_group_id = 10);

请在查询末尾将数字10视为一个参数.换句话说，在此示例中，此值只是硬编码的.它会根据输入而变化.

Please consider the number 10 at the end of the query to be a parameter. In other words this value is just hardcoded in this example; it would change depending on the input.

我的问题是:为什么会出现错误

My question is: Why do I get the error

"M"."CONTENT_ID": invalid identifier 

对于嵌套的相关子查询?有某种嵌套限制吗?需要针对结果集中的每一行运行该子查询，因为结果将根据content_id更改，而content_id对于每一行可能会有所不同.我该如何使用Oracle做到这一点?

for the nested, correlated subquery? Is there some sort of nesting limit? This subquery needs to be ran for every row in the resultset because the results will change based on the content_id, which can be different for each row. How can I accomplish this with Oracle?

并不是我要开始讨论SQL Server与Oracle，而是来自SQL Server背景，我想指出以下等效查询在SQL Server上运行良好:

Not that I'm trying to start a SQL Server vs Oracle discussion, but I come from a SQL Server background and I'd like to point out that the following, equivalent query runs fine on SQL Server:

create table con ( content_id int);
create table mat ( material_id int, content_id int, resolution int, file_location varchar(50));
create table con_groups (content_group_id int, content_id int);

insert into con values (99);
insert into mat values (1, 99, 7, 'C:\foo.jpg');
insert into mat values (2, 99, 2, '\\server\xyz.mov');
insert into mat values (3, 99, 5, '\\server2\xyz.wav');
insert into con values (100);
insert into mat values (4, 100, 5, 'C:\bar.png');
insert into mat values (5, 100, 3, '\\server\xyz.mov');
insert into mat values (6, 100, 7, '\\server2\xyz.wav');

insert into con_groups values (10, 99);
insert into con_groups values (10, 100);

SELECT m.material_id,
       (SELECT file_location 
          FROM (SELECT TOP 1 file_location
                  FROM mat
                 WHERE mat.content_id = m.content_id
              ORDER BY resolution DESC) special_mats_for_this_content            
               ) special_mat_file_location                                     
  FROM mat m
 WHERE m.material_id IN (select material_id 
                           from mat
                     inner join con on con.content_id = mat.content_id
                     inner join con_groups on con_groups.content_id = con.content_id
                          where con_groups.content_group_id = 10);

能否请您帮助我理解为什么我可以在SQL Server中做到这一点，但不能在Oracle 9i中做到这一点?如果存在嵌套限制，如何在Oracle中的单个选择查询中完成此操作而又不求助于循环表和/或临时表?

Can you please help me understand why I can do this in SQL Server but not Oracle 9i? If there is a nesting limit, how can I accomplish this in a single select query in Oracle without resorting to looping and/or temporary tables?

推荐答案

Oracle的最新版本没有限制，但是大多数旧版本的Oracle的嵌套限制深达1级.

Recent versions of Oracle do not have a limit but most older versions of Oracle have a nesting limit of 1 level deep.

这适用于所有版本:

SELECT  (
        SELECT  *
        FROM    dual dn
        WHERE   dn.dummy = do.dummy
        )
FROM    dual do

此查询适用于12c和18c，但不适用于10g和11g. (但是，至少有一个10g版本允许该查询.并且有一个补丁可以在11g中启用此行为.)

This query works in 12c and 18c but does not work in 10g and 11g. (However, there is at least one version of 10g that allowed this query. And there is a patch to enable this behavior in 11g.)

SELECT  (
        SELECT  *
        FROM    (
                SELECT  *
                FROM    dual dn
                WHERE   dn.dummy = do.dummy
                )
        WHERE   rownum = 1
        )
FROM    dual do

如有必要，您可以使用窗口功能(也可以在SQL Server中使用该功能)解决此限制:

If necessary you can workaround this limitation with window functions (which you can use in SQL Server too:)

SELECT  *
FROM    (
        SELECT  m.material_id, ROW_NUMBER() OVER (PARTITION BY content_id ORDER BY resolution DESC) AS rn
        FROM    mat m
        WHERE   m.material_id IN
                (
                SELECT  con.content_id
                FROM    con_groups
                JOIN    con
                ON      con.content_id = con_groups.content_id
                WHERE   con_groups.content_group_id = 10
                )
        )
WHERE   rn = 1

这篇关于在某些版本的Oracle中，相关子查询是否有嵌套限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！