Explain plan

From: BHAVESH PATEL <BHAVESH-PATEL_at_worldnet.att.net>
Date: 5 Aug 1998 01:29:47 GMT
Message-ID: <6q8cib$f1s@bgtnsc03.worldnet.att.net>

SQL> explain plan
  2 set statement_id = 't1'
  3 for
  4 select a.id from customer b,sales_order a   5 where exists (select 'x' from sales_order_items c where c.id= a.id and
  6 c.prod_id not in (601,700))
  7 and
  8 a.cust_id = b.id and a.region = 'CHICAGO';

Explained.

SQL> SELECT OPERATION, OPTIONS, OBJECT_NAME, ID,

  2      PARENT_ID, POSITION
  3             FROM PLAN_TABLE
  4      WHERE STATEMENT_ID = 't1'
  5             ORDER BY ID;

OPERATION                      OPTIONS                        OBJECT_NAME


------------------------------ ------------------------------ --------------


----------------


       ID PARENT_ID  POSITION

--------- --------- ---------
SELECT STATEMENT
        0

FILTER
        1 0 1

NESTED LOOPS
        2 1 1

TABLE ACCESS                   FULL                           SALES_ORDER
        3         2         1

INDEX                          UNIQUE SCAN                    CUSTOMER_X
        4         2         2

TABLE ACCESS                   BY ROWID
SALES_ORDER_ITEMS
        5         1         2

INDEX                          RANGE SCAN

SALES_ORDER_ITEMS_X

OPERATION                      OPTIONS                        OBJECT_NAME


------------------------------ ------------------------------ --------------


----------------


       ID PARENT_ID  POSITION
--------- --------- ---------
        6         5         1

7 rows selected.

---

---

2. after analyzing the table so the optimer is COST-BASED.

LPAD('',2*LEVEL)||OPERATION

---

---

OPTIONS                        OBJECT_NAME


------------------------------ ------------------------------

  FILTER     NESTED LOOPS

      TABLE ACCESS
FULL                           SALES_ORDER

      INDEX
UNIQUE SCAN                    CUSTOMER_X

    TABLE ACCESS
BY ROWID                       SALES_ORDER_ITEMS

      INDEX
RANGE SCAN                     SALES_ORDER_ITEMS_X

6 rows selected.

---

---

3. with the cost

  1 select lpad(' ',2*level)||operation||' '||DECODE (id,0,'cost = '|| position) operation,
  2 options, object_name
  3 from plan_table
  4 connect by prior id = parent_id
  5 start with id = 0
  6* order by id
SQL> / OPERATION

---

---

OPTIONS                        OBJECT_NAME


------------------------------ ------------------------------

  SELECT STATEMENT cost = 3

    FILTER       NESTED LOOPS

        TABLE ACCESS
FULL                           SALES_ORDER

        INDEX
UNIQUE SCAN                    CUSTOMER_X

      TABLE ACCESS
BY ROWID                       SALES_ORDER_ITEMS

        INDEX

OPERATION

---

---

OPTIONS                        OBJECT_NAME


------------------------------ ------------------------------


RANGE SCAN                     SALES_ORDER_ITEMS_X

7 rows selected.

SQL> hi can anyone please be able to send me some idea of how to tune this query to avoid the full table scan on the sales orders table.

• Thanks and please do reply back at bpatel_at_hibbertgroup.com bhavesh-patel_at_worldnet.att.net

Received on Tue Aug 04 1998 - 20:29:47 CDT