Atypical problem with ORA-01445

errorsoraclequery

I have problem with following query Q1:

SELECT ADDRESS.STREET
     FROM  MYSALES.MYADDRESS ADDRESS 
 INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID 
 INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID

When we translate this to good old notation, Q2:

SELECT ADDRESS.STREET
FROM MYSALES.MYADDRESS ADDRESS 
    ,MYSALES.MYCOUNTRY MCOUNTRY 
    ,MYGLOBAL.COUNTRY COUNTRY
WHERE 
    MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID 
    AND COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID

The problems start when I wrap in view:

SELECT * FROM (Q1) or SELECT * FROM (Q2) results with error:

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

But for query Q3:

 SELECT * FROM  MYSALES.MYCOUNTRY MCOUNTRY
     INNER JOIN MYGLOBAL.COUNTRY COUNTRY ON COUNTRY.COUNTRY_ID = MCOUNTRY.COUNTRY_ID

Result of SELECT * FROM (Q3) is correct.

So the problem is in Q4:

  SELECT * FROM MYSALES.MYADDRESS ADDRESS 
    INNER JOIN MYSALES.MYCOUNTRY MCOUNTRY ON MCOUNTRY.MYCOUNTRY_ID = ADDRESS.MYCOUNTRY_ID

When I wrap the last query in a view, it fails, with the above error.

The columns that end with _ID, ~~are~~ should be primary or references key.

Could you please guide me a hint where should I look for solution ?

Best Answer

The solution of the problem is that table MCOUNTRY did not have the primary key. It was only configured with unique constraint that was called is the same way that primary keys. Even when the unique column was used in query Oracle could not provide expected result.

Something obvious but still lesson to learn, unique constraint is not the same as primary key where it comes to joins.

Have a nice day.

Related Solutions

Oracle – Non Key-Preserved Table Should Be

You can update a join in Oracle if the following conditions are met:

Only one base table is updated
All other tables are key-preserved: each of them must have at most one row for each row of the base table.

(additional restrictions on updating views apply)

In your example you update table PG_MACHINE only. Oracle has to make sure that for a single row of this table, only one row of the other can be found. This seems to be the case since you have a PK on PG_LABLOCATION.LABLOCID. Therefore you should be able to update the join. See for example this SQLFiddle with a similar setup.

In your case you should either:

make sure that the primary key is enabled, validated, not deferrable (interestingly, a deferrable constraint prevents Oracle from updating the join!)
use MERGE if PG_LABLOCATION.LABLOCID is unique for the relevant query. MERGE is less strict than update with joins and will only return an error if there is actually a duplicate in the result set (whereas UPDATE will fail if there is the possibility of a duplicate).

review your query, since you don't need values from the parent table in the SELECT clause, you could rewrite it as a semi-join (that guarantees that no duplicate will be generated):

UPDATE (SELECT mac.in_use, mac.update_time
          FROM pg_machine mac
         WHERE mac.lablocid IN (SELECT loc.lablocid 
                                  FROM pg_lablocation loc 
                                 WHERE loc.dnsname = 'value')
           AND to_date('02-JAN-2013') > mac.update_time)
   SET in_use = 1 - MOD(101, 2), 
       update_time = to_date('02-JAN-2013');

This could be rewritten as:

UPDATE pg_machine mac
   SET in_use = 1 - MOD(101, 2), 
       update_time = to_date('02-JAN-2013')
 WHERE mac.lablocid IN (SELECT loc.lablocid 
                          FROM pg_lablocation loc 
                         WHERE loc.dnsname = 'value')
   AND to_date('02-JAN-2013') > mac.update_time;

In this case I would go with the third option: in general you can't update the parent in a parent-child join.

Oracle – Update Row of One Table Using Rows of Another Table

You could try MERGE instead of UPDATE. It is often faster and (in my opinion) more clear than UPDATE when updating a table based on another table

merge into FA_SUBLEDGER_MST s1
using (
    select SL_CODE, OFFICE_ID, OFFICE_TYPE
    from EMPLOYEE_MST
    where OFFICE_TYPE != 'DHB'
) emp
on (s1.SL_CODE = emp.SL_CODE)
when matched then update set 
    s1.OFFICE_ID = emp.OFFICE_ID,
    s1.OFFICE_TYPE = emp.OFFICE_TYPE
where sl.STATUS = 'A'
    and (s1.OFFICE_ID <> emp.OFFICE_ID
    or  s1.OFFICE_TYPE <> emp.OFFICE_TYPE);

Make sure that using-subquery doesn't return duplicate rows for each SL_CODE

Also your last example is missing the STATUS = 'A' condition

Best Answer

Related Solutions

Oracle – Non Key-Preserved Table Should Be

Oracle – Update Row of One Table Using Rows of Another Table

Related Question