JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 23: Number Crunching.


Materialized views fixed bugs (6th Feb 2002)

Materialized views and Analytic Function(25th Feb 2001)




Back to Book Index



Materialized views fixed bugs (6th Feb 2002)

This note appears as a consequence of reading the bug fixes note that came with a recent upgrade to Version There are bugs in 8.1.7 that are fixed in this release, so if you are running an earlier release, be careful. . Note - At the time of writing this addendum, I have not visited Metalink to read up the details of any bugs reported.

Bug 1664189 - Query rewrite does not occur if base table has a FUNCTIONAL index on it. (Is this a side-effect of the conflict between the required values for query_rewrite_integrity when using function based indexes and the required value for particular types of query rewrite ?) Bear in mind that indexes with descending columns are also considered to be function-based indexes, so they also may hit this bug.

Bug 1898834 - During query rewrite with materialzed view, an outer join can become an inner join ,and thereby caused incorrect results in some cases.

Bug 1873265 - When a materialized view has a group by but there is no group by in the query,then a select count(*) may return null instead of 0.

Top of page


Materialized views and Analytic Functions

I have often pointed out that Oracle is stuffed full of useful features that work well independently, but sometimes cause unfortuate side-effects or accidents when mixed. I came across one such oddity on Metalink recently whilst researching a completely different problem. The bug number is 1503466 - Mixing Query Rewrite with RANK() causes "ORA-03113 end of file on communication channel". Tested against various levels of 8.1.6 and 8.1.7. This bug has be 'closed as a duplicate of 1503466', but I wasn't able to see the base bug.

If you do look this one up, don't be put off that the 'small test-case' supplied to demonstrate the problem looks a little unrealistic; it is presumably the smallest, simplistic example the analyst contrived, rather than a real example of what the developer was actually trying to do.

Essentially a simple declaration of a materialized view such as the following caused a crash if a session executed a query that could be satisfied immediately from the materialized view.

                    create materialized view mv_emp
        enable query rewrite
               deptno, ename, sal, comm, 
               rank() over (
                       partition by deptno
                       order by sale desc, comm
               ) as rk
        from emp;


Top of page



On average, the time it usually takes me to find an error of some sort (sometimes just an error of omission or age) in an Oracle book is about 5 minutes. I found this to be true even of my own book. When I received the first copies fresh off the press, I flipped the book open, happened to hit this chapter, and read this SQL (p. 520):

        select {complicated expression} from tableX sample blocks (1);

If you have tried to use a variant of this SQL, you will have discovered that there is a spurious 's'. It should read:

        select {complicated expression} from tableX sample block (1);.

Typo: (p.525): In the where clause the table alias is given as “sql” instead of “sal

Back to Book Index