RE: [Q] Unefficient SQL statement need help?

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Tue, 21 Nov 2000 18:34:42 -0500
Message-Id: <10687.122674@fatcity.com>

You don't seem to need to following two clauses:

where FACILITY.facility_master_id = FACILITY_MASTER.facility_master_id and FACILITY.facility_type_id = FACILITY_TYPE.facility_type_id

as you aren't using the contents of FACILITY_MASTER or of FACILITY_TYPE for anything. Removing those two tables from your query would certainly save some time.

Are the INTERNET_QUERY_FLAG columns candidates for bitmap indexes?

Just some suggestions...

Diana

-----Original Message-----
From: L [mailto:leed_at_chele.cais.net]
Sent: Tuesday, November 21, 2000 5:06 PM To: Multiple recipients of list ORACLE-L Subject: [Q] Unefficient SQL statement need help?

We have ORACLE 7.3.4 on SIN SPARC SOlaris 2.6 computer. I used "Tkprof" to trace a SQL statement. Can anyone hel me on following :

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

Received on Tue Nov 21 2000 - 17:34:42 CST