Asked and answered... ORA-1445
- From: "Tornblad, John" <JTornblad@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 Dec 2012 18:33:20 -0600
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
