RE: Asked and answered... ORA-1445
Date: Fri, 7 Dec 2012 10:08:41 -0600
Message-ID: <0EC8AF65BC8E9A4E9C4EB86F1882C96E05509A65_at_ZBNAAEEX052.na.webmd.net>
| The reason why you got ORA-1445 is because you didn't use alias for
| rowid and Oracle parser interpreted it as you trying to fetch pseudo
| rowid from the inline view and hence it bis subjected to key preserved
| table requirement.
Sai, that's definitely the issue with the second test case. But the first one, which works, did not alias the rowid. That, to me, was the surprising thing... in that case the projected column needed was satisfied from the index and then the consideration of a key-preserved table was not an issue. The third test case shows your recommendation.
The whole reason I went down this path was to avoid a massive hash join (a VERY large one... we just don't have the resources, and as far as I can tell Oracle is not stingy with its use of TEMP space... ). So I am working with forcing a Nested Loop (although this test case didn't get into that). I hate using rowids for anything but I think I just ran into my first situation where it was unavoidable. And then the "subtleties" with rowid started popping up.
-john
</pre>This message is confidential, intended only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution, or copying of this message is strictly prohibited. If you receive this message in error or are not the named recipient(s), please notify the sender by return email and delete this message. Thank you.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Dec 07 2012 - 17:08:41 CET