CBQT

Jonathan Lewis has recently posted a good example of CBO not good enough in transforming specific query types. A recent thread on the SQL.ru Oracle forum reminded me of this issue.

Basically OP has a query with disjuncted (OR-ed) predicate which started to fail after 11.2.0.3 upgrade with ORA-01790: expression must have same datatype as corresponding expression. Here is a test case (I’ve renamed column and table names cause I’ve used to such naming):

In our application we extensively use a function-based index on an important table. Couple of days ago I’ve seen an interesting issue associated with this FBI, view and a GROUP BY query. I have to say I don’t have an explanation what exactly it is and how I should call it properly, hence just “trouble” in the subject line.

Here is a nice example of what Oracle 11.2.0.2 is able to do with a subquery inside an expression. It can unnest it – that is a new 11.2.0.2 feature of the transformation part of the CBO.

As a follow-up to a recent post on different names Oracle can use for the intermediate views, here is a quick example of the technique called distinct placement.

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. Set up: drop table t2 cascade constraints purge; drop [...]

While googling I found a fresh article from Oracle for the VLDB journal: Enhanced Subquery Optimization in Oracle. It primarily discusses subqueries – how Oracle deals with them on optimization and at run-time. Paper’s topics overview: subquery coalescing – here I’ve done some testing of the feature partially available in 11gR2 (parallel) group-by pushdown – [...]