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

About the Author

Dan Hotka

Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.

Start the discussion at forums.toadworld.com