Skip to Main Content
  • Questions
  • Is there some sort of nesting limit for correlated subqueries?

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Nate.

Asked: August 04, 2009 - 6:00 pm UTC

Answered by: Tom Kyte - Last updated: August 22, 2016 - 10:24 am UTC

Category: Database - Version: 11.1.0

Viewed 10K+ times! This question is

You Asked

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);


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 
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?

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);


Can you please help me understand why I can do this in SQL Server but not Oracle?

Thanks,
Nate

and we said...

ANSI SQL has table references (correlation names) scoped to just one level deep

ops$tkyte%ORA10GR2> select (select count(*) from (select * from scott.emp where ename = dual.dummy)) from dual;
select (select count(*) from (select * from scott.emp where ename = dual.dummy)) from dual
                                                                    *
ERROR at line 1:
ORA-00904: "DUAL"."DUMMY": invalid identifier



ops$tkyte%ORA10GR2> select (select count(*) from scott.emp where ename = dual.dummy) from dual;

(SELECTCOUNT(*)FROMSCOTT.EMPWHEREENAME=DUAL.DUMMY)
--------------------------------------------------
                                                 0



the first one fails because we tried to push the dual.dummy reference two levels down - in correlated subqueries, they only go a level.


ops$tkyte%ORA10GR2> SELECT m.material_id, m.content_id,
  2            (SELECT max(file_location) keep (dense_rank first order by resolution desc)
  3               FROM mat
  4              WHERE mat.content_id = m.content_id) special_mat_file_location
  5    FROM mat m
  6   WHERE m.material_id IN (select material_id
  7                             from mat
  8                       inner join con on con.content_id = mat.content_id
  9                       inner join con_groups on con_groups.content_id = con.content_id
 10                            where con_groups.content_group_id = 10);

MATERIAL_ID CONTENT_ID SPECIAL_MAT_FILE_LOCATION
----------- ---------- --------------------------------------------------
          1         99 C:\foo.jpg
          2         99 C:\foo.jpg
          3         99 C:\foo.jpg
          4        100 \\server2\xyz.wav
          5        100 \\server2\xyz.wav
          6        100 \\server2\xyz.wav

6 rows selected.

and you rated our response

  (26 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Thank you!

August 05, 2009 - 2:49 pm UTC

Reviewer: Nate from New York City, NY

Perfect! Thank you!

it works in 10.2.0.1.0

August 06, 2009 - 1:14 pm UTC

Reviewer: James Su from toronto

In version 10.2.0.1.0 this query works:

select (select count(*) from (select * from scott.emp where ename = dual.dummy)) from dual;
Tom Kyte

Followup  

August 06, 2009 - 1:26 pm UTC

that was a bug.

it works in none of the terminal releases of 9i, 10g, or 11g

Similar nested query but not able to figure a way out for this

August 06, 2009 - 5:28 pm UTC

Reviewer: Raj from Keene, NH

Hi Tom,

I have a similar situation here but cannot get any better way to write this.

CREATE TABLE ARCH
(
  FILE_ID          NUMBER,
  ACTIVITY_ID      NUMBER,
  SALE_START       VARCHAR2(50 BYTE),
  ACTIVITY_START   VARCHAR2(50 BYTE),
  ACTIVITY_END     VARCHAR2(50 BYTE),
  ITEM_CODE_CUST   VARCHAR2(50 BYTE),
  ADVERTISED       VARCHAR2(25 BYTE),
  DIVISION         VARCHAR2(50 BYTE),
  ACTIVITY_RETAIL  VARCHAR2(50 BYTE),
  ACTIVITY_MULT    VARCHAR2(50 BYTE)
)

Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2505878, '08/22/2009', '08/14/2009', '08/20/2009', 
    '00514757', 'Y', '005', '2', '1');
Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2505879, '08/22/2009', '08/14/2009', '08/20/2009', 
    '00514757', 'Y', '005', '2', '1');
Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2541390, '08/22/2009', '08/14/2009', '08/20/2009', 
    '00514757', 'Y', '005', '1', '1');
Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2541391, '08/22/2009', '08/14/2009', '08/20/2009', 
    '00514757', 'Y', '005', '2', '1');
Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2541392, '08/22/2009', '08/14/2009', '08/20/2009', 
    '00514757', 'Y', '005', '3', '1');
Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2505998, '08/15/2009', '08/24/2009', '08/31/2009', 
    '00515555', 'Y', '006', '5', '1');
Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2505493, '08/15/2009', '08/24/2009', '08/31/2009', 
    '00515555', 'Y', '006', '7', '1');
Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2541542, '08/15/2009', '08/24/2009', '08/31/2009', 
    '00515555', 'Y', '006', '8', '1');
Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2541874, '08/15/2009', '08/24/2009', '08/31/2009', 
    '00515555', 'Y', '006', '3', '1');
Insert into ARCH
   (FILE_ID, ACTIVITY_ID, SALE_START, ACTIVITY_START, ACTIVITY_END, 
    ITEM_CODE_CUST, ADVERTISED, DIVISION, ACTIVITY_RETAIL, ACTIVITY_MULT)
 Values
   (217, 2541762, '08/15/2009', '08/24/2009', '08/31/2009', 
    '00515555', 'Y', '006', '5', '1');
COMMIT;

SELECT   a.file_id,
         a.sale_start,
         a.activity_start,
         a.activity_end,
         a.item_code_cust,
         a.division,
         (SELECT MAX (DISTINCT (NVL (b.advertised, 'No')))
            FROM arch b
           WHERE b.file_id = a.file_id
             AND NVL (b.sale_start, 'NULL') = NVL (a.sale_start, 'NULL')
             AND NVL (b.activity_start, 'NULL') = NVL (a.activity_start, 'NULL')
             AND NVL (b.activity_end, 'NULL') = NVL (a.activity_end, 'NULL')
             AND b.item_code_cust = a.item_code_cust
             AND b.division = a.division) advertised,
         (SELECT activity_id
            FROM (SELECT c.activity_id,
                         ROW_NUMBER () OVER (ORDER BY (c.activity_retail / c.activity_mult)) rnk
                    FROM arch c
                       WHERE NVL (c.sale_start, 'NULL') = NVL (a.sale_start, 'NULL')
                       AND NVL (c.activity_start, 'NULL') = NVL (a.activity_start, 'NULL')
                       AND NVL (c.activity_end, 'NULL') = NVL (a.activity_end, 'NULL')
                       AND c.item_code_cust = a.item_code_cust
                       AND c.division = a.division
                       AND UPPER (NVL (c.advertised, 'N')) = a.advertised
                       AND c.file_id = 217)
           WHERE rnk = 1) primary_id,
         COUNT (*)
    FROM arch a
   WHERE a.file_id = 217
GROUP BY a.file_id, a.sale_start, a.activity_start, a.activity_end, a.item_code_cust, a.division
  HAVING COUNT (*) > 1;

ORA-00904: "A"."ADVERTISED": invalid identifier

I am not sure how to use inner join in a scenario like this.
Need your 10 cents please !!!

Thanks,
Raj
Tom Kyte

Followup  

August 07, 2009 - 9:20 am UTC

looking at:

 (SELECT MAX (DISTINCT (NVL (b.advertised, 'No')))
            FROM arch b
           WHERE b.file_id = a.file_id
             AND NVL (b.sale_start, 'NULL') = NVL (a.sale_start, 'NULL')
             AND NVL (b.activity_start, 'NULL') = NVL (a.activity_start, 
'NULL')
             AND NVL (b.activity_end, 'NULL') = NVL (a.activity_end, 'NULL')
             AND b.item_code_cust = a.item_code_cust
             AND b.division = a.division) advertised,



that is just saying "for this FILE_ID (217), and every row that has the same sale_start, activity_start, activity_end, item_code_cust, division - find the max advertised"

That is more readily expressed as:

 max( nvl(advertised,'No') ) over
        (partition by sale_start, activity_start, activity_end, 
                      item_code_cust, division) advertised,


I'm making the "reasonable" assumption that item_code_cust and division are in real life NOT NULL - since you didn't use NVL() on them. If they are NULLABLE - we'd have to modify that slightly be to exactly like yours - but probably - that is it.

break the data for file_id = 217 up by "sale_start, activity_start, activity_end, item_code_cust, division" and for every row in that partition - assign the max(advertised) value.


Next, in looking at:

(SELECT activity_id
            FROM (SELECT c.activity_id,
                         ROW_NUMBER () OVER (ORDER BY (c.activity_retail / 
c.activity_mult)) rnk
                    FROM arch c
                       WHERE NVL (c.sale_start, 'NULL') = NVL (a.sale_start, 
'NULL')
                       AND NVL (c.activity_start, 'NULL') = NVL 
(a.activity_start, 'NULL')
                       AND NVL (c.activity_end, 'NULL') = NVL (a.activity_end, 
'NULL')
                       AND c.item_code_cust = a.item_code_cust
                       AND c.division = a.division
                       AND UPPER (NVL (c.advertised, 'N')) = a.advertised
                       AND c.file_id = 217)
           WHERE rnk = 1) primary_id,


You are basically saying:

o break the data up by sale_start, activity_start, activity_end, item_code_cust, division and ADVERTISED WITHOUT REGARDS TO CASE

o find the first value of activity id after sorting by a number divided by a string (ouch, ouch - i hate that, I hate your table actually, you are using strings for numbers, bad idea, you will get burned hugely by that as will the customers of your application)

That is more readily stated by:

 11           first_value(activity_id) over
 12                       ( partition by sale_start, activity_start, activity_end, item_code_cust, division, upper(advertised)
 13                             order by activity_retail/TO_NUMBER(activity_mult)) primary_id


So, I think your query is probably:



ops$tkyte%ORA10GR2> select x.*, count(*)
  2    from (
  3  SELECT   a.file_id,
  4           a.sale_start,
  5           a.activity_start,
  6           a.activity_end,
  7           a.item_code_cust,
  8           a.division,
  9                   max( nvl(advertised,'No') ) over
 10                       (partition by sale_start, activity_start, activity_end, item_code_cust, division) advertised,
 11           first_value(activity_id) over
 12                       ( partition by sale_start, activity_start, activity_end, item_code_cust, division, upper(advertised)
 13                             order by activity_retail/TO_NUMBER(activity_mult)) primary_id
 14      FROM arch a
 15     WHERE a.file_id = 217
 16     ) x
 17  GROUP BY x.file_id, x.sale_start, x.activity_start, x.activity_end, x.item_code_cust, x.division, x.advertised, x.advertised, x.primary_id
 18    HAVING COUNT (*) > 1;

FILE_ID SALE_START ACTIVITY_S ACTIVITY_E ITEM_CODE_ DIV ADV PRIMARY_ID   COUNT(*)
------- ---------- ---------- ---------- ---------- --- --- ---------- ----------
    217 08/22/2009 08/14/2009 08/20/2009 00514757   005 Y      2541390          5
    217 08/15/2009 08/24/2009 08/31/2009 00515555   006 Y      2541874          5


replace nested query with analytic function

August 07, 2009 - 9:07 am UTC

Reviewer: Timo Raitalaakso from Finland

select distinct * from (
SELECT a.file_id,
a.sale_start,
a.activity_start,
a.activity_end,
a.item_code_cust,
a.division,
first_value(a.activity_id) over (partition by a.file_id, a.sale_start, a.activity_start, a.activity_end, a.item_code_cust, a.division) primary_id,
MAX (NVL (a.advertised, 'No')) over (partition by a.file_id, a.sale_start, a.activity_start, a.activity_end, a.item_code_cust, a.division) advertised,
COUNT (*) over (partition by a.file_id, a.sale_start, a.activity_start, a.activity_end, a.item_code_cust, a.division) c
FROM arch a
WHERE a.file_id = 217
) where c > 1

Tom Kyte

Followup  

August 07, 2009 - 10:24 am UTC

the analytic count(*) cannot be used to replace the aggregate count(*) in this case - the other two modifications (you are missing an order by on the first_value and an UPPER in the max partition by) can replace the scalar subquery.

But the group by on the outer query removes duplicates, the analytic count(*) cannot be used in this case (in general)

Missed order by in first_value

August 07, 2009 - 9:11 am UTC

Reviewer: Timo Raitalaakso from Finland

select distinct * from (
SELECT a.file_id,
a.sale_start,
a.activity_start,
a.activity_end,
a.item_code_cust,
a.division,
first_value(a.activity_id) over (partition by a.file_id, a.sale_start, a.activity_start,
a.activity_end, a.item_code_cust, a.division) primary_id,
MAX (NVL (a.advertised, 'No')) over (partition by a.file_id, a.sale_start,
a.activity_start, a.activity_end, a.item_code_cust, a.division) advertised,
COUNT (*) over (partition by a.file_id, a.sale_start, a.activity_start, a.activity_end,
a.item_code_cust, a.division) c
FROM arch a
WHERE a.file_id = 217
) where c > 1

missed order by in first_value

August 07, 2009 - 9:12 am UTC

Reviewer: Timo Raitalaakso from Finland

select distinct * from (
SELECT a.file_id,
a.sale_start,
a.activity_start,
a.activity_end,
a.item_code_cust,
a.division,
first_value(a.activity_id) over (partition by a.file_id, a.sale_start, a.activity_start, a.activity_end, a.item_code_cust, a.division ORDER BY (a.activity_retail / a.activity_mult)) primary_id,
MAX (NVL (a.advertised, 'No')) over (partition by a.file_id, a.sale_start, a.activity_start, a.activity_end, a.item_code_cust, a.division) advertised,
COUNT (*) over (partition by a.file_id, a.sale_start, a.activity_start, a.activity_end, a.item_code_cust, a.division) c
FROM arch a
WHERE a.file_id = 217
) where c > 1

Thanks for your reply

August 07, 2009 - 2:36 pm UTC

Reviewer: Raj

Tom,

Thanks for your reply and time.

Bascially this whole "Arch" table is what I pulled from my applications staging table which is totally a de-normalized table containing all varchar2 columns except some ID columns(Don't know why it is designed like that).
Once I clean the duplicate records from this table I move them to a similar table which is a complete normalized one to go ahead with other business validations.

So coming to the issue, may be it is my mistake to not to give the gist of what I wanted from that query. Sorry for that.

Business rule is to find all the activities which have similar file_id,sale_start,activity_start,activity_end,item_code_cust,division.

****Let's say there are 5 duplicates****

Now figure out among those duplicates are there any activities with Advertised = 'Y'.

****Let's say if all of them have Advertised='Y' OR all of them have Advertised='N'****

Then absolutely no problem, we identify which one has least activity_retail / activity_mult and stamp it as the primary_id.

Now here is the catch if there is combination of Advertised and Un-Advertised.

****Let's say 3 of them Advertised='Y' and 2 of them Advertised='N'****

Only from the activities where Advertised='Y', identify which one has least activity_retail / activity_mult and stamp it as the primary_id.

It is a little bit messy kind to identify duplicates but that is it.

So using your query it works perfect when all of them are advertised= 'Y' or 'N'.

I went and ran this update on that records to make some of them advertised = 'N'
UPDATE arch
   SET advertised = 'N'
 WHERE activity_id IN (2505878, 2541391, 2541874)

Now when I used the same query I got this
FILE_ID SALE_START ACTIVITY_S ACTIVITY_E ITEM_CODE DIV ADV PRIMARY_ID COUNT(*)
------- ---------- ---------- ---------- --------- --- --- ---------- --------
217 08/22/2009 08/14/2009 08/20/2009 00514757  005 Y   2505878    2
217 08/15/2009 08/24/2009 08/31/2009 00515555  006 Y   2505998    4
217 08/22/2009 08/14/2009 08/20/2009 00514757  005 Y   2541390    3

But as per the business logic explained we need this
FILE_ID SALE_START ACTIVITY_S ACTIVITY_E ITEM_CODE DIV ADV PRIMARY_ID COUNT(*)
------- ---------- ---------- ---------- --------- --- --- ---------- --------
217 08/15/2009 08/24/2009 08/31/2009 00515555  006 Y   2505998    5
                                                              or
                                        2541762

217 08/22/2009 08/14/2009 08/20/2009 00514757  005 Y   2541390    5

I hope it is worth the explanation. :-)

Thanks,
Raj.


Tom Kyte

Followup  

August 07, 2009 - 2:44 pm UTC

I didn't read all of this, but.......

did you read through all of what I read (i tried to explain what I was doing and then show what I did)?

And did you try to apply that technique to your problem, if my answer was not exactly what you wanted sql-wise??


How hard have you tried to understand the 'technique' presented?

Same problem, but inside an UPDATE

October 19, 2009 - 11:00 am UTC

Reviewer: guy from UK

This currently works because I'm running Oracle 10.

I'm trying to move to Oracle 11, where it breaks (INVALID IDENTIFIER).

Any ideas greatly appreciated.

UPDATE US_PERFORMANCE_INFO i
SET LAST_RACE_DATE_5_S =
(
SELECT race_date
FROM
(
SELECT p.horse_code, p.race_date, DENSE_RANK() OVER (PARTITION BY horse_code ORDER BY p.race_date DESC) racedate_rank
FROM US_PERFORMANCE p, US_RACE R
WHERE p.horse_code = i.horse_code
AND p.race_code = r.race_Code
AND p.race_date < i.race_date
) B
WHERE b.racedate_rank = 5
AND i.horse_code = b.horse_code
)
WHERE i.race_date

(I can add some SQL for creating tables if necessary, but I thought this would be simple enough).

Tom Kyte

Followup  

October 22, 2009 - 4:09 pm UTC

(I can add some SQL for creating tables if necessary, but I thought this would
be simple enough).


why would you think that? i cannot run the code, therefore I cannot reproduce the issue, therefore I cannot tell you what to do.


Doesn't work in 11

November 19, 2009 - 10:48 am UTC

Reviewer: Guy from London

I think that it only worked in 10 because I am running a version that contains a bug.



DROP TABLE A_PERFORMANCE ;

CREATE TABLE A_PERFORMANCE ( RACE_DATE DATE, HORSE_CODE NUMBER(1) ) ;

Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('10/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('09/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('08/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('07/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('06/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('05/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('04/01/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6);

Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('10/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('09/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('08/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('07/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('06/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('05/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);
Insert into A_PERFORMANCE (RACE_DATE, HORSE_CODE) Values (TO_DATE('04/01/2008 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7);

DROP TABLE A_PERFORMANCE_INFO ;

CREATE TABLE A_PERFORMANCE_INFO ( INFO_DATE DATE, HORSE_CODE NUMBER(2), FIFTHRECENT_DATE DATE );

Insert into A_PERFORMANCE_INFO (INFO_DATE, HORSE_CODE, FIFTHRECENT_DATE) Values (TO_DATE('10/21/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6, NULL);
Insert into A_PERFORMANCE_INFO (INFO_DATE, HORSE_CODE, FIFTHRECENT_DATE) Values (TO_DATE('10/21/2009 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7, NULL);

COMMIT;

UPDATE A_PERFORMANCE_INFO pi
SET FIFTHRECENT_DATE =
(
SELECT race_date
FROM
(
SELECT p.horse_code, p.race_date, DENSE_RANK() OVER (PARTITION BY p.horse_code ORDER BY p.race_date DESC) racedate_rank
FROM A_PERFORMANCE p
WHERE p.horse_code = pi.horse_code
AND p.race_date < pi.info_date
) B
WHERE b.racedate_rank = 5
and b.horse_code = i.horse_code
and b.race_date = i.race_date
)
WHERE pi.info_date = '21 oct 2009'
Tom Kyte

Followup  

November 23, 2009 - 3:51 pm UTC

yes, correlation names such as PI can only go one level down according to the SQL scoping rules.

so, if it worked, it was a sql parsing bug that was corrected.

so, what to do?

Merge.


ops$tkyte%ORA10GR2> merge into a_performance_info
  2  using (select horse_code, race_date
  3           from (select horse_code, race_date,
  4                                DENSE_RANK() OVER (PARTITION BY horse_code ORDER BY race_date DESC) dr
  5                                   from a_performance
  6                                  where race_date < to_date( '21-oct-2009' )
  7                            )
  8                  where dr = 5) x
  9  on (a_performance_info.horse_code = x.horse_code)
 10  when matched then update set fifthrecent_date = race_date
 11  /

2 rows merged.

ops$tkyte%ORA10GR2> merge into a_performance_info
  2  using (select horse_code, max(race_date) race_date
  3           from (select horse_code, race_date,
  4                                DENSE_RANK() OVER (PARTITION BY horse_code ORDER BY race_date DESC) dr
  5                                   from a_performance
  6                                  where race_date < to_date( '21-oct-2009' )
  7                            )
  8                  where dr = 5
  9              group by horse_code) x
 10  on (a_performance_info.horse_code = x.horse_code)
 11  when matched then update set fifthrecent_date = race_date
 12  /

2 rows merged.



note that the second one would deal with a horse racing more than once on a day - not sure if you need that or not.

Wow - impressive!

November 24, 2009 - 3:35 am UTC

Reviewer: A reader

Very nice piece of code. I didn't know you can write updates like this.

Thx.


P.S. A horse almost never runs more than once a day. I know of one instance in Britain some years back.

Some correlataed subquery 2 level deep look to work

May 03, 2011 - 4:06 pm UTC

Reviewer: orel from France

Hi Tom,

Look at the following, i don't understand why your query from your first answer failed and mine didn't.

For me, my "t1.date_time" in "where t3.date_time < t1.date_time" is at same level down than your "dual.dummy" in "where ename = dual.dummy"

What am i missing ?

SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select (select count(*)
  2            from (select *
  3                    from scott.emp
  4                   where ename = dual.dummy
  5                 )
  6         ) as c
  7    from dual
  8  /
                 where ename = dual.dummy
                               *
ERROR at line 4:
ORA-00904: "DUAL"."DUMMY": invalid identifier


SQL> with t as (
  2    select to_date('2011-05-03 20:00:00','yyyy-mm-dd hh24:mi:ss') as date_time, 200 as val from dual union all
  3    select to_date('2011-05-03 20:15:00','yyyy-mm-dd hh24:mi:ss')             , 220        from dual union all
  4    select to_date('2011-05-03 20:30:00','yyyy-mm-dd hh24:mi:ss')             , 210        from dual union all
  5    select to_date('2011-05-03 20:45:00','yyyy-mm-dd hh24:mi:ss')             , 190        from dual
  6  )
  7  select date_time,
  8         val,
  9         (select val
 10            from t t2
 11           where date_time = (select max(date_time)
 12                                from t t3
 13                               where t3.date_time < t1.date_time)
 14          ) as prev_val
 15     from t t1
 16  /

DATE_TIME        VAL   PREV_VAL
--------- ---------- ----------
03-MAY-11        200
03-MAY-11        220        200
03-MAY-11        210        220
03-MAY-11        190        210


Obviously i should have used lag, but i was developing an answer for a MySql forum.
So why did my query work ?

Tom Kyte

Followup  

May 04, 2011 - 2:00 pm UTC

it is the difference between using the scalar subquery and the inline view.

I think the second one should be failing though.

not exactly the same...

May 03, 2011 - 4:47 pm UTC

Reviewer: orel from France

Doh, in fact my subquery doesn't look exactly the same than yours...
It looks more like :

SQL> select (select count(*)
  2            from dual d2
  3           where dummy = (select ename
  4                            from scott.emp
  5                           where ename = d1.dummy)
  6         ) as c
  7    from dual d1
  8  /

         C
----------
         0

So, in fact it looks safe to have 2 level deep correlated query in select since it is not correlated SUBQUERY.
Can you confirm if my understanding is correct ? And maybe rephrase it because my english is not really good.

thanks

May 04, 2011 - 3:13 pm UTC

Reviewer: orel from France

Thanks for your feedback.
So I will also avoid as much as possible these 2 level deep scalar subquery.
Luckily for us, Oracle offer many SQL fonctionalities to avoid it :)

May 05, 2011 - 10:19 am UTC

Reviewer: A reader

Hi Tom,

In the above query posted by op why his second query didn't fail it should give error since its referencing 2 level deep

is it optimizer merging(un_nest) the inner query?

Thanks

Tom Kyte

Followup  

May 06, 2011 - 10:11 am UTC

try hinting it to prohibit any unnesting and see what you see?

the plan I saw had no unnesting in it...

My testings and questions

September 14, 2011 - 8:32 pm UTC

Reviewer: A reader from CN

Hi, Tom:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as apps
 
SQL> create table mmt(id number);
 
Table created
 
SQL> create table cicd(id number, mmt_id number);
 
Table created
 
SQL> create index cicd_n1 on cicd(mmt_id);
 
Index created

SQL> insert into mmt values (1);
 
1 row inserted
 
SQL> insert into mmt values (2);
 
1 row inserted
 
SQL> insert into mmt values (3);
 
1 row inserted

SQL> insert into cicd values (1,1);
 
1 row inserted
 
SQL> insert into cicd values (1,2);
 
1 row inserted
 
SQL> insert into cicd values (2,1);
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
We want to out join mmt to cicd with mmt's id and cicd's mmt_id.
If there are more than 1 corresponding rows in cicd, we don't want to get too many results and like to get a null value.
Firstly, we wrote SQL as follow and it works well.

SQL> SELECT mmt.id,
  2         cicd2.id
  3  FROM mmt,
  4       (SELECT mmt_id, COUNT(*) FROM cicd GROUP BY mmt_id HAVING COUNT(*)=1) cicd,
  5       cicd cicd2
  6  WHERE mmt.id=cicd.mmt_id(+)
  7  AND cicd.mmt_id=cicd2.mmt_id(+)
  8  ;
 
        ID         ID
---------- ----------
         2          1
         3 
         1 

Then we think it use cicd table 2 times and may be poor performance when the data becomes more and more.
So we changed the SQL to use scalar subquery.

SQL> SELECT mmt.id,
  2         (SELECT ID FROM (SELECT ID,
  3         COUNT(*) over(PARTITION BY mmt_id) cnt
  4         FROM cicd
  5         WHERE mmt_id=mmt.id
  6         ))
  7  FROM mmt
  8  ;
 
SELECT mmt.id,
       (SELECT ID FROM (SELECT ID,
       COUNT(*) over(PARTITION BY mmt_id) cnt
       FROM cicd
       WHERE mmt_id=mmt.id
       ))
FROM mmt
 
ORA-00904: "MMT"."ID": invalid identifier

Now I know it's because of the one level deep limitation.
Then I changed it another way.

SQL> SELECT mmt.id
  2        ,cicd.id
  3  FROM   mmt
  4        ,(SELECT mmt_id
  5                ,ID
  6                ,COUNT(*) over(PARTITION BY mmt_id) cnt
  7          FROM   cicd) cicd
  8  WHERE  mmt.id = cicd.mmt_id(+)
  9  AND    cicd.cnt(+) = 1
 10  ;
 
        ID         ID
---------- ----------
         2          1
         1 
         3 

It works but because of the excution plan its performance is not good.
Then I changed it again.

SQL> SELECT mmt.id
  2        ,(SELECT ID
  3         FROM   cicd
  4         WHERE  mmt_id = mmt.id
  5         AND    (SELECT COUNT(*)
  6                 FROM   cicd
  7                 WHERE  mmt_id = mmt.id) = 1)
  8  FROM   mmt
  9  ;
 
        ID (SELECTIDFROMCICDWHEREMMT_ID=M
---------- ------------------------------
         1 
         2                              1
         3 

Now, my questions are:
1. Why the second SQL suffered from 904 error while the forth not?
2. Because of the index cicd_n1, the forth SQL can use it twice while the third one can't use it even once. 
   But if the second SQL work or the bug you said before in 10.2.0.1.0, the performance will be better, isn't it?
3. How to change the second SQL to get better performance than the forth one?

Best Regards,
Willam

Tom Kyte

Followup  

September 15, 2011 - 7:39 am UTC

scalar subqueries are not good for large results - they are executed over and over and over and tend to use indexes which is very bad.

ops$tkyte%ORA11GR2> SELECT mmt.id,
  2         cicd2.id
  3  FROM mmt,
  4       (select *
  5          from (SELECT cicd.*, COUNT(*) over (partition by mmt_id) cnt
  6                  from cicd)
  7         where cnt = 1) cicd2
  8  WHERE mmt.id=cicd2.mmt_id(+)
  9  ;

        ID         ID
---------- ----------
         1
         2          1
         3


how do you feel about that one - benchmark them against your data, scale up your data..



1) scoping, inline views only go one level.
2) good, you want NO INDEXES used, they would be painfully slow
3) see above...

September 15, 2011 - 9:05 pm UTC

Reviewer: william from CN

1. I know the one level deep limitation of inline view. Do you mean that if the subquery is in select or where clause, there is no such limitation?

2. For your suggested SQL, I found its plan is very same as my third SQL. It also use FULL table scan and can't use my index build on cicd table.
If the data in mmt is large and data in cicd is small, I think the performance won't be bad. But if the data in mmt is small and the data in cicd is large, the performance will be very very bad.

I found that:
1/ If I want to access cicd table just once, I must use analytic function here.
2/ If I use analytic functions and want to use filter conditions on the result of the analytic functions, I must use inline view to push subquery a level deeper.
3/ If I push the subquery a level deeper, I can't access any identifier of the main query.
4/ If I can't use identifier in the main query, I can't utilize the index on the table of the subquery.

So, I think if we can't write the SQL this way:
SELECT mmt.id
,(SELECT ID
FROM cicd
WHERE mmt_id = mmt.id
HAVING COUNT(*) over(PARTITION BY mmt_id) = 1)
FROM mmt
we may have no way to solve the problem.

Best Regards,
William
Tom Kyte

Followup  

September 16, 2011 - 2:10 pm UTC

1) there are scoping limits there. Your scalar subquery has an inline view, the inline view can only see the scalar subquery right above it, it cannot see the outer query

2) EXCELLENT!!!! I would be so mad if it used an index. You are processing every row from every table - it would be painful to use an index.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6749454952894#6760861174154

Indexes are slow when processing lots of rows. I want to see a big juicy pair of full scans and a hash outer join. Anything else would be wrong for this query (assuming you want to fetch the entire result set, not just the first rows)


But if the data in mmt is small and the data in cicd
is large, the performance will be very very bad.


is that the case?

if both are large - indexes would be painful
if mnt is large - indexes would be painful

now what?


If mnt is designed to be small and ccid big, then you can consider:



write the query my way and test it, become surprised....


ops$tkyte%ORA11GR2> SELECT mmt.id,
  2         cicd2.id
  3  FROM mmt,
  4       (select *
  5          from (SELECT cicd.*, COUNT(*) over (partition by mmt_id) cnt
  6                  from cicd <b>where mnt_id in (select id from mmt)</b> )
  7         where cnt = 1) cicd2
  8  WHERE mmt.id=cicd2.mmt_id(+)
  9  ;

September 17, 2011 - 2:45 am UTC

Reviewer: A reader from CN

Thank you very much for your help!

1. Maybe I have not explain the question very clearly.
SELECT mmt.id
,(SELECT ID
FROM (SELECT ID
,COUNT(*) over(PARTITION BY mmt_id) cnt
FROM cicd
WHERE mmt_id = mmt.id) v2
WHERE cnt = 1) v1
FROM mmt
In this query, the reference of mmt.id is in v2 whose upper 1 level is v1. Because the mmt.id is a column of its upper 2 level table mmt, the ora-904 is raised.
This is no problem.

Then, let's see another query:
SELECT mmt.id
,(SELECT ID
FROM cicd
WHERE mmt_id = mmt.id
AND (SELECT COUNT(*)
FROM cicd
WHERE mmt_id = mmt.id) = 1) v1
FROM mmt
The reference of mmt.id is in the deepest level subquery which is a nested subquery. Its upper 1 level is the inline view v1 and its upper 2 level is the mmt table query. So why the deepest subquery can reference its upper 2 level column this time?

2. Your this query is good to use index on cicd. But actually, my mmt is not a table indeed. It's a very complex view. Suppose that the complex mmt view may run 10 seconds and get 10 rows result, if the query can be written in my way:
SELECT mmt.id
,(SELECT ID
FROM cicd
WHERE mmt_id = mmt.id
HAVING COUNT(*) over(PARTITION BY mmt_id) = 1)
FROM mmt
it will search the corresponding 10 rows in cicd table which have millions of rows with index and thus will run very fast, maybe totally 10.001 seconds
If I use your query:
SELECT mmt.id
,cicd2.id
FROM mmt
,(SELECT *
FROM (SELECT cicd.*
,COUNT(*) over(PARTITION BY mmt_id) cnt
FROM cicd
WHERE mmt_id IN (SELECT id
FROM mmt))
WHERE cnt = 1) cicd2
WHERE mmt.id = cicd2.mmt_id(+)
the mmt will run 2 times and thus may run 20.001 seconds.

So I wonder why oracle can't implement the use of having clause in analytic query which can apply conditions after the running of analytic functions?

Best Regards,
William
Tom Kyte

Followup  

September 17, 2011 - 11:14 am UTC

1) because scalar subquery <> inline view.

2) with my_mmt as ( select * frmo mmt )
select ......


use with subquery factoring and you stand a good chance of having the complex view materialized into temp and then used twice.

September 19, 2011 - 4:19 pm UTC

Reviewer: A reader from AZ

Is there a particular reason it is limited to one level? I see that in a particular version it was available for more levels, but it was considered a bug and fixed. Is there some sort of performance reason why this is restricted to one level or is Oracle protecting the developers from themselves (i.e. using the same alias at multiple levels and being confused when data returns differently than expected). I realize that 9 times out of 10 Analytical Functions will get you what you want faster and more eligantly, I am just curious the reasoning.
Tom Kyte

Followup  

September 19, 2011 - 5:32 pm UTC

scoping rules are dictated by the standards for the language. Which we don't write - ANSI does that.

No trace for scalar subquery?

September 20, 2011 - 2:44 am UTC

Reviewer: William from CN

Hi, Tom,

When I want to have a look at and compare the trace of my sql and your suggested sql, I suddenly found that there is no trace for scalar subquery.
Why?
If so, how can I analyze the rows processed by every access line and compare the 2 tkprofed trace file?

Thanks,
William
Tom Kyte

Followup  

September 20, 2011 - 10:26 am UTC

it should be in there, for example:

select (select count(*) from dual) from scott.dept

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          7          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.02          0          7          0           4

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 273
Number of plan statistics captured: 1

Row Source Operation
---------------------------------------------------
SORT AGGREGATE (cr=0 pr=0 pw=0 time=47 us)
 FAST DUAL  (cr=0 pr=0 pw=0 time=4 us cost=2 size=0 card=1)
TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=384 us cost=3 size=0 card=4) 


select (select count(*)
from
 dual, scott.emp where ename like '%'||dummy||'%') from scott.dept


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         15          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         15          0           4

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 273
Number of plan statistics captured: 1


---------------------------------------------------
SORT AGGREGATE (cr=8 pr=0 pw=0 time=119 us)
 NESTED LOOPS  (cr=8 pr=0 pw=0 time=92 us cost=5 size=8 card=1)
  TABLE ACCESS FULL DUAL (cr=2 pr=0 pw=0 time=31 us cost=2 size=2 card=1)
  TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=48 us cost=3 size=6 card=1)
TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=54 us cost=3 size=0 card=4)




you can see the access to "fast dual" in there and the accesses to EMP and so on...


September 20, 2011 - 8:26 pm UTC

Reviewer: A reader from CN

My DB version is: 10.2.0.3.0.
There's nothing of scalar subquery tables in the trace file or the explain plan result.

Could you please help confirming that it should be in this version of DB?
And what's your DB version?

Thanks,
William
Tom Kyte

Followup  

September 21, 2011 - 9:24 am UTC

I'm on 11g.

and unless someone tells me otherwise, I assume they are too ;)

from 10.2.0.5:

select (select count(*)
from
 dual, scott.emp where ename like '%'||dummy||'%') from scott.dept

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          9         12          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.02          9         12          0           4

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 207

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=10 pr=8 pw=0 time=3089 us)
      0   NESTED LOOPS  (cr=10 pr=8 pw=0 time=3071 us)
      1    TABLE ACCESS FULL DUAL (cr=3 pr=2 pw=0 time=1655 us)
      0    TABLE ACCESS FULL EMP (cr=7 pr=6 pw=0 time=1410 us)
      4  INDEX FULL SCAN PK_DEPT (cr=2 pr=1 pw=0 time=1547 us)(object id 51147)



I don't have a 10.2.0.3 sitting around to test with, sorry.

sometimes

October 07, 2011 - 11:09 pm UTC

Reviewer: A reader

Hi,

I found the trace part of scalar subqueries sometimes it exists while sometimes not.
I don't know how to reproduce it in your environment.
Just like the execution plan in the trace file, I also find it sometimes exists, sometimes does not exist.
May be a bug? who knows.

Best Regards,
William

Can you clarify the documentation?

February 13, 2013 - 5:23 pm UTC

Reviewer: Ramesh from CA USA

Hi Tom,
Sorry to update such an old thread, but I wanted to get your view on something I read in the documentation about correlated subqueries.

From Oracle Database SQL Language Reference for 11g Release 2

"Oracle performs a correlated subquery when a nested subquery references a column
from a table referred to a parent statement any number of levels above the subquery."

Is the "any number of level above the subquery" statement not true for correlated subqueries?

Thanks
Tom Kyte

Followup  

February 14, 2013 - 7:26 am UTC

I bugged that section:

doc says:

<quote>
Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery. 
</quote>

that is not true, the correlated subquery must reference only its immediate parent query block - if you try to go up two or more levels - the outer tables attributes are out of scope.  

Consider this example - if D2 is two levels down, D1 is out of scope.  Only when D2 is one level down can it reference D1:


ops$tkyte%ORA11GR2> select *
  2    from dual d1
  3   where exists ( select null
  4                    from (select null
  5                            from dual d2
  6                           where d2.dummy = d1.dummy))
  7  /
                         where d2.dummy = d1.dummy))
                                          *
ERROR at line 6:
ORA-00904: "D1"."DUMMY": invalid identifier


ops$tkyte%ORA11GR2> select *
  2    from dual d1
  3   where exists ( select null
  4                    from dual d2
  5                   where d2.dummy = d1.dummy)
  6  /

D
-
X





additionally the doc states:

A correlated subquery is evaluated once for each row processed by the parent statement. 


that is an inaccurate depiction of what takes place.  It would be accurate to say:

A correlated subquery conceptually is evaluated once for each row processed by the parent statement, however the optimizer is free to rewrite the query as a join or using some other technique to arrive at a query that is semantically equivalent. 

Is this considered as two levels deep?

February 14, 2013 - 11:50 am UTC

Reviewer: Ramesh from CA USA

Hi Tom,
Thanks for you answer. Testing further, I got confusing results.
Consider,
create table p1(p1_col number);
create table c1(c1_col number);
create table c2 (c2_col number);
insert all
       into p1 values(lvl)
       into c1 values(lvl)
       into c2 values(lvl)    
select level as lvl
from   dual
connect by level<6;
commit;

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> set autotrace on explain;
SQL> select *
  2  from p1
  3  where exists (select null
  4                from   c1
  5                 where exists (select null
  6                               from c2
  7                               where c2.c2_col=p1.p1_col));

    P1_COL
----------
         1
         2
         3
         4
         5


Execution Plan
----------------------------------------------------------
Plan hash value: 3478153553

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     2 |    26 |    18   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | P1   |     5 |    65 |     3   (0)| 00:00:01 |
|*  3 |   FILTER            |      |       |       |            |          |
|   4 |    TABLE ACCESS FULL| C1   |     5 |       |     3   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| C2   |     1 |    13 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM "C1" "C1" WHERE  EXISTS (SELECT 0
              FROM "C2" "C2" WHERE "C2"."C2_COL"=:B1)))
   3 - filter( EXISTS (SELECT 0 FROM "C2" "C2" WHERE "C2"."C2_COL"=:B1))
   5 - filter("C2"."C2_COL"=:B1)

Note
-----
   - dynamic sampling used for this statement (level=2)   

So that worked. I was trying to see if there was anything in the execution plan that would indicate that the optimizer decided to do a rewrite and use joins, but I couldn't see anything.

However this statement does not work.

SQL> select *
  2  from p1
  3  where exists (select null
  4                from   (select null
  5                        from c2
  6                        where c2.c2_col=p1.p1_col));
                      where c2.c2_col=p1.p1_col))
                                      *
ERROR at line 6:
ORA-00904: "P1"."P1_COL": invalid identifier   

Isn't the inner-most query two levels deep in both cases?

Tom Kyte

Followup  

February 14, 2013 - 12:06 pm UTC



it is the inline view that is preventing it...

Two level of correlated query

August 27, 2013 - 10:04 am UTC

Reviewer: Jay from TN

Select date

(SELECT dt FROM (SELECT lapsedate + LEVEL dt FROM DUAL
CONNECT BY LEVEL < 30) WHERE
(SELECT COUNT (*) FROM holiday WHERE holidaydate = dt) = 0 and rownum=1)
from (SELECT
CASE
WHEN M.action = 'R'
THEN CASE
WHEN f.frequencyseq = 1
THEN t.tdate + f.lapseday
WHEN f.frequencyseq = 3
THEN t.tdate + f.lapseday
END
ELSE NULL
END lapsedate
FROM option_upload_aud t,
documentstatic M,
followupstatic f)

I have a query to display the next non holiday based on some calculation.Connect by level block code is the sql for calculating next business day.When i execute it alone with hard coding date,it works well.But when i embed this with code,error is displayed,lapsedate invalid identifier.Please help me to resolve this error.

Create table option_upload_aud(tradeid number,tdate date);

Insert into option_upload_aud values(1,to_date('15-Aug-2013','dd/mm/yyyy');

Insert into option_upload_aud values(1,to_date('25-SEP-2013','dd/mm/yyyy');


Create table followupstatic(frequencyseq number,lapseday number);
insert into followupstatic values(1,1);
insert into followupstatic values(2,1);

create table holiday(no number,holidaydate date);
insert into holiday values(1,to_date('17-AUG-2013','dd/mm/yyyy');
insert into holiday values(1,to_date('05-SEP-2013','dd/mm/yyyy');

If my action=R and frequency seq=1 then lapsedate = 15/08/2013 +1,so my output should display next non holiday date after 16/08/2013.For example next day is 17/08/2013 which is a holiday date in holiday table,so my output should display 18/08/2013.

Thanks & Regards
Aravind
Tom Kyte

Followup  

August 28, 2013 - 7:07 pm UTC

give full example, I don't know how to reproduce your error?

Full example of the above query

August 29, 2013 - 3:46 am UTC

Reviewer: Jay

select (SELECT dt FROM (SELECT lapsedate + LEVEL dt FROM DUAL
CONNECT BY LEVEL < 30) WHERE
(SELECT COUNT (*) FROM mst_holiday WHERE holidaydate = dt) = 0 and rownum=1) from select( CASE
WHEN MAP.action = 'R'
THEN CASE
WHEN fmap.frequencyseq = 1
THEN trn.tradedate+ fmap.lapseday
WHEN fmap.frequencyseq = 3
THEN trn.tradedate + fmap.lapseday
END
ELSE NULL
END lapsedate
from option_upload_aud trn, trn_documentstatic MAP,
trn_followupstatic fmap)

I get error when lapsedate is invalid identifier in the nested correlated subquery.This example is to find the next non holiday from the holiday table.
Tom Kyte

Followup  

September 04, 2013 - 5:58 pm UTC

still no go, you reference a table trn_documentstatic that I don't have

that and YOUR INSERTS in the original do not actually work, they are not syntactically correct.



how about you also explain in text what you are trying to do. "This example is to find the next non holiday from the holiday table." is not very precise.


tell us - in words - what it means to find the "next non holiday"

Behaviour seems to have changed with 12

August 22, 2016 - 8:48 am UTC

Reviewer: Thomas from Germany

Easy explanation to a strange limitation that puzzled me quite some times over the last years.

But actually it seems, that things have changed with Oracle 12, where the limitation disappeared -or did the ANSI standard change along?

Connor McDonald

Followup  

August 22, 2016 - 10:24 am UTC

Yes, 11.2 and 12c have some improvements in that regard.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.