Top 60 Oracle Blogs

Recent comments

October 2012

Openworld day 3

Yesterday was another day of good quality sessions at OpenWorld.  Yes its true that there are many sessions that could only be described as pure marketing, but with a little effort, there are gems in the agenda !

It started with partitioning facilities that are coming in 12c.

Interval/Reference partitions will be supported, although no word on whether you can have subpartitions under interval based range partitions.  Time will tell.

One of the nice things was the CASCADE option coming for truncate/exchange partition.  In the past, when you had foreign keys between tables (as of course we always should :-) ), then truncate (being a DDL) would require a lot of manual FK maintenance before and after the truncate command.

This restriction has been lifted to a reasonable degree in 12c.

Partitioning 12c

Most useful presentation of OOW so far, from Hermann Baer of Oracle on improvements in partitioning features in 12c – and there are lots of useful ones, including:

Online move of a partition – so easy to compress a partition when it has reached its final “read-only” state

multiple partition maintenance in one operation – e.g. merge 3 monthly partitions into one quarterly partition, or split one partition into 24 (think about “how do I partition a non-partitioned table”, and 12c has just made it easier and quicker – exchange it into an empty partitioned table, then do one massive split).

partial indexing – define which partitions of a partitioned table should be included in the indexes you create on the table – and the optimizer also knows that different partitions need different plans (an enhancement of “table expansion”.

Openworld Day 2

Yesterday I went to a presentation on histograms in 12c.  A number of good improvements made there by Oracle coming in 12c.

Particular emphasis seems to be placed on some of the short comings of height balanced histograms.  In 12c, we will have TOP-N histograms (where the "top scorers" get their own frequency bucket, and the other values get collapsed together.  Similarly, there is a hybrid form of height balanced histogram.  Lots of playing around to be done there.

The limit on histograms has been bumped up to 2048, although you have to ask the question – if you have 2000 special values, then maybe, you don’t actually have *any* special values :-)

Oracle OpenWorld 2012 : Day 2

The cold continues…

I decided than rather than trying to sit in sessions and struggle to cope I would do my stint at RAC Attack and spend the day on the demo grounds…

The demo grounds are a really awesome way to get information because you are often speaking to the developers and product specialists working on code itself. In my opinion, you get a much better fix on what is going on that sitting in a presentation.

I got to speak to a bunch of people for a few hours about:

Oracle OpenWorld 2012 : Day 1

Day 1 turned out to be a little awkward for me because I was feeling pretty under the weather. I’ve been nursing a very minor cold for a couple of weeks and I think the lack of sleep has made it kick up a gear.

I went to a DB tuning session and an ADF deployment session, then I felt out for the count. At lunch time I went and watched a movie (review to come), then I went back to my room and vegetated for the rest of the day. It felt like a bit of a waste of a day, but I really wasn’t able to cope with anything more.

In the evening it was the ACE Dinner. I ate loads of food and talked until my throat was dead. It was fun, but I was glad to get back to my bed.



OOW 2012 – XQuery Update (HOL)

As promised, hereby the Hands-On Lab Oracle OpenWorld XQuery Update example statements. The following XQuery Update code will work from Oracle database and upwards and is fully supported from this version onwards. For more information see some of the blogposts here on this site or the OTN XMLDB forum for more examples. The code …

Continue reading »

OOW 2012 – And was there news on the XMLDB front…?

Just finished my Birds of a Feather XMLDB panel session in the Marriott Hotel and now looking back on an event full day. It all started of with a Keynote session with among others Mark Hurd. the most interesting bit, in my honest opinion, was the announcement of more details in Oracle 12c. I will …

Continue reading »

overlapping ranges with priority

A customer of ours (a leading Italian consumer goods retailer) has asked us to solve the following problem, that occurs quite frequently and that is not trivial to solve efficiently - and that is very interesting to design and fun to blog about!
the problem
Prices of skus (i.e. goods) have validity ranges (time intervals) and can overlap; on an overlapping range, the strongest priority (lower number) wins. In pictures:


the expected output is, since priority 0 is stronger then 1:

Row sizes 2

In an earlier post I showed you how you could generate SQL to analyze the distribution of row sizes in a table. In the introduction to the code I made a comment about how it failed to “allow for nulls at the end of rows”; a feature of Oracle storage that isn’t commonly known is that a set of consecutive null columns at the end of a row take up no storage space, while any null columns followed by a non-null column take up one byte per column (holding the value zero – the length of the null column) so that Oracle can “count its way” through the null columns to the non-null column. Consider this example:

create table null_demo(
	n0 number,
	n1 number,
	n2 number,
	n3 number,
	n4 number,
	n5 number,
	n6 number,
	n7 number,
	n8 number,
	n9 number

SQL> insert into null_demo(n0, n9) values (0,0);

1 row created.

SQL> insert into null_demo(n0, n1) values (0,0);

1 row created.

SQL> commit;