I’ve previously published a couple of notes (here and here) about the driving_site() hint. The first note pointed out that the hint was deliberately ignored if you write a local CTAS or INSERT that did a remote query. I’ve just found another case where the hint is ignored – this time in a simple SELECT statement.
Try running an ordinary distributed query from the SYS account, and then try using the driving_site() hint to make it run at the remote site. When I tried this a few days ago I ended up wasting half an hour translating some SQL from ANSI to Oracle dialect because I thought that the ANSI was making Oracle transform the query in a way that lost the hint – then I discovered that both versions of the code worked correctly if I logged in as a different user.
I was running my queries between two databases using 11.1.0.7 – I won’t guarantee you get the same results on other versions, but it looks like SYS doesn’t honour the driving_site() hint. I can’t think of a robust argument why this should be the case, but if I were forced to do some vague hand-waving I’d probably mumble something about potential security loopholes.
Footnote: I should, of course, have mentioned that there are all sorts of things that behave in unexpected ways if you are logged on as SYS, and that you shouldn’t be logged on as SYS – especially in a production system.
[Further reading on distributed databases]
[Further reading on "ignoring hints"]
Interesting…and funny that you have a strategy of converting ANSI SQL to Oracle dialect when you encounter a problem…that’s the first thing I do when someone hands me any ANSI SQL. Over the past two days, I’ve been given three pieces of SQL, all of which were getting a suboptimal plan, but when converted to Oracle dialect – no other changes and they were all reasonably simple SQL – the plan improved considerably and the query ran in minutes, rather than days.
Comment by Jeff Moss — May 20, 2010 @ 5:53 am UTC May 20,2010 |
Jeff,
I do have a strategy for translating ANSI to Oracle (since I know that ANSI sometimes is the problem) – but it’s an option I would only adopt if I were fairly sure that ANSI was the problem.
I actually made two mistakes in my analysis in this case – the first one was that I didn’t consider the fact that it might be the SYS account that was causing the problem … and I should have thought of that before trying the rewrite. The second was rewriting the query correctly instead of writing a simplified variant of the ANSI query to see if it still failed, rather than spending time doing an exact rewrite into Oracle SQL.
Rewriting queries is something I’m not keen on (even Oracle to Oracle), because it can be very hard to prove that the rewrite is logically equivalent to the original.
Comment by Jonathan Lewis — May 23, 2010 @ 1:42 pm UTC May 23,2010 |
[...] Bugs in Oracle Database cause the hint to be lost (see Jonathan Lewis’ comment in the above forum post, and the examples on his blog – for example) [...]
Pingback by Demonstration of Oracle “Ignoring” an Index Hint « Charles Hooper's Oracle Notes — July 26, 2010 @ 3:31 pm UTC Jul 26,2010 |