start_of_group

Those who visit SQL.ru often know what the title means. It’s a very simple yet powerful technique to group data which doesn’t seem appropriate for grouping at first sight.
I learned this very nice tip long time ago but often have to re-think before applying it to SQL or even search for the correct way of doing it when my brains give me denial of service. Recently I needed to do this type of query again and thought I should document the process, so I won’t forget it next time.

V$SQL.IS_OBSOLETE

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.

ORDER BY

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, because it’s true (Update: but see the first comment from Sokrates for an exception to the rule). I want to say it again because yesterday I’ve seen it in action. A query that has been working correctly for several years without an issue has started to randomly return rows without an expected order. The query looks like this:

select ...
  from (select ...
          from t
          where ...
         order by col1
       ) t1
     , t2
 where t1.id = t2.id;

i.e. it joins an inline view with another table on the primary key column. It worked well before 11g due to the fact that t2 was always joined using nested loops. However, after an upgrade the CBO has decided to use nested loops join batching which causes the order of rows to change when the data is on disk. That’s it – the query starts to return “unordered” result set sometimes, which in fact doesn’t have to be ordered. I also recommend to read Doc 378254.1 Order of the resultset changes when using a different version/patchset.

_connect_by_use_union_all

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 a bunch of unexpected side effects, such as wrong or incorrect results, and even ORA-00904 in a simple case. All these bugs have been fixed in the 11.2.0.2 patch set, but who knows how many issues related to the change are still there? If you see something unusual with a standard Oracle hierarchical query in the 11gR2, I think it’s good to try turning this parameter off and see if it helps.

ORA-01719 is partially relaxed

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 where (B)).

In 11gR2 it is no longer raised for predicates like column(+) IN (constant1, constant2, …).

CONNECT BY oddity

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.

Native full outer join

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:

Silent ORA-904 on MERGE

From time to time looking at the tkprof’ed SQL traces I see this message:

The following statement encountered a error during parse:

SELECT 1 FROM DUAL WHERE some_predicate_here

Error encountered: ORA-00904

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 very little annoyance.

Control file sequential read

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.