Re: Query + Materialized View

Can wrote:

> Hi,
>
> I've got following query:
>
> SELECT upper(url), TRUNC(a.date) as day, COUNT(DISTINCT a.IP) as viewed
> FROM accesslog a
> WHERE robotid IS NULL
> GROUP BY upper(url), TRUNC(a.date);
>
> [trunc(date) is bitmap-indexed
> robotid is indexed
> upper(url) is indexed
> ip is indexed]
>
> Actually robotid, and upper(url) should also be bitmap indexed (because
> there are few distinct ones), but the table perpetually receives inserts
> (sometimes 5-10 per second), so I thought I should go with b-tree indexes.
> Any opinions to that?
>
> The query is supposed to evaluate access to a website and takes about 10
> seconds for a table with 850000 rows. I'm trying to get it to run under 0.5
> seconds.
>
> I've tried materialized views but
> a) the count(DISTINCT ...) didn't work ("too complex query"), it only worked
> when I left out "distinct"
> b) the "fast refresh" was anything but fast (I thought a fast refresh would
> be so fast, that I could do it on every insert automatically) - it took
> perhaps half a minute, perhaps even more
>
> Is b) normal? Or should it really be fast (like 0.2 seconds)? (I've little
> experience with mviews)
>
>

[...]
>
> Can Oezdemir
>
>

Like any query tuning task, you should begin with some EXPLAIN PLAN output and work from there using reproducible test cases.

The IS NULL predicate might be your worst offender. You already thought of using bit-map index (the only type of index that stores NULL). Another option is a function-based index on robotid using NVL() to map nulls to a special value, and then change the query to use "where NVL(robotid, special_value) = special_value" instead of IS NULL. This requires Enterprise Edition IIRC, and query_rewrite_enabled=true. Do some tests.

Materialized views with aggregation have some specific requirements for fast refresh to work correctly, read the docs carefully. In OEM, there is a menu choice to "explain mview" under Summary Management which details which mview features do and don't work with your mview, and why.   You can get the same information using the DBMS_MVIEW package.

--Mark Bole Received on Thu Sep 09 2004 - 12:54:50 CDT