Why is CHOOSE so much slower than RULE ?

Hi folks,

        I have an odd situation with an 8.1.7 database. A query that takes only .51 seconds when the optimizer is set to RULE, takes a whopping 9.5 seconds when the optimizer is set to CHOOSE.

Here is the query:

SELECT d.topicid, COUNT(1) hits, NVL(SUM(((a.endtime - a.starttime))), 0) TotalTime

    FROM auvw_discussions_view a, discussions d     WHERE d.message_id = a.message_id

        AND a.id = 31316
    GROUP BY d.topicid

When optimizer goal is CHOOSE, ALL_ROWS or FIRST_ROWS: (9.54 sec.)

    SELECT STATEMENT Cost = 7471

      SORT GROUP BY
        HASH JOIN
          VIEW  AUVW_DISCUSSIONS_VIEW
            UNION-ALL
              TABLE ACCESS FULL AUDIT_DISCUSSIONS_VIEW
              TABLE ACCESS BY INDEX ROWID AUARC_DISCUSSIONS_VIEW
                INDEX RANGE SCAN IDX_AUARC_DISCUSSIONS_VIEW2
          TABLE ACCESS FULL DISCUSSIONS

When optimizer goal is RULE: (0.51 sec.)

    SELECT STATEMENT Cost =

      SORT GROUP BY
        NESTED LOOPS
          VIEW  AUVW_DISCUSSIONS_VIEW
            UNION-ALL
              TABLE ACCESS FULL AUDIT_DISCUSSIONS_VIEW
              TABLE ACCESS BY INDEX ROWID AUARC_DISCUSSIONS_VIEW
                INDEX RANGE SCAN IDX_AUARC_DISCUSSIONS_VIEW2
          TABLE ACCESS BY INDEX ROWID DISCUSSIONS
            INDEX UNIQUE SCAN PK_DISCUSSIONS

Any thoughts?

			Yours Truly,
			Jeffrey Boulier

-- 
Community Source & Support  


------=>Prometheus<=------