Urgent !! Finding New Cookies
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Urgent !! Finding New Cookies

  1. #1
    Join Date
    Dec 2000
    Posts
    87
    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)

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    A report is:
    Code:
    SELECT * FROM WebLog
    If you were more specific in what you were looking for, the answers might be clearer.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    You could have simply replied to this thread instead of starting another.

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

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Reopened...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    Originally posted by marist89
    A report is:
    Code:
    SELECT * FROM WebLog
    If you were more specific in what you were looking for, the answers might be clearer.
    That one cracked me up.

  6. #6
    Join Date
    Dec 2000
    Posts
    87
    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.

  7. #7
    Join Date
    Dec 2000
    Posts
    87
    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.






  8. #8
    Join Date
    Dec 2000
    Posts
    87
    Please read the above above for complete question:

    ....waiting....

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    How about a query plan?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Dec 2000
    Posts
    87
    What exactly do you mean by query plan...

    Can you be more specific?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width