Asked and answered... ORA-1445

From: Tornblad, John <JTornblad_at_emdeon.com>
Date: Thu, 6 Dec 2012 18:33:20 -0600
Message-ID: <0EC8AF65BC8E9A4E9C4EB86F1882C96E05509995_at_ZBNAAEEX052.na.webmd.net>

Spent several hours on this today... an ORA-1445 "cannot select ROWID from, or sample, a join view without a key-preserved table" in one environment but not another on (what we thought) were the same objects. Vexing! Shared for the benefit of future searchers on ORA-1445.

Bottom line is be careful where your join data comes from! If a join view can be satisfied completely through an index (no table access), Oracle does not consider it "polluted", i.e., it's still a key-preserved row source. We had an index on a small table in development that did not exist in test, but that's all it took for the same query to not even parse in test. Neat trick... nice example how an index can completely influence whether Oracle thinks a query is valid or not! This felt a little like sighting a rare bird.

-john

• TEST CASE setup create table x_test_a (foo varchar2(10), bar varchar2(10)); create table x_test_b (bar varchar2(10)); create unique index i_test_b_pk on x_test_b (bar);
• #1 WORKS! create table x_test as with a as (select x.rowid from x_test_a x, x_test_b b where x.bar = b.bar) select foo from a, x_test_a x where x.rowid = a.rowid;
• cleanup drop table x_test purge;
• now... no index on X_TEXT_B drop index i_test_b_pk;
• #2 Same CTAS as above... DOES NOT WORK! Does not even PARSE!
• ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table create table x_test as with a as (select x.rowid from x_test_a x, x_test_b b where x.bar = b.bar) select foo from a, x_test_a x where x.rowid = a.rowid;
• #3... index still dropped
• alias the rowid... WORKS! create table x_test as with a as (select x.rowid row_id from x_test_a x, x_test_b b where x.bar = b.bar) select foo from a, x_test_a x where x.rowid = a.row_id;
• dispose of test objects drop table x_test_a purge; drop table x_test_b purge; drop table x_test purge;

</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-l Received on Fri Dec 07 2012 - 01:33:20 CET