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

Home -> Community -> Usenet -> c.d.o.misc -> Query Plan-Eliminating Full Table Scans

Query Plan-Eliminating Full Table Scans

From: <dnobra_at_txpetrochem.com>
Date: Thu, 24 Jun 1999 14:38:21 GMT
Message-ID: <7ktfss$7gq$1@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 - 09:38:21 CDT

Original text of this message

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