Top 60 Oracle Blogs

Recent comments

October 2012

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;