Re: PL/SQL Help

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 Received on Mon Oct 18 2004 - 22:32:01 CDT