Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: View Problems

Re: View Problems

From: Michael Bialik <michael_bialik_at_my-deja.com>
Date: Mon, 25 Sep 2000 20:56:16 GMT
Message-ID: <8qoe54$fa1$1@nnrp1.deja.com> 






Hi, Arthur.



 I think you have a number of problems in your view, but the greates
one resides inside your last UNION:



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#);



If I undersood correctly then you are trying to select rows from
PAYMENT_HISTORY table that does NOT


have a matching ( by PAYMENT_HISTORY_KEY ) row in AUDIT_TRAIL table.
In that case try using following statement:



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


  NOT EXISTS ( SELECT 1 FROM audit_trail at

                        WHERE at.payment_history_key =


ph.payment_history_key )



 Another possibility is to use OUTER join:



 ...


 FROM payment_history ph, audit_trail at
 WHERE at.payment_history_key(+) = ph.payment_history_key AND


              NVL(at.caps_code, -1) = -1;



 In both cases the idea is to eliminate unnecessary SORT of AUDIT_TRAIL
table, because you are not


 using any data from it.



 HTH. Michael





In article <8qfrs3$44$1_at_nnrp1.deja.com>,
  amerar_at_my-deja.com wrote:


>

>

> 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.

>





Sent via Deja.com http://www.deja.com/


Before you buy.
Received on Mon Sep 25 2000 - 15:56:16 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US