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

From: L <leed_at_chele.cais.net>
Date: Tue, 5 Dec 2000 14:29:40 -0500 (EST)
Message-Id: <10701.123712@fatcity.com>

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)

Received on Tue Dec 05 2000 - 13:29:40 CST