Re: Query Plan-Eliminating Full Table Scans

You have 4 outer joins so you cannot avoid full table scans on those outer joined tables.

Also your subquery down the bottom doesn't have a where clause

M

dnobra_at_txpetrochem.com wrote in message <7ktfss$7gq$1_at_nnrp1.deja.com>...
>Hi all,
>
>I need some help eliminating the full table scans out of this query.
>It takes about 15 min to run to get back 450 records! Below is the
>code. I'm getting full table scans on the select and the sub-select.
>I'm brand new to Oracle, that's obviously a given, but any help is
>greatly appreciated! By the way, I was used to using temporary tables
>to manipulate and delete data, does Oracle have an easy to bulk copy
>data from table into another?
>
>Dan
>
>Query:
>
>SELECT
> A.COMPANY_CODE,
> A.DIVISION,
> A.WAREHOUSE,
> A.PO_NUMBER,
> A.PO_LINE_NUMBER,
> RTRIM(A.PO_NUMBER) || '-' || RTRIM(TO_CHAR(A.PO_LINE_NUMBER)) AS
>PO_LINE,
> B.REQUISITION_NUMBER,
> A.PART_CODE,
> C.PART_DESC_1,
> A.ORDER_QUANTITY,
> A.QTY_ACCEPTED,
> A.UNIT_PUR,
> A.PURCHASE_PRICE,
> A.REQUIRED_DATE,
> A.PROMISED_DATE,
> A.VENDOR_NUMBER,
> D.VENDOR_NAME,
> D.PHONE,
> D.VENDOR_CONTACT
>
>FROM
> POP_LINES A,
> POP_LINK_REQ_PO B,
> PRODUCT_MASTER C,
> VENDORS D
>
>WHERE
> (A.PO_NUMBER = B.PO_NUMBER (+) AND
> A.PO_LINE_NUMBER = B.PO_LINE_NUMBER (+) )AND
> A.PART_CODE = C.PART_CODE (+) AND
> A.VENDOR_NUMBER = D.VENDOR_NUMBER (+) AND
> A.REQUIRED_DATE > TO_DATE('05-01-1999', 'MM-DD-YYYY') AND
> RTRIM(A.PO_NUMBER) || '-' || RTRIM(TO_CHAR(A.PO_LINE_NUMBER)) NOT
>IN
> (SELECT RTRIM(E.PO_NUMBER) || '-' || RTRIM(TO_CHAR
>(E.PO_LINE_NUMBER))
> LINK_PO_GRN E)
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Thu Jun 24 1999 - 10:25:23 CDT