Re: Why is CHOOSE so much slower than RULE ?

Initial thoughts are the usual ones are the tables involved analyzed?.

It also looks to me as if rewriting the where clause as

WHERE d.message_id = a.message_id

         AND d.id = 31316

might give you a performance improvement (assuming discussions.id is the primary key).

(and of course rewriting using bind variables will result in a staable and presumably optimum plan for the whole class of queries

eg AND d.id = :discussionid )

--
Niall Litchfield
Oracle DBA
Audit Commission UK

"Jeffrey Boulier" <jeffreyb_at_gwu.edu> wrote in message
news:dW107.1847$K6.1479606_at_grover.nit.gwu.edu...


>

> 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<=------