View Problems

From: <amerar_at_my-deja.com>
Date: Fri, 22 Sep 2000 14:55:11 GMT
Message-ID: <8qfrs3$44$1@nnrp1.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: amerar_at_ci.chi.il.us

Thank you,

Arthur
amerar_at_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. Received on Fri Sep 22 2000 - 09:55:11 CDT