Hey, did you catch that redirect? The Toad World URL is now community.toadworld.com. Don't worry -- you'll still find all the same great content here (and more on the way). We're just in the process of giving our Toad World site some well-deserved love. Stay tuned for some more updates coming this way.
Meanwhile, enjoy community.toadworld.com.
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 Oracle184.108.40.206 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