Skip to content

Cost Based Optimizer

12c SQL Plan Directive: state has changed between 12.1.0.1 and 12.1.0.2

By Franck Pachot . SQL plan Directives have been introduced in 12c. If you have scripts that check their states (and I have that as I prefer to monitor closely the features that are new) you probably have seen a difference when going from 12.1.0.1 – the first release of 12c and the only one avilable yet in Standard Edition… 

Oracle 12.1.0.2.1 Set to Join Conversion

By Franck Pachot . Recently, I described the Partial Join Evaluation transformation that appeared last year in Oracle 12c. I did it as an introduction for another transformation that appeared long time ago in 10.1.0.3 but was not used by default. And even in the latest Oracle 12c patchset 1 (aka 12.1.0.2.0) it is still not enabled. But it’s there… 

Partial Join Evaluation in Oracle 12c

By Franck Pachot . Do you think that it’s better to write semi-join SQL statements with IN(), EXISTS(), or to do a JOIN? Usually, the optimizer will evaluate the cost and do the transformation for you. And in this area, one more transformation has been introduced in 12c which is the Partial Join Evaluation (PJE). First, let’s have a look… 

ROWNUM vs ROW_NUMBER() and 12c fetch first

By Franck Pachot . Prior to Oracle 12c, there were two ways to do ‘top-n’ queries: use rownum after sorting rows with “order by” use row_number() over (order by) Top-n queries are usually required for result pagination. The application shows the first 10 rows, then the next 10 on the following page, etc. In these queries, user interactions are often…