Re: PL/SQL Help

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Mon, 18 Oct 2004 21:34:34 -0700
Message-ID: <1098160424.432279@yasure>

David Fitzjarrell wrote:

> google_at_earthlink.net (Googles) wrote in message news:<a92dd22c.0410181151.1a30a9e8_at_posting.google.com>...
>

>>Need help with this statement.  It hangs in SQL*Plus.  There is an
>>outer join between invoice_header table and ship_addr table.
>>SELECT invoice_detail.prod_id, invoice_detail.quantity,
>>product.description, ship_addr.country AS stcountry
>>  FROM invoice_header, invoice_detail, ship_addr, product
>>  WHERE invoice_header.inv_id IN ('2002780','2002781') AND
>>    invoice_header.cust_id = ship_addr.cust_id(+) AND 
>>    invoice_header.ship_id = ship_addr.ship_id(+) AND
>>    invoice_header.inv_id = invoice_detail.inv_id AND 
>>    invoice_detail.prod_id = product.prod_id 
>>Why did the above statement hang?
>>
>>fyi...  These 2 statements below work
>>1) When only one inv_id is in the condition
>>SELECT invoice_detail.prod_id, invoice_detail.quantity,
>>product.description, ship_addr.country AS stcountry
>>  FROM invoice_header, invoice_detail, ship_addr, product
>>  WHERE invoice_header.inv_id IN ('2002780') AND
>>    invoice_header.cust_id = ship_addr.cust_id(+) AND 
>>    invoice_header.ship_id = ship_addr.ship_id(+) AND
>>    invoice_header.inv_id = invoice_detail.inv_id AND 
>>    invoice_detail.prod_id = product.prod_id
>>
>>2) When the outer join is taken out
>>SELECT invoice_detail.prod_id, invoice_detail.quantity,
>>product.description
>>  FROM invoice_header, invoice_detail, product
>>  WHERE invoice_header.inv_id IN ('2002780','2002781') AND
>>    invoice_header.inv_id = invoice_detail.inv_id AND 
>>    invoice_detail.prod_id = product.prod_id 		
>>
>>Thank you.

>
>
> SQL> @?/rdbms/admin/utlxplan
>
> Table created.
>
> SQL> explain plan
> 2> set statement_id = 'thishangs' for
> 3> SELECT invoice_detail.prod_id, invoice_detail.quantity,
> 4> product.description, ship_addr.country AS stcountry
> 5> FROM invoice_header, invoice_detail, ship_addr, product
> 6> WHERE invoice_header.inv_id IN ('2002780','2002781') AND
> 7> invoice_header.cust_id = ship_addr.cust_id(+) AND
> 8> invoice_header.ship_id = ship_addr.ship_id(+) AND
> 9> invoice_header.inv_id = invoice_detail.inv_id AND
> 10> invoice_detail.prod_id = product.prod_id;
>
> Explained.
>
> SQL> set echo off termout off feedback off verify off
> SQL> set pagesize 0
> SQL> select decode(id,0,'',
> 2> lpad(' ', 2*(level -1))||level||'.'||position)||' '||
> 3> operation||' '||options||' '||object_name||' '||
> 4> object_type||' '||
> 5> decode(id,0,'Cost = '||cost) Query_plan
> 6> from plan_table
> 7> connect by prior id = parent_id
> 8> and statement_id = 'thishangs'
> 9> start with id = 0 and statement_id = 'thishangs'
> 10> /
>
> Report back when you have the query plan in hand. Then we can help you.
>
> David Fitzjarrell

Oracle now advises to never use scripts such as this one. Please go to http://www.psoug.org
click on Morgan's Library
click on DBMS_XPLAN

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Mon Oct 18 2004 - 23:34:34 CDT