Monday, 9 October 2017

ODC Appreciation Day: IOT, Index Organized Tables

The ODC appreciation Day is a yearly tradition created by Tim Hall of Oracle-Base (link) to say "Thank You" to the Oracle Developer Community. This year he designated 10-Oct-2017, hence this post.

link to Tim: https://oracle-base.com/blog/2017/09/25/odc-appreciation-day-2017-thanksodc/

To join in on the ODC chorus, I'll present my favorite, very Simple, feature. 

The INDEX ORGANIZED TABLE. 

This feature is a favorite of mine, because 1) it is simple, you can explain it to almost anyone in under five minutes 2) there are a few additional benefits that kick in with the way Oracle has implemented this feature. 

Short Description:
In an Index-Organized Table (IOT) is a table of which the of records are (physically) stored in the order of their defined primary-key index, even if they are inserted at very different points in time. In a conventional (heap) table, the dependants of 1 parent may get scattered all over the heap, depending on the time of insert, whereas in an IOT the children of a parent will always end up in the same block. (or adjacent blocks if more are needed).

Use-Cases:
In my experience, IOTs can be beneficial in 3 specific cases :
1) parent-child tables where retrieval often includes the "fetch all children" case. 
2) link-tables that connect two sets in an n:m relation.
3) lookup tables.

I will not say you need to take these cases as "must-do" or "best practices", because there are cases where IOTs will fit nicely, but there are also cases where they will backfire on you. Go Read.

History and "other databases":
IOT has been around since Oracle 8 or 8.1 (the nineties!), but somehow has only been used by nerdy-geeky DBA's in specific cases. Competing Databases have "discovered" and used this feature more than Oracle. Hence explaining this feature to converted colleagues is sometimes very easy.

Further reading:
I would encourage you to explore the Manual and other sources of information, especially if you have large "hierarchical" sets of data with parent-child relations such as history-keeping or sets of object-attribute tables.
Richard -Mr Index- Foote has discussed IOTs in 2012:
And Martin Widlake in 2011:
And here is the link to my original blogpost about IOTs, mainly based on my work on a few project from around that time:

Voodoo Anecdote: 
Kamil and his team on the Voodoo product have recently found to their surprise(?), that direct-inserts do not work on IOTs. (link) Not surprising, if you realise that each record will have to be inserted in exactly the right place in the table: in between it's "siblings", to maintain the correct ordering of the records. Hence a Direct-Insert (inserting into a new block) would only be possible if the inserted data adhered to at least two conditions: 1) new records should have higher PK values than the highest existing record and 2) the newly inserted data would need to be presented in a (pre)ordered way to maintain the IOT property.
Careful study of the Really Terribly Fantastic Manual may reveal that a direct-insert on IOT is (was?) possible, but only when inserting pre-ordered data into an empy-table (link to docu, if I can still find it).

IoT versus IOT
Since the abbreviation IoT is now in use for Internet of Things, any mention of IOT (index organized table) seems to get lost in the noise. Hence this blog-post is also little reminder to the search engines to keep the legacy-item of IOT alive.

Tuesday, 27 June 2017

PG Day Russia, out of comfort zone.

In one week from now, I will be visiting the pgday.ru event in St Petersburg.

There is a Russian and an English page...


A big Thanks (Спасибо) goes to Timur Akhmadeev for bringing the event to my attention.

Curious as I am, I decided to try and send them some abstracts, and Low-and-Behold: I got selected to speak. Twice. Wow, Honored. My head was swelling with pride...

To some of my oracle Friends: I can recommend visiting non-Oracle events: You can stop bickering about the CBO or the opatch-inventory, and re-start living again. We can learn a lot, and we may even warn the PG community of some of our oracle-based mistakes.

It is Interesting and Fun just exploring the fellow-speakers and topics.
The PG community seems full of talent and experience. For example, they are also (re)discovering the benefits of "smart-DB" and "thick-DB" (link to "continuous delivery of stored-procedures").

So today I got my Passport back (visum approved), and I am all set to go. I really look forward to wandering around this event as a complete noob in a Foreign Environment.

#Curious  #любопытный


Tuesday, 28 March 2017

OUG Meetings Spring 2017

Planning is in the air.

This spring, if all continues to go well, I will visit six events in a nice, long spring "tour". And here is the Logo-and-Link-fest of the various events:


15-16 May, the Riga Dev Days (RDD):

Riga Dev Days.


24-26 May: Serbian Oracle User Group conference in Zlatibor:

Serbian Oracle User Group - Zlatibor Conference.




30 May: meetup of RoOUG




02-04 June: BgOUG spring conference :





15-16 June: the OGH tech experience:



Austrian Anwender Conference.



I cannot wait to get on the motorcycle again. On a map, it looks like this:

Hmm, if I stretch it, I could make an interactive Google-map with all the events + dates on it. But I'm too lazy; I'd rather go visit those places by motorcycle... 

The map link for the complete trip-plan is here, if you want to zoom in on the details: https://goo.gl/maps/1YvXPzCowMJ2 .

And if you live along that route and feel like grabbing a cup of Coffee while I check the Wifi Signal: Contact me!

Regrettably, I will have to skip two events due to time constraints: OUGF and ITOUG
I have politely cancelled going to Helsinki this year. I’m really sorry to miss the OUGF.fi Harmony event because it is always good quality content and nice social activities. I’ll try to be there next year folks. Kiitosh.

And, although I am very curious, I have to politely skip the (first?) Italian ITOUG tech day on 06 Giugno. I love to go to “new” places and start-up-usergroups, but this one would mean a race from Bulgaria to Milano. I hope to visit an Italian event soon though (it will mean going over the Alps and drinking Good Coffee, always a Pleasure!)

And by the way, I've done trips like this for 3 years in a row now. I know how to do this by now, and it is Great Fun. Check last year's tour on the interactive map-log at MyAdventure.bike...

See  you on an OUG somewhere ! 


Monday, 10 October 2016

OTN Appreciation Day : Function Result Cache

Feature: Function Result Cache.

Why : because this feature is Simple.
It is_almost_ no hassle to activate, and it had a Good Effect where we needed it.
Simple.

Why not: If you need this feature badly, you probably got something wrong in the design of the application. But hey: our app was an off-the-shelf COTS package, and the users took advantage of the software in ways that had never been envisaged 10 years ago...

Why not : because it didnt work in our case, despite all the RTFM we did. But one of my colleagues was persistent... and we found a workaround. Now it works like a charm.

Thx Peter Swier for persisting.

Thx Tim Hall for re-vitalizing my blog-postings.

More on FRC: Later.
Or come see me somewhere...
At SIOUG in Portoroz,
at HROUG in Rovinj,
at DOAG in Nürnberg,
or at UKOUG in Birmingham(UK).

Or... provide me with a desk to work + some coffee on one of my motorcycle tours  ...







Tuesday, 20 September 2016

Plans, plans, plans

As a conference-speaker, you have to announce your speaking-assignments on the internet...

Posting on the internet (Twitter, FB, blogs, forums even) generates links for the event-organizer,

Posting on the internet generates interest in your presentations...

But most of all, it allows me to "publish" my travel plans.

This particular post also serves to earn me a free (and hopefully safe) parking space somewhere... hehehe.

Plan for my October business trip is:
 - [tentative] POM: Paris Oracle Meetup
 - POUG.org: Polish Oracle User Group conference
 - SIOUG.si: Slovenian Oracle User Group
 - HROUG.hr : Croatian Oracle User Group

On a map, it looks like this..


Any one interested to come to my ppts ?

Saturday, 16 April 2016

The next ride.. 

This spring I am lucky to speak at :

OUGF.fi (18+19May)

SrOUG.rs (25-26May)

BgOUG.org (3-4June)

I hope to learn a lot again: I always get a lot of good info out of those conferences.

But another reason to do this is of course: to ride the motorcycle...

(why doesnt google maps provide a motorcycle icon.. they know enough about me to server up adverts...)

Tuesday, 6 October 2015

SIOUG and HROUG both at 20th Edition

Next week I will try to ride by (and speak at) SIOUG and HROUG conferences.
Both events are at their 20th Edition, so extra-celebrations.

SIOUG:


HROUG:




Also, many other Oracle friends seem to have discovered this corner of the world.


Travel plan is approximately.. (I will try to include some nice mountain roads.... )