The column is there for a long time – even 9i documentation have it. I’ve never thought about it until today when I caought something extraordinary on 11.2.0.3 instance.
This is a quick note on the importance of ORDER BY for the order of the result set produced by a SELECT. The mantra is very simple: Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order. Repeat it as necessary many times everywhere, [...]
This is just a short note on the parameter introduced in the 11gR2 called _connect_by_use_union_all. I’ve noticed it for the first time in Doc ID 7210630.8, which gives a brief overview of the changes made to the way CBO generates plans for hierarchical queries. As usually happens, the change helps to one problem, but produces [...]
You most likely have seen this error before: ORA-01719: outer join operator (+) not allowed in operand of OR or IN Cause: An outer join appears in an or clause. Action: If A and B are predicates, to get the effect of (A(+) or B), try (select where (A(+) and not B)) union all (select [...]
This week I’ve seen an issue with a CONNECT BY query: for some reason Oracle 10.2.0.4 decided to build a weird plan (the query is weird too, but that’s not my point here ). An explanation of why that happened looks interesting, so here it is.
February 22, 2010 – 19:16
Starting with version 11gR1 Oracle database uses native FULL JOIN implementation based on HASH JOIN whenever possible. It’s good to know that this functionality first appeared in Oracle 10.2.0.3 and could be used to overcome different issues, including performance and bugs. Here is an example appeared on the SQL.ru recently:
February 15, 2010 – 23:38
From time to time looking at the tkprof’ed SQL traces I see this message: A some_predicate_here is the predicate from ON clause of a MERGE statement executed by the session. Up until recently I didn’t know why that happens, and since the error is not reported to the end-user there’s no big problem other than [...]
There was a thread recently on the OTN forum (it’s currently down, that’s why I’m writing in the blog; here is a stale version of the page from Google cache; at the time of writing this version had 1 comment) about reasons for control file sequential read accounted to particular query.
