Urgent !! Finding New Cookies

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)

A report is:

Code:

---

SELECT * FROM WebLog

---

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

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

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

- Chris

Reopened...

Quote:

---

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.

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.

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.

Please read the above above for complete question:

....waiting....

How about a query plan?

What exactly do you mean by query plan...

Can you be more specific?

http://otn.oracle.com/docs/products/...13_exp.htm#838

I know what's explain plan here..but I think in my case..it doesn't help me too much...because I'm comparing result sets with more than 8 million records...

=================================================
Your Query is:

select distinct trunc(logdate),
cookieid from weblog w1
where not exists(
select 1 from weblog w2
where w1.cookieid=w2.cookieid
and trunc(logdate)<=trunc(logdate-1));
====================================================

What is the purpose of "trunc(logdate)<=trunc(logdate-1)" condition in the sub query?

I guess it's not quite the way to put it..

But the purpose is to compare the 'current' (whatever the date of the current record is) cookie with all the previous(whatever the date of the record is minus 1) cookies

Perhaps, something like

Code:

---

SELECT a.cookie_id,
      a.count(distinct cookie_id) total count,
      b.today_count
FROM
  weblog a,
  ( SELECT count(distinct cookie_id) today_count
    FROM weblog w2 ,
          (SELECT cookie_id, min(logdate) first_date
          FROM weblog
          GROUP BY cookie_id) iv
    WHERE w2.cookie_id = iv.cookie_id
    AND w2.logdate >= sysdate -1
    AND w2.logdate <= sysdate) b
group by a.cookie_id

---

I am not too sure if using 'Sysdate' is a right choice here...
Due to the fact that I'm going through each of the records in the table, and each cookie of arecord is compared to 'all' the previous cookies appeared. That means a cookie in a record dated '2002/8/10' will compare to all cookied prior to '2002/8/10' . Hope it's clear, thanks.

Quote:

---

Originally posted by marist89
Perhaps, something like

Code:

---

SELECT a.cookie_id,
      a.count(distinct cookie_id) total count,
      b.today_count
FROM
  weblog a,
  ( SELECT count(distinct cookie_id) today_count
    FROM weblog w2 ,
          (SELECT cookie_id, min(logdate) first_date
          FROM weblog
          GROUP BY cookie_id) iv
    WHERE w2.cookie_id = iv.cookie_id
    AND w2.logdate >= sysdate -1
    AND w2.logdate <= sysdate) b
group by a.cookie_id

---

---

Nope, we cant use dates as such. Date datatype contains the minute and seconds too. So, we should truncate the date column so that we can round it.

My suggestion,

Use function based index on logdate.

yes, time needs to be truncated here...as it was what I did in my original query. Only date is required.

I meant:

Code:

---

SELECT a.cookie_id,
      a.count(distinct cookie_id) total count,
      b.today_count
FROM
  weblog a,
  ( SELECT count(distinct cookie_id) today_count
    FROM weblog w2 ,
          (SELECT cookie_id, min(logdate) first_date
          FROM weblog
          GROUP BY cookie_id) iv
    WHERE w2.cookie_id = iv.cookie_id
    AND iv.first_date >= sysdate -1
    AND iv.first_date <= sysdate) b
group by a.cookie_id

---

Quote:

---

Originally posted by nagarjuna

Nope, we cant use dates as such. Date datatype contains the minute and seconds too. So, we should truncate the date column so that we can round it.

---

Come again?

Code:

---

SQL> select * from xyz;

D
-------------------
09/30/2002 13:29:06
10/30/2002 13:29:17
10/15/2002 13:29:27

SQL> select * from xyz
  2  where d >= to_date('10/30/2002','mm/dd/yyyy')
  3  and d < to_date('10/31/2002','mm/dd/yyyy')
  4  /

D
-------------------
10/30/2002 13:29:17

SQL> select * from xyz
  2  where trunc(d) = to_date('10/30/2002','mm/dd/yyyy');

D
-------------------
10/30/2002 13:29:17

---

Quote:

---

Originally posted by coolmandba
yes, time needs to be truncated here...as it was what I did in my original query. Only date is required.

---

It's an example. You mileage may vary....

Maybe something like:

Code:

---

SQL> desc xyz
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOGDATE                                            DATE
 SESSION_ID                                        NUMBER(10)
 COOKIE_ID                                          NUMBER(10)

SQL> set pause on
SQL> set linesize 30
SQL> set linesize 132
SQL> set pages 30
SQL> select * from xyz;


LOGDATE            SESSION_ID  COOKIE_ID
------------------- ---------- ----------
09/29/2002 14:29:28          1          1
09/28/2002 14:29:28          2          2
09/27/2002 14:29:28          3          3
09/26/2002 14:29:28          4          4
09/25/2002 14:29:28          5          5
09/24/2002 14:29:28          6          6
09/23/2002 14:29:28          7          7
09/22/2002 14:29:28          8          8
09/21/2002 14:29:28          9          9
09/20/2002 14:29:28        10        10
09/19/2002 14:29:28        11        11
09/18/2002 14:29:28        12        12
09/17/2002 14:29:28        13        13
09/16/2002 14:29:28        14        14
09/15/2002 14:29:28        15        15
09/14/2002 14:29:28        16        16
09/13/2002 14:29:28        17        17
09/12/2002 14:29:28        18        18
09/11/2002 14:29:28        19        19
09/10/2002 14:29:28        20        20
09/09/2002 14:29:28        21        21
09/08/2002 14:29:28        22        22
09/07/2002 14:29:28        23        23
09/06/2002 14:29:28        24        24
09/05/2002 14:29:28        25        25
09/04/2002 14:29:28        26        26
09/03/2002 14:29:28        27        27

LOGDATE            SESSION_ID  COOKIE_ID
------------------- ---------- ----------
09/02/2002 14:29:28        28        28
09/01/2002 14:29:28        29        29
08/31/2002 14:29:28        30        30
08/30/2002 14:29:28        31        31
08/29/2002 14:29:28        32        32
08/28/2002 14:29:28        33        33
08/27/2002 14:29:28        34        34
08/26/2002 14:29:28        35        35
08/25/2002 14:29:28        36        36
08/24/2002 14:29:28        37        37
08/23/2002 14:29:28        38        38
08/21/2002 14:29:28        40        40
08/20/2002 14:29:28        41        41
08/19/2002 14:29:28        42        42
08/18/2002 14:29:28        43        43
08/17/2002 14:29:28        44        44
08/16/2002 14:29:28        45        45
08/15/2002 14:29:28        46        46
08/14/2002 14:29:28        47        47
08/13/2002 14:29:28        48        48
08/12/2002 14:29:28        49        49
08/11/2002 14:29:28        50        50
08/10/2002 14:29:28        51        51
08/09/2002 14:29:28        52        52
08/08/2002 14:29:28        53        53
08/07/2002 14:29:28        54        54
08/06/2002 14:29:28        55        55


08/05/2002 14:29:28        56        56

56 rows selected.

SQL>  SELECT
  2      TRUNC(logdate) logdate,
  3      COUNT(DISTINCT cookie_id) total_cookies,
  4      NVL(b.new_cookies,0) new_cookies
  5  FROM xyz a,
  6        (SELECT
  7          TRUNC(iv.first_date) first_date,
  8          COUNT(*) new_cookies
  9        FROM
 10            (SELECT
 11              cookie_id,
 12              MIN(logdate) first_date
 13            FROM xyz
 14            GROUP BY cookie_id) iv
 15        GROUP BY TRUNC(iv.first_date)) b
 16  WHERE TRUNC(a.logdate) = b.first_date (+)
 17  GROUP BY TRUNC(a.logdate), NVL(b.new_cookies,0)
 18  /


LOGDATE            TOTAL_COOKIES NEW_COOKIES
------------------- ------------- -----------
08/02/2002 00:00:00            16          16
08/03/2002 00:00:00            16          16
08/04/2002 00:00:00            16          16
08/05/2002 00:00:00            16          16
08/06/2002 00:00:00            16          16
08/07/2002 00:00:00            16          16
08/08/2002 00:00:00            14          14
08/09/2002 00:00:00            16          5
08/10/2002 00:00:00            16          5
08/11/2002 00:00:00            16          5
08/12/2002 00:00:00            16          5
08/13/2002 00:00:00            16          5
08/14/2002 00:00:00            16          5
08/15/2002 00:00:00            14          5
08/16/2002 00:00:00            16          5
08/17/2002 00:00:00            16          5
08/18/2002 00:00:00            16          5
08/19/2002 00:00:00            16          5
08/20/2002 00:00:00            16          5
08/21/2002 00:00:00            17          6
08/22/2002 00:00:00            16          6
08/23/2002 00:00:00            17          6
08/24/2002 00:00:00            17          6
08/25/2002 00:00:00            17          6
08/26/2002 00:00:00            17          6
08/27/2002 00:00:00            17          6
08/28/2002 00:00:00            17          5

LOGDATE            TOTAL_COOKIES NEW_COOKIES
------------------- ------------- -----------
08/29/2002 00:00:00            17          5
08/30/2002 00:00:00            17          5
08/31/2002 00:00:00            17          5
09/01/2002 00:00:00            17          5
09/02/2002 00:00:00            17          5
09/03/2002 00:00:00            17          5
09/04/2002 00:00:00            17          5
09/05/2002 00:00:00            17          5
09/06/2002 00:00:00            17          5
09/07/2002 00:00:00            17          5
09/08/2002 00:00:00            17          5
09/09/2002 00:00:00            17          5
09/10/2002 00:00:00            17          4
09/11/2002 00:00:00            17          4
09/12/2002 00:00:00            17          4
09/13/2002 00:00:00            17          4
09/14/2002 00:00:00            17          4
09/15/2002 00:00:00            17          4
09/16/2002 00:00:00            13          0
09/17/2002 00:00:00            17          0
09/18/2002 00:00:00            17          0
09/19/2002 00:00:00            17          0
09/20/2002 00:00:00            17          0
09/21/2002 00:00:00            17          0
09/22/2002 00:00:00            17          0
09/23/2002 00:00:00            13          0
09/24/2002 00:00:00            17          0


LOGDATE            TOTAL_COOKIES NEW_COOKIES
------------------- ------------- -----------
09/25/2002 00:00:00            17          0
09/26/2002 00:00:00            17          0
09/27/2002 00:00:00            17          0
09/28/2002 00:00:00            17          0
09/29/2002 00:00:00            17          0
09/30/2002 00:00:00            16          6

60 rows selected.

---

My idea is to make the queries use index by creating function based index. an index on trunc(logdate)
Offcourse, we have to truncate the logdate. That's what I meant in my previous post. Mebbe I was very brief without any explanations. :)

trunc(logdate)<=trunc(logdate-1)

this condition will never be satisfied, logdate will always be bigger than logdate - 1 huh

if it's a typo and you mean

trunc(logdate)<=trunc(sysdate-1)

then instead of trunc the column you can try this:

logdate <= to_date(to_char(sysdate, 'yyyymmdd')||'235959', 'yyyymmddhh24miss') - 1