!!Please Help!! Query a table with 8 million rows

[coolmandba]

Hi all,

I come across the following scenario:
We have a table which store weblog records, say the table is called 'Weblog' which contains many columns and three of them are:

*Logdate
*Sessionid
*Cookieid

Sessionid is unique, and cookieid can be duplicated
For example:

logdate sessionid cookieid
... ... ....
... ... ....
2002/09/10 12345 78888
2002/09/10 23456 78888
2002/09/11 74729 78888
2002/09/11 32433 99999
2002/09/12 54533 33333

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?

Thanks in advance.

[marist89]

Closing, duplicate thread. http://www.dbasupport.com/forums/sho...threadid=29933