I decided to take a chance (installing Oracle is a big leap :-) ) and see how things match in Oracle.
I decided to run the following query:
SELECT deptno, dname, loc, (SELECT COUNT(*) FROM emp WHERE emp.deptno = dept.deptno) AS empcount FROM dept WHERE deptno = 20
Please note that I run in on a database that had (total) maybe a 100 records, so the results may be skewed.
Like in the SQL Server case, we need to create an index on the FK column. I did so, after which I got:
Then I dropped that index and create a simple view:
CREATE VIEW depswithempcount AS SELECT deptno, dname, loc, (SELECT COUNT(*) FROM emp WHERE emp.deptno = dept.deptno) AS empcount FROM dept
Querying on top of that gives me the same query plan as before. Trying to create a materialized view out of this fails, because of the subquery expression, I’ll have to express the view in terms of joins, instead. Like this:
SELECT dept.deptno, dname, loc, COUNT(*) empcount FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno WHERE dept.deptno = 20 GROUP BY dept.deptno, dname, loc
Interestingly enough, this is a different query plan than the subquery, with SQL Server, those two query exhibit identical query plans.
Now, to turn that into an materialized view.
CREATE materialized VIEW deptwithempcount AS SELECT dept.deptno, dname, loc, COUNT(*) empcount FROM dept left join emp ON dept.deptno = emp.deptno GROUP BY dept.deptno, dname, loc
And querying on this gives us very interesting results:
select * from deptwithempcount where deptno = 20
Unlike SQL Server, we can see that Oracle is reading everything from the view. But let us try one more thing, before we conclude this with a victory.
update emp set deptno = 10 where deptno = 20; select * from deptwithempcount where deptno = 20But now, when we re-run the materialized view query, we see the results as they were at the creation of the view.
There appears to be a set of options to control that, but the one that I want (RERESH FAST), which update the view as soon as data changes will not work with this query, since it consider it too complex. I didn’t investigate too deeply, but it seems that this is another dead end.