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 Oracle18.104.22.168 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…
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 HotkaOracle ACE DirectorAuthor/Instructor/CEO