Skip navigation.

Tony Andrews

Syndicate content
Some very occasional musings on matters concerning Oracle specifically or databases in general.Tony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.comBlogger34125
Updated: 2 weeks 18 hours ago

Ignoring outliers in aggregate function

Wed, 2014-09-17 08:22
This is another aide-memoire for myself really.  I want to calculate the average load times per page for an application from timings stored in the database, and see which pages need attention. However, the stats can be skewed by the odd exceptional load that takes much longer than a typical load for reasons that are probably irrelevant to me. Here is a fictitious example: create table timings (Tony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2014/09/ignoring-outliers-in-aggregate-function.html

Why use CASE when NVL will do?

Thu, 2014-09-11 11:42
I've found that many developers are reluctant to use "new" features like CASE expressions and ANSI joins. (By new I mean: this millennium.) But now they have started to and they get carried away.  I have seen this several times recently:     CASE WHEN column1 IS NOT NULL THEN column1 ELSE column2 END Before they learned to use CASE I'm sure they would have written the much simpler:     NVL (Tony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com3http://tonyandrews.blogspot.com/2014/09/why-use-case-when-nvl-will-do.html

Handy pre-defined Oracle collections

Sat, 2014-08-30 05:09
Note to self: SYS.DBMS_DEBUG_VC2COLL is a handy pre-defined TABLE OF VARCHAR2(1000) SYS.KU$_VCNT is TABLE OF VARCHAR2(4000) Both are granted to public. Thanks to Eddie Awad's blog for these. Tony Andrewshttp://www.blogger.com/profile/16750945985361011515noreply@blogger.com0http://tonyandrews.blogspot.com/2014/08/handy-pre-defined-oracle-collections.html