Re: Query Plan-Eliminating Full Table Scans

A copy of this was sent to dnobra_at_txpetrochem.com (if that email address didn't require changing) On Thu, 24 Jun 1999 14:38:21 GMT, you wrote:

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

try:

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

      not exists ( select null
                     from link_po_grn e
                    where e.po_number = a.po_number
                      and e.po_line_number = a.po_line_number )

not in has a tendency to full scan for each row in the outer query (you full scan link_po_grn for every row in the query -- not once per query).

Not exists will use indexes. Also, i rewrote the query to not use rtrim(a)||'-'||rtrim(b) -- that would preclude indexes (until Oracle8i, release 8.1 that is). In any event, you don't need to do that -- we'll just compare column to column.

So, if you have an index on A.REQUIRED_DATE, we should be able to use an index to access table A and then for each row found in table A, we'll use an index on link_po_grn(po_number, po_ine_number) -- you should have that index as well -- to see if a row exists in link_po_grn.

>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jun 24 1999 - 09:58:22 CDT