“ORA-01445: cannot select ROWID from, or sample, a join without a key-preserved table”
Unfortunately, even if the bug was solved by Oracle, it seems the update was missed on some servers and the bug haunts my queries almost on a daily basis.
Having an SQL Server background and, for code clearness, I prefer ANSI 92 JOIN syntax:
instead of using the old fashioned writing:
In theory the two queries should provide the same output and have, hopefully, similar performance. The problem with ANSI 92 syntax is that, on some Oracle installations, when the number of joins exceeds a certain limit, usually greater than 7, the above error is thrown.
SELECT A.column1, B.column2 FROM table1 A JOIN table2 B ON A.column1 = B.column2
instead of using the old fashioned writing:
SELECT A.column1, B.column2 FROM table1 A , table2 B WHERE A.column1 = B.column2
In theory the two queries should provide the same output and have, hopefully, similar performance. The problem with ANSI 92 syntax is that, on some Oracle installations, when the number of joins exceeds a certain limit, usually greater than 7, the above error is thrown.
What one can do is to reduce the number of joins to the main table by restructuring the query and grouping multiple tables into subqueries, which are then joined to the main table. For the tables from which is returned only one column, one can move the table into the SELECT statement.
Happy coding!
