Ignoring Hints
A couple of things this morning.
First I should know better than to make blanket statements as I did yesterday.
Never update a row at a time in a loop should read something like Never update a row at a time in a loop if you can accomplish the same result by operating on the set of records. For example
begin
for c in (select col1,col2,col3 from tab1) loop
update tab2 set col1 = c.col1||c.col2||mod(col3,1000)
where col2 = c.col1;
end loop;
commit;
end;
Would be better written using set operations.
Second.
I have in the past listed ‘the optimizer can ignore hints’ as a myth. This is because the optimizer doesn’t ignore hints it follows them when considering the range of available access paths (this assumes that the hint is valid and correctly specified). This can be seen quite easily from a 10053 trace. The problem usually is either that the hint is invalid, or that insufficient hints have been specified. Never the less it turns out that with 10g the optimizer can ignore hints. This is because a new hidden parameter _optimizer_ignore_hints can be set to true – the default is false. In addition the 10053 trace has been enhanced so that adjusted parameter file parameters show up at the top and (what looks like) the whole range of default parameters show up in the trace.
Hi Niall
I have seen in 9.2.0.6 and 9.2.0.7 optimizer has ignored hint.
The hint that was specified was very stupid.The hint used was USE_HASH(Tablename) but the optimier estimated that zero rows would be returned and instead choose to use a NL Join.
regards
Hrishy
Anonymous
30 Nov 06 at 1:56 pm