Re: Optimizer puzzle: unnecessary full index scan

On 15 Apr 2002, hansmayer1962_at_hotmail.com wrote:
> Hello Oracle tuning experts,

I wish.

> The statement is this:
>
> insert into KSC_CALLER
> select *
> from caller_at_remote_schema b
> where not exists
> (select 1
> from local_schema.customers c
> where c.cust_id = b.caller);

What happens if you hint the CUST_IX2 on the production box? Hm... Since you probably can't do that, what happens if the test instance gets a hint so that it uses the CUST_DSTMP_IX2 index instead of the CUST_IX2? Do you see the dramatic increase in execution time?

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.