RE: [Q] SQL statement that Rule base run faster than cost base!!

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Wed, 6 Dec 2000 15:44:35 -0500
Message-Id: <10702.123845@fatcity.com>

You've posted this exact same query before -- and it's still the same. Have you tried any of the previous suggestions?

There is no need for FACILITY_MASTER or FACILITY_TYPE to be in this query. Is is possible to remove them? I'd be interested in the plan for the following query, which is logically equivalent to yours:

select distinct FACILITY_ATTRIBUTE.facility_attribute_id, FACILITY_ATTRIBUTE.title, FACILITY_ATTRIBUTE.internet_query_flag from FACILITY, FACILITY_FEATURE, FACILITY_ATTRIBUTE where 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

Have you considered bitmap indices on the internet_query_flags?

Diana

-----Original Message-----

From: L [mailto:leed_at_chele.cais.net]
Sent: Tuesday, December 05, 2000 2:38 PM To: Multiple recipients of list ORACLE-L Subject: [Q] SQL statement that Rule base run faster than cost base!!

I have foloowing SQL statement, it run a lot faster on Rule mode (18 seconds) than cost mode (10 min) under ORACLE 7.3.4. Does anyone know why?

I list output from TKPROF :

use "cost" base mode.

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.93 0.99 0 999 0 0
Execute 2 0.01 0.03 0 0 0 0
Fetch 12 592.96 597.79 672 9025930 6 176
------- ------ -------- ---------- ---------- ---------- ----------

total 15 593.90 598.81 672 9026929 6 176

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

Rows Execution Plan
------- ---------------------------------------------------

0 SELECT STATEMENT GOAL: CHOOSE 23081 SORT (UNIQUE)
23081 NESTED LOOPS

2256300     NESTED LOOPS
2256300      HASH JOIN
  12535       TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'FACILITY'
  17280       MERGE JOIN (CARTESIAN)
    180        TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                   'FACILITY_ATTRIBUTE'
     96        SORT (JOIN)
     97         INDEX   GOAL: ANALYZED (FULL 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)

B. use "rule" base mode

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.51 0.52 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 12 17.39 18.49 6232 185191 2 176
------- ------ -------- ---------- ---------- ---------- ----------

total 14 17.90 19.01 6232 185191 2 176

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

Rows Execution Plan
------- ---------------------------------------------------

      0 SELECT STATEMENT GOAL: RULE
  23081 SORT (UNIQUE)
  23081 NESTED LOOPS

  23081     NESTED LOOPS
  23081      NESTED LOOPS
  23081       NESTED LOOPS
    180        TABLE ACCESS   GOAL: ANALYZED (FULL) OF 
                   'FACILITY_ATTRIBUTE'
  23081        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 
                   'FACILITY_FEATURE'
  23261         INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                    'XIE1FACILITY_FEATURE' (NON-UNIQUE)
  23081       TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 
                  'FACILITY'
  23081        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'XPKFACILITY' 
                   (UNIQUE)
  23081      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                 'XPKFACILITY_MASTER' (UNIQUE)
  23081     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'XPKFACILITY_TYPE' 
                (UNIQUE)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: L
  INET: leed_at_chele.cais.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


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

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

Received on Wed Dec 06 2000 - 14:44:35 CST