Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: need help trying to make this an analytical

Re: need help trying to make this an analytical

From: <jo_holvoet_at_amis.com>
Date: Fri, 28 Oct 2005 09:51:53 +0200
Message-id: <OF65A48669.58BC0773-ONC12570A8.002A9CAC@eu.amis.com>

Michael,

I'm probably missing something, but wouldn't the following work :

select * from (
SELECT a.username, a.nasipaddress, a.acctsessionid, a.infostartdate, a.recdate,

       lag(a.recdate, 1) over (partition by a.username order by a.infostartdate) prevrec
)
where infostartdate < prevrec

mvg/regards

Jo

                                                                                                                                         
                      "Michael McMullen"                                                                                                 
                      <ganstadba_at_hotmail         To:      <oracle-l_at_freelists.org>                                                       
                      .com>                      cc:                                                                                     
                      Sent by:                   Subject: need help trying to make this an analytical                                    
                      oracle-l-bounce_at_fr                                                                                                 
                      eelists.org                                                                                                        
                                                                                                                                         
                                                                                                                                         
                      10/27/2005 18:58                                                                                                   
                      Please respond to                                                                                                  
                      ganstadba                                                                                                          
                                                                                                                                         
                                                                                                                                         




Tried on various platforms/os - 1st version 8.1.7.3 - latest 9.2.0.7

Problem: Users are assigned a username to log onto the system. When they log off a record is cut showing columns below. A combination of nasipaddress and acctsessionid is unique to the session but not to the table. Sometimes the server spits out dups. Users shouldn't be allowed to log onto the system from more than one computer but that has been subverted. So I'm trying to figure out how to find overlapping date values per user (infostartdate is when they log on, recdate is when they log off). Basically, for each username I have to look at all of their sessions and see if any of them overlap. We have > 4 million users with each days logs about 4 Gb. Though my test table is ~1Gb. I don't think I can do it one pass with an analytical but am hoping someone can figure it out. In 8i I cancelled after 14hrs. The hash join is taking forever. Trying it on 9i. I've tried numerous indexing strategies but really they don't make sense. Two full scans is required. I'm now trying it as a cursor, with my cursor looping through each distinct username, so I'm avoiding the self join. It's taken about 2 hrs to get 1/2 way.

My query is as follows.

SELECT DISTINCT a.username, a.nasipaddress, a.acctsessionid,

                   a.infostartdate, a.recdate
              FROM r_monitor.ar_unique a, r_monitor.ar_unique b
             WHERE a.username = b.username
               AND a.nasipaddress != b.nasipaddress
               AND a.acctsessionid != b.acctsessionid
               AND (    (a.infostartdate <= b.recdate)
                    AND (a.recdate >= b.infostartdate)
                   )


PLAN_TABLE_OUTPUT



| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |


|   0 | SELECT STATEMENT     |             |   291K|    43M|       | 82809
|
|   1 |  SORT UNIQUE         |             |   291K|    43M|    96M| 82809
|
|*  2 |   HASH JOIN          |             |   291K|    43M|  1083M| 75896
|
|   3 |    TABLE ACCESS FULL | AR_UNIQUE   |    12M|   940M|       | 13586
|
|   4 |    TABLE ACCESS FULL | AR_UNIQUE   |    12M|   940M|       | 13586
|

----------------------------------------------------------------------------

Predicate Information (identified by operation id):


   2 - access("A"."USERNAME"="B"."USERNAME")
       filter("A"."NASIPADDRESS"<>"B"."NASIPADDRESS" AND
              "A"."ACCTSESSIONID"<>"B"."ACCTSESSIONID" AND
              "A"."INFOSTARTDATE"<="B"."RECDATE" AND
"A"."RECDATE">="B"."INFOSTARTDATE") Note: cpu costing is off

20 rows selected.

describe ar_unique

 Name                                                  Null?    Type
 ----------------------------------------------------- --------
------------------------------------

 USERNAME
VARCHAR2(200)
 NASIPADDRESS                                          NOT NULL
VARCHAR2(20)
 ACCTSESSIONID                                         NOT NULL
VARCHAR2(200)
 INFOSTARTDATE                                                  DATE
 RECDATE                                                        DATE




--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 28 2005 - 02:54:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US