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.
Join the DZone community and get the full member experience.Join For Free
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).
Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.