Query + Materialized View

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)

Then I thought of range-partitioning the table by date...it could enhance other queries but as this is a count operation that would touch all partitions, I think it won't signifantly enhance the execution time.

There must be a faster way of showing the daily views (including the current day). Any suggestions to that? Or db design suggestions?

Thank you in advance.

Can Oezdemir Received on Thu Sep 09 2004 - 02:53:27 CDT