Parse Order and Analysis

Over the years I’ve heard and learned quite a lot about how Oracle does stuff. Some of it was logical, some just details to remember (I have a really lousy memory, but somehow, I actually remember some of the details). Not too long ago I wrote a query and got a parse error, and that lead me to write this post (and a couple more that will follow).

What happens first during parsing?

When parsing a statement, Oracle first checks for syntax and then semantics. Syntax check means checking the words that compose the SQL statement itself, while semantic check is checking the actual objects in the database. So if I query a table called T1, during the semantic check, Oracle will check for T1 existence, along with the columns it has, etc. This process makes sense, right? I don’t have to actually check anything if the query is invalid. Let’s check that:

SQL> select * from t1 where a123456789012345678901234567890=1;
select * from t1 where a123456789012345678901234567890=1
                       *
ERROR at line 1:
ORA-00972: identifier is too long
SQL> select * from t1 where a1=1;
select * from t1 where a1=1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

We can see that the error in the first statement was that the identifier is too long, while the table doesn’t even exist (as you can see in the second statement). This means that Oracle didn’t go and check the object existence before throwing the error.
Now let’s check evaluation of different parts of the query. I created a table called T1 that has only one numeric column called F1.

SQL> select f2 from t1 where f2=1 order by f2;
select f2 from t1 where f2=1 order by f2
                        *
ERROR at line 1:
ORA-00904: "F2": invalid identifier
SQL> select f2 from t1 where f1=1 order by f2;
select f2 from t1 where f1=1 order by f2
       *
ERROR at line 1:
ORA-00904: "F2": invalid identifier
SQL> select f1 from t1 where f1=1 order by f2;
select f1 from t1 where f1=1 order by f2
                                      *
ERROR at line 1:
ORA-00904: "F2": invalid identifier
SQL> select f1 from t1 where f1=1 order by f1;
no rows selected

The first thing that Oracle checks is the predicates in the WHERE clause. Then the column list in the SELECT clause and the ORDER BY will be last.
What about multi-table select?

SQL> select * from t2,t3;
select * from t2,t3
                 *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from t2,t1;
select * from t2,t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Tables are evaluated from the last to the first.

Footnote

What I showed here is the parsing order. It’s important to understand that this doesn’t affect the performance of the database. The optimizer chooses a plan regardless the order of table or column existence check.
I will discuss performance related order of execution really soon…