Help with complex SQL and self joins-too intensive!

[CanNeverThinkOfaName]

Hi

I've been banging my head against the wall for this one for way too long so I really am hoping someone else can see a solution for me!

To start with here is the base data...there are 4 scenarios I am trying to cover in my sql query and the rows below cover all 4

create table cdr
( starttime varchar2(50),
userid		varchar2(100), 
direction	varchar2(20),
localcallid	varchar2(20),
remotecallid varchar2(20),
relatedcallid	varchar2(20),
transfer_relatedcallid varchar2(20)
)
;

--scenario 1
insert into cdr values('02/02/2008 00:00:05','69900548187@x','Originating','4705114:0','4705115:0','','');	
insert into cdr values('02/02/2008 00:00:05','20151241@x','Terminating','4705115:0','4705114:0','','');
insert into cdr values('02/02/2008 00:00:05','20151241@x','Originating','4705115:0A','4705116:0','4705115:0','');
insert into cdr values('02/02/2008 00:00:05','69900255577@x','Terminating','4705116:0','4705115:0A','','4705116:0A');
insert into cdr values('02/02/2008 00:00:11','69900255577@x','Originating','4705116:0A','4705118:0','','4705116:0');
insert into cdr values('02/02/2008 00:00:11','140212125_VMR@x','Terminating','4705118:0','4705116:0A','','');
--scenario 2
insert into cdr values('02/02/2008 02:02:03','69900078096@x','Originating','4706880:0','4706881:0','','');
insert into cdr values('02/02/2008 02:02:03','69900046976@x','Terminating','4706881:0','4706880:0','','4706881:0A');	
insert into cdr values('02/02/2008 02:02:03','69900046976@x','Originating','4706881:0A','4706882:0','','4706881:0');	
insert into cdr values('02/02/2008 02:02:03','132526763_VMR@x','Terminating','4706882:0','4706881:0A','','');
--scenario 3
insert into cdr values('02/02/2008 00:39:07','202822780@x','Originating','4705790:0','4705791:0','','');		
insert into cdr values('02/02/2008 00:39:07','202821542@x','Terminating','4705791:0','4705790:0','','');
insert into cdr values('02/02/2008 00:39:07','202821542@x','Originating','4705791:0A','4705792:0','4705791:0','');	
insert into cdr values('02/02/2008 00:39:07','69900312616@x','Terminating','4705792:0','4705791:0A','','');
--scenario 4
insert into cdr values('02/02/2008 00:27:51','2086082@x','Originating','4705550:0','4705551:0','','');
insert into cdr values('02/02/2008 00:27:51','2086214@x','Terminating','4705551:0','4705550:0','','');

This is all to do with call (phone) records!
In scenario 1, caller 69900548187 (originator) rang caller 20151241 (terminatior)
but 20151241 had call forwarding set up so the call forwarded to 69900255577 (lines 3 and 4 of scenario 1)
69900255577 didnt answer so it went to voicemail (lines 5 and 6 of scenario 1)


in scenario 2, the caller rang someone who had call forwarding set on. (2 call parts - part 1 the call part 2 the forwarded call)

in scenario 3, the caller rang someone which went to voicemail.(2 call parts - part 1 the call part 2 the voicemail)

in scenario 4, the caller rang someone who answered (I wish they were all that way!)

SO my problem is I need to count how many calls where made- from these 16 lines, only 4 calls were actually made (there are 2 legs to each call and I dont want to count fowarding/voicemail as being a seperate call)

I need to get my sql code to return me a count of 4 so I need to cater for all these scenario's in my 1 sql statement.

what I have come up with is this but it just never finishes running as its too complex (takes 25 mins to return the count for just 2 days worth of data!!)

select cn.starttime,
cn.userid, cnb.userid,
cn.direction , cn.localcallid, cn.remotecallid, cn.relatedcallid, cn.transfer_relatedcallid,cnb.localcallid, 
cnb.remotecallid, cnb.relatedcallid, cnb.transfer_relatedcallid, cnb.transfer_relatedcallid
from cdr cn, cdr cnb
where cn.localcallid = cnb.remotecallid
and cn.direction = 'Terminating'
and  (
(cnb.relatedcallid is null
and cnb.TRANSFER_RELATEDCALLID is not null) -- scenario 1 and 2
or 
(cn.TRANSFER_RELATEDCALLID is  null 
and cnb.TRANSFER_RELATEDCALLID is  null
and cnb.relatedcallid is not null) -- scenario 3
or (
((select relatedcallid from cdr_normal cn1 where cn1.relatedcallid = cn.localcallid) is  null)
and (select transfer_relatedcallid from cdr_normal cn1 where cn1.transfer_relatedcallid = cn.localcallid) is  null) -- scenario 4
)
order by cn.localcallid

The last bit in the 'or' statement is too intensive but I can't see any other way to return scenario 4 without it (the most simplest one)

(((select relatedcallid from cdr_normal cn1 where cn1.relatedcallid = cn.localcallid) is  null)
and (select transfer_relatedcallid from cdr_normal cn1 where cn1.transfer_relatedcallid = cn.localcallid) is  null))

I'm using Oracle 10g.
Has anyone any hints or tips for me please?!!

Thanks in advance!

 

[SantaMufasa]

Maria,

Could you please run these two queries and post the results:

SELECT last_analyzed
  FROM user_tables
 WHERE table_name IN ('CDR','CDR_NORMAL');

col col_name format a32
SELECT table_name
      ,indexname
      ,column_position||':'||column_name col_name
  FROM user_ind_columns
 WHERE table_name in ('CDR','CDR_NORMAL')
 ORDER BY 1,2,3;

Thanks,


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CanNeverThinkOfaName]

Hi Dave

My fault- it is only one table i deal with (cdr_normal) but for the purpose of testing what I put in the post I made a new table cdr

I ran that code on cdr_normal for you in sql plus ( i use toad normally)

LAST_ANAL
---------
27-FEB-08


TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
COL_NAME
--------------------------------
CDR_NORMAL IDX$$_7C120001
1:NETWORKCALLID

CDR_NORMAL IDX$$_7C120002
1:ACCESSCALLID

CDR_NORMAL IDX$$_7C120004
1:REMOTECALLID


TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
COL_NAME
--------------------------------
CDR_NORMAL IDX$$_7D200001
1:RELATEDCALLID

CDR_NORMAL IDX$$_7D200002
1:TRANSFER_RELATEDCALLID

 

[taupirho]

This could be close or still a fair bit away. I don't know enough about your data to tell.

select starttime,userid,lead_d ,lead_l from
(
select starttime,userid,direction,localcallid l,remotecallid,
lead(remotecallid) over (order by localcallid) lead_l ,
lead(direction) over (order by localcallid) lead_d ,
relatedcallid, transfer_relatedcallid from cdr
order by localcallid
)
where relatedcallid is null
and transfer_relatedcallid is null
and l = lead_l



In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

...And after you have tried Taupirho's suggestion, try adding an index on CDR_NORMAL.LOCALCALLID. Let us know the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CanNeverThinkOfaName]

I dont use many analytic functions but I'm thinking I should start as those lag and lead functions might be the answer.

I've only given a simplified version of the acutal query I'm using so I need to work out what you did exactly and adjust my current (monstrous) query to see if it works better!
I tried to run it there and after a few mins got an error

ORA-01652: unable to extend temp segment by 128 in tablespace INUKSTATS_TMP ( my temp space)



Second thing is does that cater for outer joins where 1 row might not ever actually match another row (this happens where example I only get 1 leg of a call and the other leg is missing)
I need to also count these rows.

 

[SantaMufasa]

ORA-01652: unable to extend temp segment by 128 in tablespace INUKSTATS_TMP ( my temp space)

The space available to your temporary tablespace has run out. This means that your DBA must either allocate more disk space to that tablespace or do some other type of maintenance (which I won't go into unless you are the DBA and you don't know what "other maintenance" options you have).

...outer joins where 1 row might not ever actually match another row...I need to also count these rows.

I use Oracle's "(+)" left-outer-join operator to obtain "matches" between tables where no matches exist. Let us know whether you are familiar with, and have tried, this join method to achieve your results.

analytic functions...lag and lead functions might be the answer.

Oracle "Analytic Functions" are extremely powerful/useful. If you want some excellent references and examples of their uses, click on this excellent post by Shouvik Basu and click on Oracle's On-line Reference pages for Analytic syntax and Examples -- In this link, for the Analytic references, you will need to scroll down the page a ways.




Let us know how things progress for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CanNeverThinkOfaName]

Hello!

I've been working on making my query work with lag and lead and I've finally gotton it! (sorry had to finish work in a hurry on Friday so just getting stuck into it today!)

Now my next problem is the outer join I mentioned earlier!

I also need to bring back the rows where a localcallid will not match a remotecallid

I've tried l = lead_l(+) and l(+) = lead_l but i get the error 'ORA-01416: two tables cannot be outer-joined to each other'

So how can I get those entries where there is no corresponding remotecallid to the localcallid?

eg
insert into cdr values('02/02/2008 00:06:58','69900275528@x','Originating','4705245:0','','','');

alongside all the other inserts I've written above I need to get back 5 rows instead of the 4 I currently get...

PS the temp space problem was due to a misplaced bracket!!

How can I bring back the extra rows which don't correspond to other rows?!

Thanks for the help- much appreciated!

 

[taupirho]

Change

and l = lead_l

to

and l = lead_l or direction = lead_d



In order to understand recursion, you must first understand recursion.