View Problems
ame...@my-deja.com
Hello,
We have a very rotten view in our database. It ties two tables together
giving results of matching and non-matching records. The problem is
that the queries are taking up to 15 minutes or more to complete.
I do not really ahve any ideas on how to optimize this query. Can
anyone help or give suggestions? The query is pasted below.
Please CC a copy to my e-mail: ame...@ci.chi.il.us
Thank you,
Arthur
ame...@ci.chi.il.us
CREATE VIEW CASH_MAN.PAYMENT_HISTORY_VIEW (
A_CODE,N_CODE,MR_FLAG,
SUB_SYSTEM_DESC,PAYMENT_TYPE,TRANDATETIME,
TRANSMISSIONDATE,BATCHNUM,TRANNUM,
SEQNUM,CAPS_CODE,DEPT,
AMT_PAID,CHECK_NUMBER,AMT_CASH,
AMT_CHECK,AMT_CREDIT,ADVICE_OF_CREDIT,
FOOD_STAMPS,INT,PEN,
TELLER,FUND_#,FUND_AMT,
LETTER_OF_CREDIT,ELECTRONIC_FUND_TRANSFER,LSV,
MISC1,MISC2,MISC3,
MISC4,MISC5,MISC6,
MISC7,MISC8,MISC9,
MISC10,MISC11,MISC12,
MISC13,MISC14,T_DATE,
PAYMENT_HISTORY_KEY,DATE_TIME_BATCH_CLOSED,EJ1_SEQNUM,
CREDIT_OTHERS,ADJ_AMT)
AS
select /*+FIRST_ROWS*/
a_code, n_code, mr_flag, sub_system_desc, payment_type,
trandatetime, transmissiondate, batchnum, trannum, seqnum,
ph.caps_code, dept, amt_paid, check_number,
NVL(amt_cash,0) + NVL(adj_amt_cash,0) amt_cash,
NVL(amt_check,0) + NVL(adj_amt_check,0) amt_check,
NVL(amt_credit,0) + NVL(adj_amt_credit,0) amt_credit,
NVL(advice_of_credit,0) + NVL(adj_advice_of_credit,0)
advice_of_credit,
NVL(food_stamps,0) + NVL(adj_food_stamps,0) food_stamps,
NVL(int,0) + NVL(adj_int,0) int,
NVL(pen,0) + NVL(adj_pen,0) pen,
teller, fund_#,
NVL(fund_amt,0) + NVL(adj_fund_amt,0) fund_amt,
NVL(letter_of_credit,0) + NVL(adj_letter_of_credit,0)
letter_of_credit,
NVL(electronic_fund_transfer,0) + NVL(adj_electronic_fund_xfer,0)
electronic_fund_transfer,
lsv, misc1, misc2, misc3, misc4, misc5, misc6, misc7,
misc8, misc9, misc10, misc11, misc12, misc13, misc14,
t_date, ph.payment_history_key, ph.date_time_batch_closed, ej1_seqnum,
NVL(credit_others,0) + NVL(adj_credit_others,0) credit_others, adj_amt
from payment_history ph, audit_trail_report at
where ph.payment_history_key = at.payment_history_key and
ph.caps_code = at.caps_code
UNION
select /*+FIRST_ROWS*/
a_code, n_code, mr_flag, sub_system_desc, payment_type,
trandatetime, transmissiondate, batchnum, trannum, seqnum,
at.caps_code, dept, amt_paid, check_number,
adj_amt_cash amt_cash,
adj_amt_check amt_check,
adj_amt_credit amt_credit,
adj_advice_of_credit advice_of_credit,
adj_food_stamps food_stamps,
adj_int int,
adj_pen pen,
teller, fund_#,
adj_fund_amt fund_amt,
adj_letter_of_credit letter_of_credit,
adj_electronic_fund_xfer electronic_fund_transfer,
lsv, misc1, misc2, misc3, misc4, misc5, misc6, misc7,
misc8, misc9, misc10, misc11, misc12, misc13, misc14,
t_date, at.payment_history_key, ph.date_time_batch_closed, ej1_seqnum,
adj_credit_others credit_others, adj_amt
from payment_history ph, audit_trail_report at
where ph.payment_history_key = at.payment_history_key and
ph.caps_code <> at.caps_code
UNION
select /*+FIRST_ROWS*/
a_code, n_code, mr_flag, sub_system_desc, payment_type,
trandatetime, transmissiondate, batchnum, trannum, seqnum,
caps_code, dept, amt_paid, check_number,
amt_cash,
amt_check,
amt_credit,
advice_of_credit,
food_stamps,
int,
pen ,
teller, fund_#,
fund_amt,
letter_of_credit,
electronic_fund_transfer,
lsv, misc1, misc2, misc3, misc4, misc5, misc6, misc7,
misc8, misc9, misc10, misc11, misc12, misc13, misc14,
t_date, payment_history_key, date_time_batch_closed, ej1_seqnum,
credit_others, adj_amt
from payment_history ph
where ph.payment_history_key not in
(select payment_history_key, caps_code, fund#,
sum(adj_amt_cash) adj_amt_cash,
sum(adj_amt_check) adj_amt_check,
sum(adj_amt_credit) adj_amt_credit,
sum(adj_advice_of_credit) adj_advice_of_credit,
sum(adj_int) adj_int,
sum(adj_pen) adj_pen,
sum(adj_fund_amt) adj_fund_amt,
sum(adj_letter_of_credit) adj_letter_of_credit,
sum(adj_food_stamps) adj_food_stamps,
sum(adj_electronic_fund_xfer) adj_electronic_fund_xfer,
sum(adj_credit_others) adj_credit_others
from audit_trail
group by payment_history_key, caps_code, fund#);
Sent via Deja.com http://www.deja.com/
Before you buy.
Boris Oblak
duplicates. There is no need for sort with UNION ALL.
<ame...@my-deja.com> wrote in message news:8qfrst$5u$1...@nnrp1.deja.com...