Re: [Q] Unefficient SQL statement need help?

From: yong huang <yong321_at_yahoo.com>
Date: Wed, 22 Nov 2000 20:56:12 -0800 (PST)
Message-Id: <10688.122786@fatcity.com>

Hi, L,

In addition to Diana's suggestion (remove two joins in the WHERE clause), I suggest you put FACILITY at the end of the FROM list, or add /*+ ORDERED */ hint to the query. Also check whether you have appropriate indexes on the tables FACILITY and FACILITY_ATTRIBUTE.

I'm not sure why Oracle is showing a cartesian merge join on row sources from FACILITY and FACILITY_ATTRIBUTE.

Yong Huang
yong321_at_yahoo.com

you wrote:

select distinct FACILITY_ATTRIBUTE.facility_attribute_id,   FACILITY_ATTRIBUTE.title, FACILITY_ATTRIBUTE.internet_query_flag from
 FACILITY, FACILITY_TYPE, FACILITY_MASTER, FACILITY_FEATURE,

  FACILITY_ATTRIBUTE where FACILITY.facility_master_id =
  FACILITY_MASTER.facility_master_id and FACILITY.facility_type_id =
  FACILITY_TYPE.facility_type_id and FACILITY.facility_id =
  FACILITY_FEATURE.facility_id and FACILITY_FEATURE.facility_attribute_id

=
  FACILITY_ATTRIBUTE.facility_attribute_id and FACILITY.internet_query_flag
<> 0 and FACILITY_ATTRIBUTE.internet_query_flag <> 0 order by
  FACILITY_ATTRIBUTE.title

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------

---

Parse 1 0.33 0.33 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 511.75 513.18 666 11294584 4 176
------- ------ -------- ---------- ---------- ---------- ----------

---

total 3 512.08 513.51 666 11294584 4 176

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 11 (CLASS)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE

  23081 SORT (UNIQUE)
  23081 NESTED LOOPS

2256300     NESTED LOOPS
2256300      NESTED LOOPS
2256300       MERGE JOIN (CARTESIAN)
  12535        TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                   'FACILITY'
    180        SORT (JOIN)
    180         TABLE ACCESS   GOAL: ANALYZED (FULL) OF
                    'FACILITY_ATTRIBUTE'
2256300       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                  'XPKFACILITY_TYPE' (UNIQUE)
2256300      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                 'XPKFACILITY_MASTER' (UNIQUE)
2256300     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF
                'XPKFACILITY_FEATURE' (UNIQUE)

__________________________________________________

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. Received on Wed Nov 22 2000 - 22:56:12 CST