Query Help

[fmrock]

I have built the following query and takes very very long to run and was wondering if anyone had any suggestions to make it more effecient.

The Remarks table stores coments about each account, and I am trying to pull the last comment and date from that table.

Select 
	t1.Site,
	t1.Account, 
	t1.Log_Date, 
	t1.Balance, 
	t1.Last_Name, 
	t1.First_Name, 
	t1.FC, 
	t1.EX_Code, 
	t1.Hold, 
	t1.Initials, 
	to_date(t1.Followup_Date,'j') Followup_Date,
	to_date(R.REMDATE,'j') Last_Comment_Date,
	t1.max_Line_Num,
	R.REMTEXT Last_Comment
FROM
(
SELECT 
	A.ACCTCPCODE Site, 
	A.ACCTCODE Account, 
	to_date(A.ACCTLOGDATE,'j') Log_Date, 
	A.ACCTBALANCE Balance, 
	A.ACCTNAME Last_Name, 
	A.ACCTFIRST First_Name, 
	A.ACCTBILLSTATUS FC, 
	A.ACCTEXCEPTION EX_Code, 
	A.ACCTHOLDST Hold, 
	A.ACCTINIT Initials, 
	A.ACCTFOLLOWUP Followup_Date,
	MAX(R.REMLINE) max_Line_Num
FROM   
	MEDACCOUNT A
	JOIN MEDREMARKS R ON ((A.ACCTCPCODE=R.REMCPCODE) AND (A.ACCTCODE=R.REMACCTCODE)) 
WHERE  
	(A.ACCTHOLDST='Y') AND (A.ACCTMINPAY IS NULL) AND (A.ACCTFOLLOWUP IS NULL)
GROUP BY
	A.ACCTCPCODE, 
	A.ACCTCODE, 
	to_date(A.ACCTLOGDATE,'j'), 
	A.ACCTBALANCE, 
	A.ACCTNAME, 
	A.ACCTFIRST, 
	A.ACCTBILLSTATUS, 
	A.ACCTEXCEPTION, 
	A.ACCTHOLDST,
	A.ACCTINIT, 
	A.ACCTFOLLOWUP 
) t1
JOIN MEDREMARKS R ON (t1.Site=R.REMCPCODE) AND (t1.Account=R.REMACCTCODE) and (t1.max_Line_Num=R.REMLINE)

 

[taupirho]

You'll probably find that analytics is the way to go for this for this query.

Since you don't provide any table creation/insert statements here is a much simplified version to get you going.

SQL> desc acctab
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCNUM NUMBER

SQL> select * from acctab;

ACCNUM
----------
1
2
3
4


SQL> desc accrem;
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCNUM NUMBER
REMARK VARCHAR2(10)
REMLINE NUMBER



SQL> select * from accrem;

ACCNUM REMARK REMLINE
---------- ---------- ----------
1 comment1 1
1 comment2 2
1 comment3 3
1 comment4 4
1 comment5 5
2 comment6 1
2 comment7 2
2 comment8 3
3 comment9 1
3 comment10 2
4 comment11 1

ACCNUM REMARK REMLINE
---------- ---------- ----------
4 comment12 2
4 comment13 3
4 comment14 4
4 comment15 5
4 comment16 6

16 rows selected.

SQL>

Finally, the sql required to get the latest comment for each account number is:-


1 select accnum,remark,remline
2 from
3 (
4 select t1.accnum,t2.remark,t2.remline,
5 count(*) over (partition by t2.accnum) cnt
6 from acctab t1, accrem t2
7 where t1.accnum = t2.accnum
8 )
9* where remline = cnt
SQL> /

ACCNUM REMARK REMLINE
---------- ---------- ----------
1 comment5 5
2 comment8 3
3 comment10 2
4 comment16 6


Hopefully the above will give you some pointers




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

 

[fmrock]

Can i partition over to fields as that is what makes my accounts unique.

A.ACCTCPCODE Site, A.ACCTCODE Account

 

[taupirho]

Yes you can, mine was only a simplified example


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

 

[fmrock]

I tried the example, but it takes a very long time also.

The remarks table has 77,738,954 rows.