Top 60 Oracle Blogs

Recent comments

November 2013

Delete Archived Logs from Standby

This is a little surprising to me because it’s so simple – but I couldn’t find a script anywhere on oracle support or on the internet which elegantly (IMHO) cleaned up archived logs on a standby system.  (Specifically, a RAC/thread aware script.)

There are a few scripts published:

How to use HyperLogLog to incrementally maintain number of distinct values

In this post I'll show how extremely easy it is to maintain the number of distinct values when using HyperLogLog. Please reference to my previous post for some description how HyperLogLog works.

Let's assume we have a table with some existing data:

SQL> create table existing_data as
2 select round(dbms_random.value(0, 77777)) n
3 from dual
4 connect by level <= 100000;

Table created

Precise number of distinct values:

SQL> select count(distinct n) from existing_data;


Now in order for the incremental refresh to work we first need to create HyperLogLog synopsis:

SQL> create table existing_hll as
2 select mod(ora_hash(n), 1024) bucket,
3 max(num_zeroes(trunc(ora_hash(n)/1024)))+1 val
4 from existing_data

HyperLogLog in Oracle

Calculating number of unique values using Oracle distinct for big data sets has two major problems:

  • It may require lots of memory for sort/hash group by.
  • It is very difficult to refresh distinct numbers incrementally meaning every time you append some new data you generally have to perform distinct calculations from scratch.

Oracle introduced approximate NDV algorithm in 11G to solve the above problems but it's practical application is pretty much limited to stats gathering at the moment.

Places like Facebook and Google solve the above problems by using a very interesting algorithm called HyperLogLog.

What Delphix does in 1 minute 22 seconds




For a quick write up on what Delphix and database virtualizaition is , see

For a quick writeup on the use cases for Delphix and database virtualization, see


Screen Shot 2013-11-17 at 2.27.05 PM

Webinar Follow-up: Everyday Oracle Pro

Thanks to everyone who attended the November 12 webinar!

Presentation PDF
Webinar recording

Oak Table World UK 2013

In three weeks time, I'll be travelling to Manchester for Oak Table World UK

Actually, in three weeks time I'll be watching Roddy Frame
reprise Aztec Camera's debut album
- High Land, Hard Rain - but the next
morning I'll be catching the train to Manchester to present "Cloudy Reality".
It's a presentation about some of the things I've learned over the past 6
months, thinking about private clouds and Database as a Service

If the abstract seems a bit strange it's because I won't be
talking about anything to do with specific clients or projects but about the
things one might learn if one happened to be working on one of the larger
DBaaS projects out there.

Designing IT for Data Manufacture


photo by Jason Mrachina

As a (recovering) Mechanical Engineer, one of the things I’ve studied in the past is Design for Assembly (DFA). In a nutshell, the basic concepts of DFA are to reduce assembly time and cost by using fewer parts and process steps, making the parts and process steps you do use standard, automating, making it easy to grasp/insert/connect parts, removing time wasting process steps like having to orient the parts and so on.

Time for Bed, said Zebedee

Zebedee has just boinged onto my TV screen on his big-spring lower body and told us all that it is “time for bed”. I concur Zebedee. Good night everyone!


What Delphix does in 1 minute 22 seconds




For a quick write up on what Delphix and database virtualizaition is , see

For a quick writeup on the use cases for Delphix and database virtualization, see


Screen Shot 2013-11-17 at 2.27.05 PM

Database Virtualization video and slides

Had a great time at the East Coast Oracle (ECO) users group conference last week in Durham, NC.

Here are the slides

Here the a video  of my presentation on virtual databases as well as the slides.


For a quick write up on what Delphix and database virtualizaition is , see