Hi,

I’ll start this series of blogs on New Features for Developers.

The first is quick but good!

A while back, December of 2014 to be exact, I blogged about APPROX_COUNT and how much faster it was. This feature is in the Oracle12.1.0.2 release.

This APPROX_ type feature is a lot faster than the standalone version of the function. It is good when close is good enough but maybe you want statistics a lot faster for a dynamic web page perhaps.

Oracle12.2 (from OOW 16 Chris Saxton’s presentation on New Features for Developers) has introduced a number of other functions that you can now use this APPROX_ business on. Oracle Corp has not yet posted the 12.2 documentation; I’ll provide the full list once they do…

  • APPROX_MEDIAN
  • APPROX_PERCENTILE

There are a number of flavors of the percentile function. My guess is that they all have this APPROX_ feature too.

APPROX_PERCENTILE ( <expression> [ deterministic ],
     [ (‘ERROR_RATE’ | ‘CONFIDENCE’) ]
     Within group (order by <expression>)

Another item is you can turn this APPROX stuff on and not change your existing code!

Alter session set APPROX_FOR_COUNT_DISTINCT = true;

This feature will automatically change any count(distinct) syntax to the new APPROX_COUNT_DISTINCT syntax. Chris showed these options for the alter session too: APPROX_FOR_AGGRIGATION and APPROX_FOR_PERCENTILE.

Oracle VP Tirthankar Lahiri on the Oracle12.2 release date: “Oracle is presenting features for Oracle database 12c Release 2 on Oracle cloud. Features and enhancements related to the on-premises versions of Oracle Database 12c Release 2 are not being announced at this time”.

 

Dan Hotka
Oracle ACE Director
Author/Instructor/CEO