CODEWITHSUNDEEP
sqloracle-databasejoinlimit
Bilgin Kılıç
Bilgin Kılıç

Reputation: 9119

Oracle 22 Joins or over is not allowed in a select statement ? ORA-01445

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table

I have a long select statement on ORACLE 10g. According to this error statement, I have seen some answers on google. One of the answer is saying that ;

* limit on number of tables in join I 've run across an unusual bug (4204878/ 3765373/ 3004824) on Oracle 9.2.0.5. When more than 22 ANSII joins are done in a select statement an ORA-01445 occurs. According to Support "1- One so

I count the number of joins inside the whole select block; is 23 (after select and after where clause). The SP which has this "selec"t statement was working perfectly until I added this new join after where clause...

For short, I ve tested by disabling one of existing join and enabled my newly added join and SP worked.

What do you think are there really any limit ?

** I can't give you the web site addess since it is always found smearing by the users of StackOverflow..

Upvotes: 2

Views: 6274

Answers (2)

Christian
Christian

Reputation: 7320

I've solved that issue by just selecting the columns that I used on query.

From this:

select a.column1, b.column3
  from a
  join b on b.column2 = a.column2

To this:

select a.column1, b.column3
  from (select column1, column2 from a) a
  join (select column2, column3 from b) b on b.column2 = a.column2

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

I've run into these bugs a couple of times. It happened a lot in 9i with ANSI joins and i've found it happens less frequently in 10g.

One workaround is to rewrite the join to use the "old" join synthax, specifically outer joins as APC pointed out:

SELECT *
  FROM a, b
 WHERE a.a_id = b.a_id (+)

Upvotes: 7

Related Questions