Re: Simple (?) question about views

HI,

The view is a join, AND a GROUP BY. Both take longer than say a simple count on a single table.

In SQLPLUS:

SET TIMING ON Then try doing the SELECT statement that creates the view.

See if it is significantly faster. If not, then it works just as fast in the view, as not. You might have to create some indexes on the significant fields, especially the foreign keys.

Rodger

tgp_at_mci2000.com wrote:
>
> Okay, I'm stumped. I admit it. There MUST be a simple answer to this, but I
> cannot see it. Please post the simple answer, if you know it.
>
> I have this simple SQL statement:
>
> select sum(QTY_INVOICED)
> from PS_VCHR_LN_PO_VW
> where business_unit = 'JFFBT'
> and PO_ID = 'E-21880A' and LINE_NBR = 1
>
> (It was brought to my attention because it performs badly, if you want to
> know.) It selects from PS_VCHR_LN_PO_VW, which is a view. Yes, I checked to
> make sure there isn't a table by the same name. There appears to be precisely
> ONE object in this schema with this name.
>
> I've got this simple query that shows the TEXT field of DBA_VIEWS for a given
> VIEW_NAME. For this view, the TEXT field contains:
>
> select /*+ choose */ L.BUSINESS_UNIT_PO, L.PO_ID, L.LINE_NBR,
> L.SCHED_NBR, L.BUSINESS_UNIT_RECV, L.RECEIVER_ID, L.RECV_LN_NBR,
> L.RECV_SHIP_SEQ_NBR, L.B USINESS_UNIT, L.VOUCHER_ID, L.VOUCHER_LINE_NUM,
> L.QTY_VCHR, L.MERCHANDISE_AM T from PS_VOUCHER_LINE L, PS_VOUCHER V
> where V.BUSINESS_UNIT = L.B USINESS_UNIT and V.VOUCHER_ID = L.VOUCHER_ID
> and V.ENTRY_STATUS <> 'X' a nd L.BUSINESS_UNIT_PO <> ' ' and L.PO_ID <>
> ' ' group by L.BUSINESS_UNIT_PO , L.PO_ID, L.LINE_NBR, L.SCHED_NBR,
> L.BUSINESS_UNIT_RECV, L.RECEIVER_ID, L .RECV_LN_NBR, L.RECV_SHIP_SEQ_NBR,
> L.BUSINESS_UNIT, L.VOUCHER_ID, L.VOUCHER_ LINE_NUM, L.QTY_VCHR,
> L.MERCHANDISE_AMT
>
> I have not edited this text in any way. I simply copy/pasted it from a telnet
> session. Here's the part I don't understand: The column queried by the SQL
> statement above, QTY_INVOICED, does NOT appear in this text anywhere. Now, how
> in the heck can that be true?
>
> Now, from a completely different source, I have obtained what I strongly
> believe to be the source actually used to create the view, but I won't
> absolutely swear to it:
>
> select /*+ choose */
> L.BUSINESS_UNIT_PO,
> L.PO_ID,
> L.LINE_NBR,
> L.SCHED_NBR,
> L.BUSINESS_UNIT_RECV,
> L.RECEIVER_ID,
> L.RECV_LN_NBR,
> L.RECV_SHIP_SEQ_NBR,
> L.BUSINESS_UNIT,
> L.VOUCHER_ID,
> L.VOUCHER_LINE_NUM,
> L.QTY_VCHR,
> L.MERCHANDISE_AMT
> from
> PS_VOUCHER_LINE L,
> PS_VOUCHER V
> where
> V.BUSINESS_UNIT = L.BUSINESS_UNIT
> and V.VOUCHER_ID = L.VOUCHER_ID
> and V.ENTRY_STATUS <> 'X'
> and L.BUSINESS_UNIT_PO <> ' '
> and L.PO_ID <> ' '
> group by
> L.BUSINESS_UNIT_PO,
> L.PO_ID,
> L.LINE_NBR,
> L.SCHED_NBR,
> L.BUSINESS_UNIT_RECV,
> L.RECEIVER_ID,
> L.RECV_LN_NBR,
> L.RECV_SHIP_SEQ_NBR,
> L.BUSINESS_UNIT,
> L.VOUCHER_ID,
> L.VOUCHER_LINE_NUM,
> L.QTY_VCHR,
> L.MERCHANDISE_AMT
>
> Note that the two appear to be semi-identical, and the column in question,
> QTY_INVOICED, doesn't appear here either.
>
> HOWEVER, if I go into SQL*Plus, and enter "desc ps_vchr_ln_po_vw", it says:
>
> SQL> desc ps_vchr_ln_po_vw
> Name Null? Type
> ------------------------------- -------- ----
> BUSINESS_UNIT NOT NULL VARCHAR2(5)
> PO_ID NOT NULL VARCHAR2(10)
> LINE_NBR NOT NULL NUMBER(38)
> SCHED_NBR NOT NULL NUMBER(38)
> BUSINESS_UNIT_RECV NOT NULL VARCHAR2(5)
> RECEIVER_ID NOT NULL VARCHAR2(10)
> RECV_LN_NBR NOT NULL NUMBER(38)
> RECV_SHIP_SEQ_NBR NOT NULL NUMBER(38)
> BUSINESS_UNIT_AP NOT NULL VARCHAR2(5)
> VOUCHER_ID NOT NULL VARCHAR2(8)
> VOUCHER_LINE_NUM NOT NULL NUMBER(38)
> QTY_INVOICED NOT NULL NUMBER(15,4)
> AMT_INVOICED NOT NULL NUMBER(15,2)
>
> And QTY_INVOICED is there, just as plain as day.
>
> Now, I thought I understood Oracle fairly well, but I'm stumped. Please help
> me see the error of my ways.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Sun Aug 23 1998 - 20:45:13 CDT