How to preserve key when joining to a preference table in Oracle

Question

I have a table of customers, and a table of customer preferences, where:

create table customer (
  customerId number,
  name varchar2(200),
  primary key (customerId)
);

create table pref (
  preftype number,
  customerId number,
  prefval varchar2(200),
  primary key (preftype, customerId)
);

So basically the pref table records the customer's preference based on each preference type. customerId is unique for each preftype.

Theoretically the following join should not lose the key-preserved property, but I am getting "ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table." Anyway I can rewrite the query to maintain the key-preservedness?

select rowid, vi.* from (
  select c.customerId, c.name, p.prefval
  from customer c, (select * from pref where preftype = 1) p
  where c.customerId = p.customerId (+)
) vi;

---

Restrictions / requirements:

In our case a query engine is building that outer query (the one with rowid) around the inner query, so we don't have as much control.

We'd like to have a query that can be wrapped around with something like:

select rowid, v.*
from
    ( 
    -- query
    ) v ;

Answer 1

No, it is not. It is not guaranteed (well, it is, with preftype=1, but that is in the subquery, and that is not good enough for the optimizer) that your query returns at most 1 row for each key value from the base table. You need a unique constraint on customerid to make this work:

select rowid, vi.* from (
  select c.customerId, c.name, p.prefval
  from customer c, (select * from pref where preftype = 1) p
  where c.customerId = p.customerId (+)
) vi;

ERROR at line 3:
ORA-01445: cannot select ROWID from, or sample, a join view without a
key-preserved table

alter table pref add unique(customerid);

Table altered.

select rowid, vi.* from (
  select c.customerId, c.name, p.prefval
  from customer c, (select * from pref where preftype = 1) p
  where c.customerId = p.customerId (+)
) vi;

no rows selected

This however may not be feasible for you, as a customer can have multiple different preftypes with your original design.

This works without the unique constraint (but not the same result):

alter table pref drop unique(customerid);

Table altered.

select rowid, vi.* from (
  select c.customerId, c.name, p.prefval
  from customer c, pref p
  where c.customerId = p.customerId (+)
  and p.preftype = 1
  ) vi;

no rows selected

Or be more specific with the rowid:

select vi.* from (
  select c.rowid as c_rowid, c.customerId, c.name, p.prefval
  from customer c, (select * from pref where preftype = 1) p
  where c.customerId = p.customerId (+)
) vi;

no rows selected

Answer 2

I don't see the reason for having doubly nested subqueries when a simple LEFT JOIN can give the same result:

select c.customerid, c.name, p.prefval   
       -- , c.rowid                        -- this errs
from customer c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;

Now, when we add c.rowid in the SELECT list, Oracle parser/optimizer is not able to identify that every row of customers is going to be joined with - at maximum - 1 row from pref. So it throws an error.

We can convert the LEFT JOIN to a subquery though - and no error is thrown:

-- query 2 --
select c.customerid, c.name, 
       ( select p.prefval
         from pref p
         where c.customerid = p.customerid
           and p.preftype = 1 
       ) as prefval,
       c.rowid                               -- but works here
from customer c ;

or putting the customers in a derived table and then left join to pref:

-- query 3 --
select c.customerid, c.name, p.prefval, c.rowid                       
from 
    (select c.*, c.rowid from customer c) c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;

Tested at rextester.com