ORA-01446 - cannot select ROWID from view with DISTINCT, GROUP BY, etc

Question

I created a view that has a distinct in the select clause.

When I try to select all the records with "select * from view" I get the following error:

ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc.

I was searching why this happens and came to this:

You tried to create a view that included a ROWID in the SELECT statement as well as a clause such as DISTINCT or GROUP BY. This is not allowed. Reference

This is awkward because the select stament in the view does not selects the rowid and it isn't being used in any other clause (where, order, etc..)

Any idea on this?

Update

I'm not able to post the query itself but i'm posting a look-a-like. Here it is:

SELECT DISTINCT t1.c1 TABLE1_C1,
t1.c2 TABLE1_C2,
t1.c3 TABLE1_C3,
t1.c4 TABLE1_C4,
t1.c4 TABLE1_C4,
t1.c5 TABLE1_C5,
t1.c6 TABLE1_C6,
t1.c7 TABLE1_C7,
t1.c8 TABLE1_C8,
t2.c1 TABLE2_C1,
t2.c2 TABLE2_C2,
t2.c3 TABLE2_C3,
t2.c4 TABLE2_C4,
t2.c5 TABLE2_C5,
t3.c1 TABLE3_C1,
t2.c6 TABLE2_C6,
t4.c1 TABLE4_C1,
t4.c2 TABLE4_C2,
t4.c3 TABLE4_C3
  FROM table1 t1
  LEFT JOIN table2 t2
  ON t1.c1 = t2.c7
  left JOIN table4 t4
  ON t4.c4 = t2.c1
  LEFT JOIN table3 t3
  ON (t2.c1 = t3.c2
  AND t2.c8 = t3.c3
  AND t2.c9 = t3.c4)
  WHERE (t2.cp5  = 0 or t2.cp5 is null)
  AND (t2.c3  =
    (SELECT MAX(c3)
    FROM table2 s_t2
    WHERE s_t2.c3 LIKE t2.c3
    AND s_t2.c7 = t1.c1
    ) or t2.c3 is null)

Solution 1

If you use distinct or group by several rows can qualify the condition. So different executions would return different row id's and the other question if several rows qualify which one would you return?

This simply doesn't make sense

Solution 2

Another possibility: Having just suffered through this error the past 3 days, I have another combination of options that might cause this.

1. your query uses the ANSI-style joins and
2. one or more of the tables involved is a materialized view or table that has QUERY REWRITE option granted

You may get the Oracle error:

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

We finally discovered that 2 of the tables in a large query had this option set and was throwing this error. The permission had been granted to our schema for these two tables.

It took so long to determine this was the cause because, 1) the tables are owned by another team, and 2) the tables had granted QUERY REWRITE permission to our schema, but the query (compiled in a daily job) didn't fail until 3 weeks later, after a system update.

The team that granted this right has revoked it, but the problem persists. So our short-term work-around for the daily job is to add the following to the action:

ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE;

Follow-up Questions we have:

1. We are still pondering how to force the optimizer to re-parse the query to recognize we no longer have this grant, but it might take a system reboot.

2. While I recognize a materialized view produces a physical table, I'm also still asking myself why a table that's not part of an mview would be granted this right.

Solution 3

Not sure what the OP was doing since there is no ROWID in the query. But this may help - just add a column alias:

SQL>select  * from (select rowid from dual union select rowid from dual);
select  * from (select rowid  from dual union     select rowid from dual)
        *
ERROR at line 1:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.


SQL>select * from (select rowid as row_id from dual union select rowid from dual);

ROW_ID
------------------
AAAAB0AABAAAAOhAAA

1 row selected.