Take this query..

select
  113,period,scenario_code,s.description + ' %',coalesce((s.value /
ts.value),0.0) * 100.0 as value,project_code
from rpt_generic_detail s
inner join rpt_generic_detail ts on ts.period = s.period and
ts.scenario_code = s.scenario_code
where s.scenario_code = -2 and s.rowid = 90 and ts.rowid = 44
JOINOPTIMIZECOSTS

The PLAN makes this comment at the end saying the join is partially
optimized

******************************
Costs ARE being taken into account when executing this join
Remove the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force
the optimizer to stop considering costs when optimizing this join

The expression:

s.period = ts.period and s.scenario_code = ts.scenario_code

is PARTIALLY-OPTIMIZED and is estimated to cost 18701 bytes per candidate
row
******************************

If I remove the JOINOPTIMIZECOSTS from the SQL it says it is

******************************
Costs ARE NOT being taken into account when executing this join
Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force
the
optimizer to consider costs when optimizing this join

The expression:

s.period = ts.period and s.scenario_code = ts.scenario_code

is OPTIMIZED

******************************

I dont understand why this is?.. I have indexes on all the join columns, but
when the JOINOPTIMIZECOSTS is selected it chooses not to use the index?. Is
this because it has determined the cost of doing the join using the index is
outweighed by the I/O cost of doing said join?.

Thanks
Clive

Clive,

<< I dont understand why this is?.. I have indexes on all the join columns,
but when the JOINOPTIMIZECOSTS is selected it chooses not to use the index?.
Is this because it has determined the cost of doing the join using the index
is outweighed by the I/O cost of doing said join?. >>

You are 100% correct.  It is opting to use an index for part of the join and
a row scan for the other part because it is more efficient than using
indexes for both.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks, I only just discovered this keyword and it has made some big
improvements to some SQL, but others has no effect, but one for example went
from 5 Seconds to 300MS, very happy with that.

I just wanted to make sure I understood what it was doing.

Cheers
Clive.

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:F29B49F3-AAC5-4DE5-AB0F-A2FF90C126D4@news.elevatesoft.com...
> Clive,
>
> << I dont understand why this is?.. I have indexes on all the join
> columns, but when the JOINOPTIMIZECOSTS is selected it chooses not to use
> the index?. Is this because it has determined the cost of doing the join
> using the index is outweighed by the I/O cost of doing said join?. >>
>
> You are 100% correct.  It is opting to use an index for part of the join
> and a row scan for the other part because it is more efficient than using
> indexes for both.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>
>

Clive,

<< Thanks, I only just discovered this keyword and it has made some big
improvements to some SQL, but others has no effect, but one for example went
from 5 Seconds to 300MS, very happy with that. >>

Yeah, it's kind of a mixed bag and you have to play around to see if it will
provide any improvements because it is particularly sensitive to the actual
contents of the table columns being joined.  It's effectively the cost-based
optimizations done with the WHERE clause applied to the JOIN clauses.

--
Tim Young
Elevate Software
www.elevatesoft.com