Re: PL/SQL Help

Googles wrote:

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

I'm not at all convinced it hung as much as I am convinced that you are impatient and are probably missing a lot of indexes.

Lets find out which.

SQL> desc plan_table

If Oracle can't find it have your DBA install it from utlxplan.sql at $ORACLE_HOME/rdbms/admin.

Then run this:

SQL> EXPLAIN PLAN

      SET statement_id = 'abc'
      FOR <your SQL statement here>;

Then, assuming 9i run this:

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

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