This table contains approx. 8 million rows, what I need to do here is to produce a report which looks something like:
logdate "no. of new cookies" "total distinct cookies"
2002/09/10 50 5000
2002/09/11 69 6030
2002/09/12 70 5920
To find total distinct cookies for each date is an easy part, but the problem is to find new cookies(that means one never appeared before compares to all the ones appeared up until now), it takes forever to run (with more than 8 million rows).
To find distinct new cookies I wrote:
select distinct trunc(logdate),cookieid from weblog w1
where not exisits(select 1 from weblog w2 where w1.cookieid=w2.cookieid and trunc(logdate)<=trunc(logdate-1)
note:I also created non-unique index on 'cookieid'.
This query takes forever, so can someone help me here?
What's the best way to make one such report?