I have created a materialized view that looks exactly like a select I am doing, minus most of the where clause but including those fields in the select.
The plan does not use it though. The message in the planner, Advanced Details, is materialized view considered but "Base table mismatch". Which is not the case as far as I can see.
Is there something special I should know?
asked 17 Aug '11, 16:50
The only reasons you get table mismatch with the definitions above is that the column E.INSERTDATE in the query (where E is the base table "Companies") is not in the view's SELECT list: we cannot cover the columns needed in the queries with the columns projected out by the materialized view.
Note also that the ORDER BY in the materialized view definition is ignored by the server: the materialized view is stored just as a base table and order of the rows is not guarantee for base tables.
answered 26 Sep '11, 16:04
Please post the view definition and the query.
answered 18 Aug '11, 07:22