Re: SQL optimization

> Hello,
>
> It is not so easy to answer such a question ;)
> Especially when one doesn't know the whole operating context of your DB ;)
>
> Seems to be DSS-DW stuff, doesn't it ?

Yes, you win
>
> Your full accessed table contains lots of rows ?

E_MRP_RESULT have 27495 rows

> If I read well, 2350 is not so many rows especially in Data warehouses.
> If not, it can be an element of the explanation, CBO thinks it would be
more
> expensive to access the table by any index (this is a commonly observed
> situation)
>
> How long does your query take to complete ?

CBO= more than one hour
RBO= few minutes

> If users and system engineers are OK with throughput time and resource
> consumption, no matter to further optimizing that code and using hints to
> force RBO evaluation is not so bad (but not elegant let's say ;)
> If not, try to use M-Views and enable query rewrite (careful, it requires
> some careful planning and testing to work fine) and try to stabilize your
> RBO plan using strored outlines.
>
> Prior to settle that field artillery, please, TKPROF well your query, i.e.
> identifying which steps of the execution plan are returning or selecting
> most of the rows, both for RBO and CBO.
>
> Sometimes, when I have to optimize a query with CBO, I simply reorder
> predicates of the WHERE clause and the query works better by getting a
> better evaluated explan.
>

Thanks for your answer,
I will use tkprof to reorder the where clause if this doesn't work I 'll put a hint to use RBO :-( Do you think is the best I can do ? Received on Mon Oct 06 2003 - 14:51:40 CDT