Thursday, August 10, 2017

No subqueries allowed in materialized view? No problem!

Have you ever run into the following error when trying to create a materialized view?

ORA-22818: subquery expressions not allowed here

Yes, it is true: you cannot have a scalar subquery in the SQL statement used to create your materialized view. Here's an example of what won't work (note: I am not claiming this query makes any sense):

CREATE MATERIALIZED VIEW hr_demo_mv
AS
     SELECT employee_id,
            (SELECT MAX (hire_date)
               FROM employees ce) maxhd
       FROM employees t
/
ORA-22818: subquery expressions not allowed here
22818. 00000 -  "subquery expressions not allowed here"
*Cause:    An attempt was made to use a subquery expression where these
           are not supported.
*Action:   Rewrite the statement without the subquery expression.

Rewrite my query without the subquery expression? But I just spent an hour putting it all together. Works great. Gives me exactly the results I want and need. Rewrite it? ARGH.

Calm yourself. While it is true that you will need to "rewrite the statement" that you provide in your CREATE MATERIALIZED VIEW statement, you will not have to abandon your subqueries and all your hard work.

All you have to do is create a view with the subqueries, and then create your materialized view based on the view:

CREATE VIEW hr_demo_v
AS
     SELECT employee_id,
            (SELECT MAX (hire_date)
               FROM employees ce) maxhd
       FROM employees t
/

View HR_DEMO_V created.

CREATE MATERIALIZED VIEW hr_demo_mv
AS
     SELECT * FROM hr_demo_v
/

Materialized view HR_DEMO_MV created.

I recommend this approach (the materialized view is "nothing more" than a select from a view), even if your materialized view query does not contain a subquery or anything else that would preclude the materialized view from being created.

By taking this approach, you can change the contents of the materialized view with the next refresh by doing nothing more than changing the query (instead of dropping and re-creating the materialized view).

5 comments:

  1. That is pretty cool. Thanks!

    And it's so simple it makes me feel stupid for not thinking of this myself. Thanks for that too ;-)

    I am definitely going to use this, starting tomorrow morning.

    ReplyDelete
  2. Hello Steven, All,

    When using the approach of basing a naterialized view on a view instead of directly on the base tables,
    you should be aware that this automatically makes the materialized view to be considered COMPLEX,
    and thus NOT fast refreshable.

    This is true even for very simple views, like for example a view that selects all the columns of a single table.

    That is, if the table has a materialized view log defined, then a materialized view based directly on the table
    will be fast-refreshable, while a materialized view based on a view that selects from the table will NOT be fast-refreshable.

    This behavior may be relevant or not, based, of course, on the specifics and performance requirements of your application.


    Thanks a lot & Best Regards,
    Iudith

    ReplyDelete
  3. ok, this query doesn't make any sense, however, the "GROUP BY"-part of it doesn't even make any sense at all.
    It should be left away because it might be confusing

    ReplyDelete
    Replies
    1. Ha! Excellent point. That was left over from an earlier iteration of the query. I will remove it.

      Delete