RE: Asked and answered... ORA-1445
- From: Saibabu Devabhaktuni <saibabu_d@xxxxxxxxx>
- To: free <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 8 Dec 2012 01:49:07 -0800 (PST)
John,
Thanks for sharing your findings. The whole rowid discovery here is interesting.
I do think your first query is running fine due to key preserved table
condition being met (i.e. having an unique index or primary key). Oracle parser
is basically triggering key preserved table code path execution the moment it
see "ROWID" being selected from an inline view. Consider the below test case.
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);
insert into x_test_a values ('A', 'A');
insert into x_test_b values ('A');
commit;
with a as (select b.bar from x_test_a x, x_test_b b
where x.bar = b.bar
)
select x.bar from a, x_test_a x where x.rowid = a.rowid
/
with a as (select b.bar from x_test_a x, x_test_b b
where x.bar = b.bar
)
select x.bar from a, x_test_b x where x.rowid = a.rowid
/
with a as (select b.bar from x_test_a x, x_test_a b
where x.bar = b.bar
)
select x.bar from a, x_test_a x where x.bar = a.bar
/
First query will run fine and will return one record and it was subjected to
key preserved table requirement. Rowid predicate check on inline view always
refer to the rowid of the non key preserved table referenced in inline view,
this is really interesting by itself.
Second query will not return zero records and it was subjected to key preserved
table requirement. No rows returned because rowid referenced from inline view
belongs to x_test_a. This is the reason why simple oversight like not using
alias for rowid can cause result set to be dramatically different.
Third query will run fine (even though we joined x_test_a table twice) and it
is not subjected to key preserved table requirement since rowid from inline
view was not used.
Thanks,
Sai
http://sai-oracle.blogspot.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
