ORA-00600 when Creating Materialized View-CodePudding

I'm trying to create a materialized view like using the following:

CREATE MATERIALIZED VIEW orders_mv AS
  SELECT *
  FROM (SELECT * FROM table1
        UNION
        SELECT * FROM table2
        ORDER BY DBMS_RANDOM.VALUE)
  WHERE rownum <= 5;

But it gives me an ORA-00600 and disconnects me with the usual disconnect error:

CREATE MATERIALIZED VIEW orders_mv
                   *
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 89299
Session ID: 11416 Serial number: 40331

We are running Oracle 12c. Is there some bug with creating a materialized view and using an order by clause in this fashion?

CodePudding user response：

This has nothing to do with materialized views. Did you try to run the select by itself, without creating a view? That would be the first step in trying to figure out what is going on.

In a query with set operations (such as UNION), the order by clause can only reference columns in the select list: by number, or if you listed the columns explicitly, then by name (or by alias if you used aliases).

Specifically, you can't order by an expression like dbms_random.value. You wouldn't even be allowed to order by an arithmetic expression (say, or string expression, etc.) that depends only on columns specifically named in the select list.

So: even if you named all the columns in the select list (instead of the poor practice of select *), you wouldn't be able to do what you tried. You would still only be allowed to order by columns in select - if you try to order "at the same level" as the set operation.

Instead, you will need three levels of select. In the most deeply nested subquery, perform the UNION but don't try to order. In the middle select statement order the result of the sub-subquery by dbms_random.value. And in the outer query filter by rownum. This will work with select * too (it's just a poor practice, but it is valid).

CodePudding user response：

Your problem can be reproduced in Oracle XE Version 18.4.0.0.0 on Windows (note that in case of ORA-0600this is relevant information)

.... 
No more data to read from socket

As diagnosed in other answer this works:

CREATE MATERIALIZED VIEW orders_mv AS
  SELECT *
  FROM (SELECT DBMS_RANDOM.VALUE rnd, table1.* FROM table1
        UNION
        SELECT DBMS_RANDOM.VALUE rnd, table2.* FROM table2
        ORDER BY 1)
  WHERE rownum <= 5;