Urgent !! Finding New Cookies

[coolmandba]

Hi all,

I come across the following scenario:
We have a table which store weblog records, say the table is called 'Weblog' 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 (run daily)

[marist89]

A report is:

SELECT * FROM WebLog

If you were more specific in what you were looking for, the answers might be clearer.

[chrisrlong]

You could have simply replied to this thread instead of starting another.

http://www.dbasupport.com/forums/sho...threadid=29935

- Chris

[marist89]

Reopened...

[stecal]

Originally posted by marist89
A report is:

SELECT * FROM WebLog

If you were more specific in what you were looking for, the answers might be clearer.

That one cracked me up.

[coolmandba]

Thanks for all your reply, this is the wrong post....the content wasn't completed....I pressed the submit button by accident while I was still writing this question.

[coolmandba]

Sorry for the confusion here is the correct content:

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.

[coolmandba]

Please read the above above for complete question:

....waiting....

[marist89]

How about a query plan?

[coolmandba]

What exactly do you mean by query plan...

Can you be more specific?