Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> need help trying to make this an analytical
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
|* 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-lReceived on Thu Oct 27 2005 - 12:00:48 CDT