Home » SQL & PL/SQL » SQL & PL/SQL » Using ANSI joins (10g Release 10.2.0.3.0)
Using ANSI joins [message #311787] Sun, 06 April 2008 18:05 Go to next message
balajimadhav
Messages: 3
Registered: April 2008
Junior Member
HI ,

I am using a view which joins with around 10 tables (each table has volume in terms of millions). When i try to add columns in any of the tables (any of the 10 tables in this case) then while creating the view i am getting the following error

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

When searching for the error it is found that this is an unpublished bug (using ANSI join) which can be resolved by applying 10.2.0.4 patchset .

For temporary solution there are two options 1) removing the ANSI format as ANSI has limitations with the number of columns selected in a view 2) explicitly mentioning the cols that is required for joins

eg: select <columns> from TableA
left outer join TableB
on (TableA.keycol=TableB.keycol)
....
....

The above can be changed as
select <columns> from TableA
left outer join (select keycol from TableB) TableB
on (TableA.keycol=TableB.keycol)
....
....

Please provide your suggestions whether option 2 is a best solution and also while selecting the required columns in JOIN will enhance the performance?

Re: Using ANSI joins [message #311797 is a reply to message #311787] Sun, 06 April 2008 21:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
balajimadhav wrote on Mon, 07 April 2008 09:05
When i try to add columns in any of the tables (any of the 10 tables in this case)

I don't understand what you mean by this. Are you saying that when you ALTER TABLE ADD COLUMN, it makes the view fail?

Ross Leishman
Re: Using ANSI joins [message #311885 is a reply to message #311797] Mon, 07 April 2008 04:40 Go to previous message
balajimadhav
Messages: 3
Registered: April 2008
Junior Member
When i alter the tables for addition of columns and while recreating the view i am getting such error.
Previous Topic: How to execute The Procedure Inside the Function?
Next Topic: dbms_sql.native
Goto Forum:
  


Current Time: Sat Dec 03 20:06:56 CST 2016

Total time taken to generate the page: 0.10247 seconds