Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

No Subqueries Allowed in Materialized View? No Problem!

DZone's Guide to

No Subqueries Allowed in Materialized View? No Problem!

''Subquery expressions not allowed here,'' eh? We'll see about that. Here is how to quickly rewrite your SQL statement to let your subqueries work in materialized views.

· Database Zone ·
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

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. It works great. It 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).

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:
database ,oracle database ,subqueries ,materialized views ,tutorial

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}