Does Oracle correlate a subquery nested at any level?

Question

I see on different questions to this forum about this same topic, that a canned and frequently appearing response is:

No, Oracle doesn't correlate the subqueries nested more than one level deep (and neither does MySQL).

I would conclude from this that Oracle does correlate a subquery at one level deep. However, I have the below query, and it returns this error message:

ORA-00904: "CD"."FIELD6": invalid identifier

This query is important to be fashioned as it is, and is only a simplified version of the actual query which involves a UNION statement. When debugging why the error message was being returned, I reduced it to be this most simple version (below). I realize there may be alternate JOIN approaches to this most simple version, but such alternate approaches would not play out for the actual query. If the below can work, then seemingly our more complex query can also work. If the below can't work, then what am I reading incorrectly within Oracle's documentation and the "canned" answer above?

SELECT a.*
FROM
    main_detail cd INNER JOIN
    (
    SELECT
        Field1,
        Field2,
        Field3,
        Field4,
        Field5,
        Field6,
        Field7
    FROM other_detail x2
    WHERE x2.Field1 = cd.Field1 AND x2.Field6 = cd.Field6
    ) a ON
    a.Field1 = cd.Field1
    AND a.Field4 = cd.Field4
    AND a.Field6 = cd.Field6

The below is more similar to our actual need. The UNION needs to be as it is to allow the JOIN on the UNION-ed set returned, for which is being used as an INNER JOIN to limit the return record set:

SELECT h.*, a.*
FROM
    header h,
    (
    SELECT
        Field1,
        Field2,
        Field3,
        Field4,
        Field5,
        Field6,
        Field7
    FROM main_detail x1
    WHERE x1.Field1 = h.Field1 AND x1.Field6 = h.Field6
    UNION
    SELECT
        Field1,
        Field2,
        Field3,
        Field4,
        Field5,
        Field6,
        Field7
    FROM other_detail x2
    WHERE x2.Field1 = h.Field1 AND x2.Field6 = h.Field6
    ) a
    WHERE
        a.Field1 = h.Field1 AND
        a.Field6 = h.Field6

When comparing the above to not including the correlated subquery parameters, and testing the run in MS SQL, it improves the performance from 9 minutes to be 30-40 seconds, a very considerable improvement. I was hoping to experience the same gains in Oracle.

Below is as close as I can do for the actual code, without compromising client confidentiality:

SELECT DISTINCT 
    c.Field1,
    c.Field2,
    D.Field3,
    b.Field4,
    b.Field5,
    c.Field6,
    c.Field7 || '-' || cds1.Field8 AS status,
    b.paid,
    cds.Field8,
    p.Field9,
    p.Field10,
    c.Field11,
    c.Field12 AS provider_name
FROM
    header c,
    (
    SELECT
        a.*,
        cd.paid,
        cd.Field15,
        cd.BigList,
        cd.allowed,
        cd.copayment,
        cd.coinsurance
    FROM
        header_detail cd,
        (
        SELECT
            Field1,
            Field4,
            '' AS revenue_code,
            Field20,
            Field5,
            Field14,
            location_code,
            ServiceList
        FROM header_other_detail x1
        WHERE x1.Field1 = cd.Field1 AND x1.Field14 = cd.Field14
        UNION
        SELECT
            Field1,
            Field4,
            revenue_code,
            Field20,
            Field5,
            Field14,
            '' AS location_code,
            ServiceList
        FROM inst_claim_detail x2
        WHERE x2.Field1 = cd.Field1 AND x2.Field14 = cd.Field14
        ) a
    WHERE
        a.Field1 = cd.Field1
        AND cd.Field1 = c.Field1
        AND a.Field20 = cd.Field20
        AND a.Field14 = cd.Field14
        AND cd.Field14 = c.Field14a
    ) b,
    (
    SELECT
        Field1,
        Field14,
        Trim(
            Trailing ',' FROM
            ch.icd9_1  || ',' ||
            ch.icd9_2  || ',' ||
            ch.icd9_3  || ',' ||
            ch.icd9_4  || ',' ||
            ch.icd9_5  || ',' ||
            ch.icd9_6  || ',' ||
            ch.icd9_7  || ',' ||
            ch.icd9_8  || ',' ||
            ch.icd9_9  || ',' ||
            ch.icd9_10 || ',' ||
            ch.icd9_11 || ',' ||
            ch.icd9_12
            )
        AS Field3
    FROM prof_claim ch
    WHERE ch.Field1 = c.Field1 AND ch.Field14 = c.Field14a
    UNION
    SELECT
        Field1,
        Field14,
        Field3
    FROM inst_claim x3
    WHERE x3.Field1 = c.Field1 AND x3.Field14 = c.Field14a
    ) d,
    (
    SELECT
        Field1,
        Field14,
        Field9,
        Field10,
        Field18,
        refund_amount,
        Field15
    FROM payment_detail
    ) p,
    (SELECT * FROM Codes WHERE code_type='19') cds,
    (SELECT * FROM Codes WHERE code_type='28') cds1
WHERE
    c.Field17 = 'T00000370'
    AND c.Field1 = b.Field1 AND c.Field14a = b.Field14
    AND c.Field1 = d.Field1 AND c.Field14a = d.Field14
    AND b.Field14 = p.Field14(+) AND b.Field1 = p.Field1(+) AND b.Field15 = p.Field15(+)
    AND b.BigList = cds.Field16(+) AND b.Field14 = cds.Field14(+)
    AND c.Field7 = cds1.Field16(+) AND c.Field14a = cds1.Field14(+)
ORDER BY Field1;

Oracle 12c supports correlation over more than one level deep. All preceding versions only one level. — Husqvik
– Husqvik, Commented May 19, 2016 at 13:43

Marmite Bomber · Accepted Answer · 2016-05-19 13:34:39Z

1

In the subquery

SELECT
    Field1,
    Field2,
    Field3,
    Field4,
    Field5,
    Field6,
    Field7
FROM other_detail x2
WHERE x2.Field1 = cd.Field1 AND x2.Field6 = cd.Field6

check the where condition. If removed completely or at least removing the cdreference - the query works.

answered May 19, 2016 at 13:34

Marmite Bomber

21.3k4 gold badges32 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Boneist · Accepted Answer · 2016-05-19 16:52:36Z

0

If you're joining a subquery to a table (or another subquery) then all the join conditions must be in the ON clause, if you're using ANSI style joins.

The reason why your query is failing is because the a subquery's scope does not extend outside of itself. It is entirely separate to the scope of the main_detail table, as you are trying to join the tables, not correlate them. As Marmite Bomber's answer suggests, the subquery needs to be able to run on its own, which your attempted version doesn't.

What you need to do is move the correlated filters to the ON clause, like so:

SELECT a.*
FROM
    main_detail cd INNER JOIN
    (
    SELECT
        Field1,
        Field2,
        Field3,
        Field4,
        Field5,
        Field6,
        Field7
    FROM other_detail x2) a ON
    a.Field1 = cd.Field1
    AND a.Field4 = cd.Field4
    AND a.Field6 = cd.Field6
    and a.Field1 = cd.Field1 AND a.Field6 = cd.Field6

which is, of course, identical to:

SELECT a.*
FROM
    main_detail cd INNER JOIN
    other_detail a ON
      a.Field1 = cd.Field1
      AND a.Field4 = cd.Field4
      AND a.Field6 = cd.Field6

With regards to your query with the UNION, this should help give you some performance benefit over your original query:

SELECT *
FROM   (SELECT h1.*,
               x1.field1,
               x1.field2,
               x1.field3,
               x1.field4,
               x1.field5,
               x1.field6,
               x1.field7
        FROM   main_detail x1
               inner join header h1 on (x1.Field1 = h1.Field1 AND x1.Field6 = h1.Field6)
        UNION
        SELECT h2.*
               field1,
               field2,
               field3,
               field4,
               field5,
               field6,
               field7
        FROM   other_detail x2
               inner join header h2 on (x2.Field1 = h2.Field1 AND x2.Field6 = h2.Field6));

edited May 19, 2016 at 16:52

answered May 19, 2016 at 13:36

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

7 Comments

Boneist Over a year ago

I've updated my answer with an alternative suggestion. Out of curiosity, how many rows are in your header and details tables? Also, are you expecting to return the same data from both your main and other detail tables? Because if the data is completely separate, then you can use a UNION ALL and save yourself a sort/distinct operation.

Gregory Hart Over a year ago

Nice, but the actual query has 8 subslect tables and 5 main tables, so it can't be easily done with just joins without losing the integrity of the UNION to act as a limiting criteria against the main table. So, the UNION is one of 4 other similar UNIONS. As indicated in the edited post above, the performance changes from 9 minutes to be 30-40 seconds when testing in MS SQL. It takes a very similar run time in Oracle at around 9 minutes. We want to gain the same advantage as what MS SQL is able to accomplish.

Boneist Over a year ago

Perhaps if you could update your question with an example query that more accurately reflects your actual query, we might be able to suggest improvements that you could make.

Gregory Hart Over a year ago

Over 400 million rows in the header, and over 1.4 billion rows in the detail. Hence why I am also quickly becoming hateful of Oracle for not allowing me to set a clustered index on my Header_ID field of the detail table. Managing the clustered index of a table in MS SQL is the "magic" that has traditionally made me the superstar when optimizing a database.

Boneist Over a year ago

A quick google reveals: dba.stackexchange.com/questions/7951/… - perhaps recreating the header table as an IOT in Oracle may give you some additional performance benefit? This is assuming that header is not a wide table.

|

Collectives™ on Stack Overflow

Does Oracle correlate a subquery nested at any level?

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related