Urgent !! Finding New Cookies

[marist89]

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

[coolmandba]

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...

[tamilselvan]

=================================================
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?

[coolmandba]

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

[marist89]

Perhaps, something like

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

[coolmandba]

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.

[nagarjuna]

Originally posted by marist89
Perhaps, something like

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.

[coolmandba]

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

[marist89]

I meant:

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

[marist89]

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?

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