Re: Yes, this is probably a stupid question but...

On Apr 15, 1:09 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> Just another "probably stupid" question:
>
> Unfortunaltely, my testdatabase could not handle the test scripts (it's
> sized too small I think) but would reversing the columns in
>
> CREATE UNIQUE INDEX T1_IND1 ON T1(ACTIVE,SYS_ID);
> to
>
> CREATE UNIQUE INDEX T1_IND1 ON T1(SYS_ID,ACTIVE);
>
> do some good here? (Since active is not very discriminating and the join is
> on SYS_ID)? Would like to have tested this myself, but no success...
>
> Shakespeare

You'd think it might help a little bit, but I've got an index on just sys_id as well (for other reasons, not for this query), and the optimizer seems to not want to use it so he thinks at least that he gets enough discrimination out of active to make it worthwhile.

In case anyone is curious though, I did run down some more information here and it makes sense (to me at least).

It was kind of weird that the optimizer kept choosing nested loop joins even when a hash join was demonstrably much faster. Turns out one of my colleages (in an effort to work around an unrelated issue) had decided to set:

optimizer_index_caching=90
optimizer_index_cost_adj=10

With those settings in place, he ran nested loops consistently.

With the default
optimizer_index_caching=0
optimizer_index_cost_adj=100

He runs a hash join across this same data set.

Gonna run some more tests, but if that looks good I'm going to look at adjusting those parameters on production box. Issue they were implemented to work around was resolved another way in any event.

Thanks for all the helps,

• Pat