It's an example. You mileage may vary....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.
Printable View
It's an example. You mileage may vary....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.
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