Inner query with same source table as Outer Query

Question

I went through some PL/SQL codes and found a piece of query where I not actually get how it works. Hoping to get some technical advise from here.

The piece of query was shown as below:

SELECT a.ROWID
  FROM TableA a
 WHERE a.object_name IN ('HEADERS','LINES','DELIVERIES')
   AND a.change_type IN ('A','C')
   AND a.ROWID NOT IN (SELECT MAX (b.ROWID)
                         FROM TableA b
                        WHERE b.object_name       = a.object_name
                          AND b.change_type       = a.change_type
                          AND b.pk1               = a.pk1
                          AND b.object_identifier = a.object_identifier
                       );

From what I know, the inner query should run first (correct me if I am wrong) and then the inner query result will used for the outer query.

For the above query, how the inner query run as it needs data from the outer query (data from alias TableA a).

Hope to have some guidance on this as I am very fresh in PL/SQL development.

Thanks!

Aside: `MAX(ROWID)` is a bad idea. ROWID is Oracle's internal identifier for the location of a row on disk. This is like taking the maximum value of some pointers in C, and assigning meaning to that. Oracle might decide to put the row anywhere, so the results of this query are essentially random. See http://stackoverflow.com/questions/435109/what-can-cause-an-oracle-rowid-to-change — WW., Jul 15 '15 at 04:43

score 0 · Answer 1 · answered Jul 15 '15 at 04:31

It is not PL/SQL, just classic SQL statement. The purpose seams to be retrieve all the lines which are not the "last version" (biggest rowid for a couple pk1 and object_identifier)

The "not in" part will retrieve the max rowid for a couple (pk1 and object_identifier) and then, the outer query will retrive all the lines which are not the max rowid

In term of execution process, you can take a look at the explain plan to see what oracle is going to do.

score 0 · Answer 2 · answered Jul 15 '15 at 04:42

The inner query does not run first. Conceptually, you can think of it running like this:

Run the outer query,
For each row in the other query, run the inner query using specific values for the a.* columns
If the inner query for that row doesn't return anything, output the outer query row to the result set

Inner query with same source table as Outer Query

2 Answers2