The automatic upgrade was as smooth as ever. A couple of themes needed to be upgraded too. The menus are a little different and there is now a new persistent dashboard header, but it all seems like business as usual for the casual blogger like me.
There were a couple of new and related features at this years conference - the Unconference and OAK Talks.
The idea of an Unconference will be familiar to those who have attended Openworld - an unscheduled part of the conference that anyone can sign up for to present during a one hour slot. First come, first served. I was a little concerned about how this might take off because it needs a lot of advertising or to be in a prominent location, otherwise it just passes people by. Even at OOW, where they do a reasonable job of pushing it, it's not uncommon for sessions to be attended by a handful of people, although that in itself can lead to an interesting experience as I had several major OEM luminaries in my OEM session one year and it turned into a discussion of the finer points of the Performance Pages and potential improvements!
I feel the UKOUG could have done better here and I suspect they know that. The Unconference area was far from clearly marked and stuck out of the way on the balcony of the exhibition hall and my heart sank when I checked out how many people had signed up to present Really, people should take this kind of opportunity to speak when perhaps their presentation abstracts weren't accepted or they want to talk on a subject that would have no chance of making it on to the main agenda. They could even try and be funny or entertaining! Then again, if you don't think you'll have attendees, you're hardly likely to sign up and so it becomes a vicious circle. I hope the UKOUG don't give up on it, though, and try to improve the promotion of it next year because I like even more opportunities for new speakers and more off-beat presentations where possible. I know that Greg Rahn had a 2-hour session there that people raved about and I was sorry I missed it.
It was at least partly to promote the conference that a few of us came up with the idea of getting Oak Table Network members to give some presentations and James Morle suggested OAK Talks. 5 x 10 minute presentations which would hopefully be short, entertaining and make an interesting point or two. By holding them in the Unconference area over lunchtime, we hoped to promote it. I discussed the background to this more here.
I'd agreed to be one of the speakers in the first lunchtime slot and when we all finally worked out where the Unconference area was, my heart sank again. It looked like the OAK Talks might become too literal as a bunch of Oakies talked to other Oakies. Might as well have been in the pub, in that case! But, true to form, James took the thing by the scruff of the neck and an announcement was made in the Exhibition Hall so that the occasional person wondered in, if only for a place to sit down and eat their lunch
The main problem on the first day was that the noise-levels from the Exhibition Hall made it very difficult to hear people and the level of concentration killed the atmosphere a bit but, as new speakers took their turns, we started to realise that the only option was to shout a bit which wasn't too difficult in my case. Yet again, problems were identified and resolved and a small PA appeared for the next two days which made things better. It was still a pretty noisy location, though!
With 15 different talks of such varying messages and styles, I won't bore you with them all, but just pick out a few personal memories. Apologies to those who didn't make it - it's because you weren't very good. << Scottish humour.
- Tuomas Pystynen kicked off proceedings talking about RMAN and levels of backup compression and performance. Tuomas deserves a medal for being the first speaker because, as well as the noise and gradually growing audience, others would later discover that doing one of these talks was petrifying compared to giving a standard 45-60 minute presentation with the crutch of presentation slides and space to veer off track. Between the intimacy of the audience, the tight timescale and the lack of easy props, several of us discussed what a challenge it had been! Of course, it probably doesn't help that I suspect a lot of these talks had been developed entirely in a few minutes in a bar or airport somewhere Well done, Tuomas, for having the guts to get the show on the road!
- Me (of course) talking about You Probably Don't Need Exadata. This was inspired by Moans Longballs Nogood's much earlier paper You Probably Don't Need RAC and, whilst I planned it to be much more withering and sarcastic than I think I was (because I forgot most of my mini-speech as soon as I started), I was essentially making the point that Exadata is not the only solution out there and that the right solution for you depends on your workload and, erm, maybe some optimisation of your application might be a better first step? Just a thought. A personal highlight was Greg Rahn walking in 2/3 of the way through and the only available seat being right under my nose as I ranted and raved about Exadata, but it all added to the fun By this time, the area was pretty full and, when it was, it was actually a cool and unusual atmosphere to present in.
Here's a tiny version of the wide-screen shot, courtesy of Neil Chandler.
- The only thing missing from Neil's picture of Jonathan Lewis presenting on how to build test data rapidly for experiments is that he missed the moment when, after some discussion about whether Jonathan had made a mistake or not, Jonathan solved the problem by screwing up the bit of paper and deciding to eat it! A perfect informal presentation moment and exactly what the Oak Table Network is about. Are you sure you've written that properly? Cracking talk, too.
- Alex Gorbachev talking about the Oracle Database Appliance. I and others have questioned the value of the ODA and whether it hasn't perhaps been over-played a little by partner consulting companies? Alex, never one to avoid a challenge, decided he would pick this moment to say why he thought it was a worthwhile proposition. It keeps coming back to ease of deployment and he contrasted it with the somewhat more tricky work that had been going on at RAC Attack. At the end of the presentation he looked over and wondered whether he might have persuaded certain grumpy old people and got a begrudging and limited nod of approval! LOL
- Ever since I sat in a presentation audience with John Beresniewicz (JB) several years ago and he had murmured - "that's because DB Time is fungible", I'd become mildly obsessed with this property of DB Time and was hoping to hear him talk about it at UKOUG last year, but his abstract didn't make the cut. So an OAK Talk was the perfect opportunity for him to at least let a few more people know! As he'd thought about the subject longer, though, it turned out that maybe DB Time is more liquid than fungible but the practicalities are that maybe DB Time is a good metric to use when implementing charge-back for system resource usage because most metrics don't give a great idea of how much a system is actually being used by specific business exercises? He also higlighted how it can be sliced and diced across different application components, users, SQL statements and all the other nice dimensions that ASH gives you so it's easy to apportion time-based usage to the proper charging pot!
Updated Later: Oh, I almost forgot to mention the hilarious moment when JB, picking on a couple of people he knew in the room to make a simple point about costs, seemed to imply that my consulting rate might be higher than Cary Millsap's. In ... my ... dreams ...
There were lots of other talks I could mention and this became an immutable slot on my agenda. The opportunity to sit and chill out and not be bored senseless. It could have used a better venue, it definitely needed the PA and, as with anything so informal, it was at times sprawling and variable, but I wouldn't have missed it for the world, although most of us who presented wouldn't have minded missing out on the stress levels leading up to our individual moment!
I was discussing this later in the conference with a few of my many friends who are not Oak Table Network members. (Yes, it's true, I really have some.) They would have been keen to participate too and so the quesiton arises, should someone try to organise more slots in a similar vein next year? I'm interested in hearing further comments on that ....
Oh, and well done to James for herdiing cats and doing the most to pull the whole thing together.
Usual disclosure: My travel and accommodation expenses were covered
by the Oracle ACE Director
This blog post covers day 0 of UKOUG 2011 — Sunday, 4th of December, 2011. Since there were so many of us from Pythian at the conference, I’m adding my name in the blog post title. I think I will be doing it for all conference posts as I think I’ve been doing for some [...]
I have been lucky enough to do some work with Fusion IO cards in a blade server, soon to be followed by another set of tests on a full rack mounted server. I didn’t know exactly where model I was given, but powered my server down in eager anticipation of the events to come.
After the engineer plugged the card in, and powered the server up I logged in as root to find out what about the pre-christmas present. I knew it was a PCI card, so surely lspci would tell me more. Here’s the output:
lspci -vvvv 41:00.0 Mass storage controller: Fusion-io ioDimm3 (v1.2) (rev 01) Subsystem: Hewlett-Packard Company Unknown device 324d Control: I/O+ Mem+ BusMaster+ SpecCycle- MemWINV- VGASnoop- ParErr+ Stepping- SERR- FastB2B- Status: Cap+ 66MHz- UDF- FastB2B- ParErr- DEVSEL=fast >TAbort-
So it’s indeed a FusionIO card (an ioDrive to be precise), and it’s plugged into a x4 slot, the mimimum required.
What to do next? Always good to read the docs. The fusionio.com website allows you to download documentation and drivers after a free registration. Luckily the website didn’t have kernel modules for Oracle Linux I’m using (RHEL 5 only), which gives me the opportunity to build the software from source. I don’t like surprises, therefore I created my ~/.rpmmacros with the following content:
[root@computer1 rpm]# cat ~/.rpmmacros %_topdir /home/martin/rpm %_tmppath /home/martin/rpm/tmp
This obviously requires the full tree underneath the topdir, namely
With these directories in place it’s as simple as running rpmbuild –rebuild iomemory-vsl-184.108.40.206-1.0.src.rpm as a non-root user (martin in my case) and wait for the RPMs to be created in the RPMs/x86-64 directory. Following the documentation again I installed the needed software:
[root@computer1 x86_64]# rpm -ihv iomemory-vsl-2.6.18-220.127.116.11.1.el5-18.104.22.168-1.0.x86_64.rpm Preparing... ########################################### [100%] 1:iomemory-vsl-2.6.18-194########################################### [100%] [root@computer1 fusionio]# rpm -Uvh lib*.rpm Preparing... ########################################### [100%] 1:libfio ########################################### [ 50%] 2:libfusionjni ########################################### [100%] [root@computer1 fusionio]# rpm -Uvh fio*.rpm Preparing... ########################################### [100%] 1:fio-common ########################################### [ 14%] 2:fio-util ########################################### [ 29%] 3:fio-remote-util ########################################### [ 43%] 4:fio-smis ########################################### [ 57%] 5:fio-snmp-agentx ########################################### [ 71%] 6:fio-snmp-mib ########################################### [ 86%] 7:fio-sysvinit ########################################### [100%] [root@computer1 fusionio]#
Note that you don’t actually need the fio-sysvinit package if your distribution is reasonably modern. UDEV will load any drivers automatically.
With that completed as well it’s time to load the kernel module and watch Linux do the rest. The fio-status tool queries the card’s helth:
[root@computer1 x86_64]# fio-status Found 1 ioDrive in this system Fusion-io driver version: 2.3.1 build 123 fct0 Attached as 'fioa' (block device) HP StorageWorks 320GB IO Accelerator, Product Number:AJ878A SN:07902 Alt PN:507152-001 PCI:41:00.0 Firmware v5.0.5, rev 43674 322.55 GBytes block device size, 396 GBytes physical device size Sufficient power available: Unknown Internal temperature: 40.4 degC, max 40.9 degC Media status: Healthy; Reserves: 100.00%, warn at 10.00%
That’s it! so simple-another blog post will detail how I ran a first orion benchmark on it and show some impressive numbers.
I posted a rant about my hosting provider about 10 days ago. As I mentioned in the post, I quickly got the site up and running again. Pretty soon after that I was at the UKOUG conference and Cary Millsap‘s training course, so I never really got time to look at things again…
On Thursday evening I took a proper look at the site and OMG, what a mess. Here are some of the issues I’ve been firefighting since Thursday…
The last issue was the big one. Many moons ago I wrote my own custom CMS using PHP and mySQL to manage the site. Most of the site is flat files, but the content of the database is the single point of truth for me. Once I make a change, the flat page(s) are regenerated to reflect the change. The problem was my CMS was screwed. It’s fixed now, but a significant number of pages in the database contain some logical corruption. This doesn’t affect what people see on the site, it just means the database is no longer the single point of truth.
I’ve written some tools to help me fix the data in the database, but I’m not willing to let them roam free. I’m using them to work through the pages one at a time. It’s very labor intensive, but it’s the safest option.
Two bonuses of this whole fiasco are:
I was speaking to someone in Bulgaria about the site and mentioned that every year it gets bigger and the time I spend maintaining it, rather than writing new content, increases. This just goes to prove it.
So it looks like the next few days are going to be very busy and very boring.
The UKOUG Technology conference in Birmingham was yet another great showcase of Oracle knowledge sharing and networking. The weather was surprisingly good for that time of year in Birmingham, though, the conference kept me so busy that I rarely had a chance to walk the streets. I had the opportunity to hear a presentation by and then speak with Bryn Llewellyn from Oracle; I’ve subsequently improved (I hope) my paper on Edition Based Redefinition; I’ll be presenting a new and improved version at Collaborate in Las Vegas this spring. Another highlight was watching a team of Oracle Middleware experts from Holland work to create a replacement conference registration system in a four-hour session using Oracle SOA, BPEL, ADF, and other middleware tools. What a demonstration of flexibility and capability!
Last Thuesday, Mark Drake, Senior Product Manager and I, delivered a good presentation during UKOUG in Birmingham about how to use your database, via XMLDB functionality, as a file server. The presentation demonstrated as well how you could extent the “standaard” file server (aka your database) functionality with features like, WebDAV driven ACL Security and
Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following:
With every new release, the query optimizer is enhanced. Oracle Database 11g Release 1 and Release 2 are no exception to the rule. Specifically, they introduce key improvements in the following areas: indexing, optimization techniques, object statistics and plan stability. The aim of this presentation is to review the new features from a practical point of view as well as to point out challenges related to them. In other words, to let you know what you can expect from the query optimizer when you upgrade to Oracle Database 11g.
The aim of this short post is to point out that I made available the current version of the slides and all the scripts that go with them here.
The structure of the presentation (incl. a reference to the available scripts) is the following:
This year's UKOUG 2011 conference in Birmingham was so packed and multi-dimensional for me that I decided it's going to take a few very different posts to describe my experiences without sending any unfortunate readers into a deep stupor, so the overall summary first and then a couple of more focussed posts to follow.
When I left the hotel in Edinburgh on Sunday morning and the snow was falling, I feared the worst after the horrible travel experiences almost exactly one year before but, in the end, a little de-icing and a small delay for incoming pandas weren't enough to hold me up too much. I had to walk round the block for a while when I got to the Hyatt to secure a smoking room but, with presentation preparation on my early agenda, it was worth it.
Which meant I only managed to register at the ICC in time for the final Performance & HA Panel of Oak Table Sunday. As I walked into the reception area where everyone was on a break, the welcoming sign of Oak Table Beer greeted me, courtesy of ... I'm not sure who - James Morle? Well, whoever was responsible, it was much appreciated - a nice BrewDog-produced lager. Lager with taste. Magnificent. So a quick one of those and another one to take into the panel session and we were away ...
Whilst it was good to see Dave Ensor coming out of retirement once again, most of the conversation (just as at the ACE Directors Briefing in Redwood Shores recently) was taken up by the inability of people to play around with Exadata without buying the damn thing first. Whilst I definitely sit on the side of the fence where Oracle are preventing passionate techies from learning about a technology properly (and will comment to Martin Bach's post here to that effect at some point), I felt a little sorry for Greg Rahn and Graham Wood from Oracle being confronted from all sides with questions about it. But it was all good fun in the end, before we retired to All Bar One for the annual UKOUG volunteers and staff thank-you drinks before the conference starts.
It was a little different this year because they hadn't secured the whole first floor, so it was a little fragmented and not as easy to mingle, but I managed to meet up with some old friends from Standard Life, Oracle regulars and even the odd Norwegian attendee I recognised from this year's boat trip. Despite sensible folk from Oracle trying to persuade me to eat, I still ended up going to bed without having had any food (and way too much drink, in that context). But at least I wasn't as late as usual and managed to avoid the very late night exertions in the Tap and Spile or Jury's bar!
I had a couple of presentations scheduled for Monday that were deserving of some final preparation.
I was still awake (or at least moving) too late to make Kyle Hailey's SQL Tuning presentation and decided not to attend Greg Rahn's Real Time SQL Monitoring presentation because I'd already seen him present on the same subject at Openworld. That is one of the benefits of attending multiple conferences, it makes it just a little easier to make agenda choices which are always horribly difficult at UKOUG. Instead, I managed to catch the second half of Connor McDonald's first presentation of the week on Partitioning. (This would be the first of many presentations I only saw part of, as I kept having conflicting meetings or would just get stuck in a coridoor talking to people ...) I always try to see Connor when I can because he is an amazing presenter so even if I know a subject well, I'm almost guaranteed he'll be entertaining and listenable and this was no exception. Good stuff.
Next up was Maria Colgan's presentation on upgrading to the 11g Optimiser and some of the strategies you can use to make the transition easier. Maria's another presenter I always try to see and although I think I saw most of her presentations at Openworld this year, I wanted to check out this one because I knew she'd be talking about SQL Plan Management and I'd be dealing with the same subject later in the day so wanted to check the presentations would be reasonably complimentary. She was her usual refreshing and honest self and between her and Connor, I was finally starting to wake up a little!
Next up was the first of the lunchtime OAK Talks sessions (where I did a 10-minute You Probably Don't Need Exadata talk) but I'll probably write a seperate post about those.
Back to my room, then, to remove a few slides from my presentation to bring it down from an hour to 45 minutes, get changed and start my usual pacing around, trying to calm the pre-presentation adrenaline rush a little and think about what I was going to say. I know from talking to other presenters that knowing you have presentations on a given day always takes up some of your energy and focus, but I suppose I'm among the worst I know - I'd rather have all my presentations out of the way on the first day if possible!
Eventually I was ready and made it back to the conference in time to catch the start of Graham Wood and John Beresniewicz talking about the DB Time Performance Method which had been one of my first choices when the agenda was published although, because we clashed, I did warn them I'd have to leave early to make sure that I was in Hall 1 in good time. It was a shame it was such a small attendance but I suspect that was largely because it was a *2-hour* slot at the end of the day so, as well as making it harder for people to choose (because it would clash with two other session slots), most people wouldn't have the stamina! Regardless, the bit I saw was their usual great stuff and I was disappointed I had to leave early.
When I got to Hall 1, it was getting towards the end of Alex Gorbachev's I/O Measurement and Planning session so I didn't see enough to say much about the presentation other than to say that Alex was perhaps a little more subdued than usual
Then it was time for me to present - another blog post will cover that - followed by Cary Millsap's presentation - definitely another post for that. Suffice to say for now that I'm glad (although unsurprised) it was so good because typically I never attend presentations immediately after my own because I'm too wired and tired to enjoy them but had to in this case to help Lisa Dobson charge her phone off my laptop. More to the point, it was now getting very late in the day and Cary didn't finish until 19:45!
There seemed to be general agreement that the day was far too long, but that didn't stop people heading off to the Exhibition drinks, then the Focus Pubs. It was particularly enjoyable having a good chat and some drinks with two of my three presentations out of the way!
The UKOUG directors know that Maria Colgan and I know each other so somehow I ended up being charged with making sure that she turned up to the Focus Pubs because she might be needed for the Inspiring Presenter Awards and she'd been talking about not bothering because of jet-lag! I kept having to leave other friends I was with to tour the hall to make sure she didn't sneak off! Once she'd picked up her well-deserved award (I vaguely remember being completely unsurprised at seeing Jonathan Lewis collecting a couple, too) I was ready for my bed, utterly bushed after what seemed the longest day and determined to enjoy a presentation-free Tuesday.
Tuesday began with one of those 'where is the presenter?' moments as a pretty packed Media Suite awaited the arrival of Thomas Presslie talking about his user experiences with Data Guard Fast-Start Failover. In the end, he did make it on time, as he ran past me into his room but then had the worst blow a presenter could have - his laptop containing all his demos wouldn't work so there was a small delay while he got his slides moved over to the in-room netbook. After that start, I was amazed how well he pulled it all back together, introduced whisky to his visual demo and was able to get across how convinced he is by DG as a low-cost high-availability solution. Good recovery (if you'll pardon the pun).
I was sorely tempted to go and see Connor again next but figured he's guaranteed a large audience and 11g ugrades are a subject I'm all too familiar with so I plumped for Marco Gralike's presentation on "Drag, Drop and other Stuff. Using your Database as a File Server" XML presentation by way of moral support (I needn't have bothered - he had a pretty busy small room) and because I was more likely to learn something new. I did, but in areas I never expected to, including just how much detailed and potentially personal information modern image files contain! I loved his slides with the big, clear writing but it was a shame that it was very hard to see most of the damn screen in that room! Good stuff, though. I had yet-another-meeting scheduled so baled out half-way through as Mark Drake of Oracle started the demonstration (nothing personal, Mark!)
Where most people had a serious session-clash next, I'd seen several of the presentations at other conferences so was able to go and grab some food (I'd seen the conference lunch - cheese-central!) and then head over to the OAK Talks again before a pre-scheduled interview with Neil Jarvis from the UKOUG with Lavinia Foster on camera duties (something she only just managed to fulfill!). I think the idea here was to interview as many Oak Table types as possible for future use in the UKOUG's Oracle Scene magazine and, by filiming them, maybe release them as podcasts too but I'm not sure of the detailed plans. I must admit I was dreading this thing because it feels so much more intimate and focussed on me, me, me than a presentation but I think I just about got through it ok. I suppose I'll cringe if I ever see it though!!
The final presentation I attended was JB's talking about ASH Outliers. This was a must-see for me because it was a presentation all about a query JB's been working on to identify unusually long events from ASH data as a way of potentially diagnosing strange system hanging issues after the event. I'm going to do a seperate post on this presentation too because I think it's an important subject that warrants it and JB promised that I'd probably help him host the query as he is the *least* social media person I know. Or so he claims
As a sign of how long and tiring the days are, I was really concerned I was going to nod off a couple of times during this presentation despite how interested I am in the subject so decided I needed some rest before the party!
I liked the return of a slightly more formal party, with entertainers and gargoyles (!) and the masks. I must admit I thought the masks were going to be like paper ones out of a greetings card shop but they were pretty impressive and glittery and I noticed quite a few being tucked away as one of the better post-conference gifts for the kids. At least I assume they'll be for the kids. Despite everyone telling me I had loads of drinking time left, the memories of Bulgaria were all too fresh so, with a 9am presentation slot (one hour earlier than Bulgaria), I made my excuses and left. My word, I don't think I drunk past midnight once during this conference!
But, boy, was I glad I didn't as it meant I was early and prepared for my presentation on Statistics on Partitioned Objects. More in a seperate post, but I was reasonably happy with it.
I swithered over whether to attend too many presentations after that because, with all the early nights I felt I really hadn't spent enough time with all the friends I meet at UKOUG. It's actually becoming quite a challenge to see enough of people without being rude to others
So I saw some of Latch Internals in 2011 with Andrey Nikolaev which was as deep as I expected and something I know I am much better suited to reading up on later, rather than try to focus on the subject mid-conference. I know it sounds strange, but some subjects and presentations just refuse to stick in my head.
I was also massively entertained by Connor's Optimiser Stats talk. The guy really is an astounding presenter and, as he said earlier, our two stats talks touched on completely different areas so although I knew the content, it was still as refreshing as always to hear his perspective. Top marks for producing a single slide animation sequence which, with Connor's perfect verbal delivery over the top, produced spontaneous applause from the audience. Not an easy thing to achieve at an Oracle conference!
Which just about left enough time for the final OAK Talks and then the last afternoon meet-up with a whole bunch of friends from Pythian and other places I've worked, Oracle, the Oak Table and, well, all those regular conference attendees that it's so much fun to meet up with. I loved this part of the conference, actually, as we all wound down and people had a last few drinks before heading off on their own particular journeys. It's a good job I had a pre-booked train reservation or I might have been tempted to stay much later to make up for my relative abstinence earlier in the week!
There will be more posts to come, as I keep saying, but I had a terrific conference, as I expected although I was hopelessly tired when I got home!
Thanks to all the people who put the work in to make this conference happen every year, to those who took the time to come to my presentations and to all the old and new friends that make it fun!
Usual disclaimer: My travel and accommodation expenses were covered
by the Oracle ACE Director
program. The Oak Table Network, or James Morle, or somebody, provided the splendid Oak Table Beer. Paul Vallee of Pythian stood his round and more at the bar as always but I still don't say enough nice things about him ...
In a recent question on OTN someone asked why Oracle had put some columns into the overflow segment of an IOT when they had specified that they should be in the main index section (the “IOT_TOP”) by using the including clause.
The answer is simple and devious; there’s a little trap hidden in the including clause. It tells Oracle which columns to include, but it gets applied only after Oracle has re-arranged the column ordering (internally) to put the primary key columns first. The OP had put the last column of the primary key AFTER the dozen columns in the table that he wanted in the index section, but Oracle moved that column to the fifth position in the internal table definition, so didn’t include the desired 10 extra columns.
To demonstrate the feature, here’s a little sript to create an IOT and insert some data. I’ve given two possible including clauses to see how Oracle treats the columns. My primary key consists of the columns (id1, id2, id3) but I’ve put columns v1 and v2 ahead of id3 in my table definition.
create table t1 ( id1 number, id2 number, v1 varchar2(10), v2 varchar2(10), id3 number, v3 varchar2(10), constraint t1_pk primary key (id1, id2, id3) ) organization index including id3 -- including v2 overflow ; insert into t1 values(1,1,'AAAAAAAAAA','BBBBBBBBBB',1,'CCCCCCCCCC'); insert into t1 values(1,1,'AAAAAAAAAA','BBBBBBBBBB',2,'CCCCCCCCCC'); insert into t1 values(1,1,'AAAAAAAAAA','BBBBBBBBBB',3,'CCCCCCCCCC'); commit;
The including clause tells Oracle to include in the index segment all the columns “up to and and including the named column”, but if I dump the datablock that is the root block of the index, I get the following results. First when I include id3.
row#0 flag: K-----, lock: 2, len=20 col 0; len 2; (2): c1 02 col 1; len 2; (2): c1 02 col 2; len 2; (2): c1 02 tl: 9 fb: --H-F--- lb: 0x0 cc: 0 nrid: 0x0180008a.0 row#1 flag: K-----, lock: 2, len=20 col 0; len 2; (2): c1 02 col 1; len 2; (2): c1 02 col 2; len 2; (2): c1 03 tl: 9 fb: --H-F--- lb: 0x0 cc: 0 nrid: 0x0180008a.1 row#2 flag: K-----, lock: 2, len=20 col 0; len 2; (2): c1 02 col 1; len 2; (2): c1 02 col 2; len 2; (2): c1 04 tl: 9 fb: --H-F--- lb: 0x0 cc: 0 nrid: 0x0180008a.2
Notice that this segment holds data only for the three primary key columns – after rearranging the column ordering internally the columns “up to and including id3″ are just the primary key columns.
Now look what I get if I include v2:
row#0 flag: K-----, lock: 2, len=42 col 0; len 2; (2): c1 02 col 1; len 2; (2): c1 02 col 2; len 2; (2): c1 02 tl: 31 fb: --H-F--- lb: 0x0 cc: 2 nrid: 0x0180008a.0 col 0:  41 41 41 41 41 41 41 41 41 41 col 1:  42 42 42 42 42 42 42 42 42 42 row#1 flag: K-----, lock: 2, len=42 col 0; len 2; (2): c1 02 col 1; len 2; (2): c1 02 col 2; len 2; (2): c1 03 tl: 31 fb: --H-F--- lb: 0x0 cc: 2 nrid: 0x0180008a.1 col 0:  41 41 41 41 41 41 41 41 41 41 col 1:  42 42 42 42 42 42 42 42 42 42 row#2 flag: K-----, lock: 2, len=42 col 0; len 2; (2): c1 02 col 1; len 2; (2): c1 02 col 2; len 2; (2): c1 04 tl: 31 fb: --H-F--- lb: 0x0 cc: 2 nrid: 0x0180008a.2 col 0:  41 41 41 41 41 41 41 41 41 41 col 1:  42 42 42 42 42 42 42 42 42 42 ----- end of leaf block dump -----
Notice that we now have both v1 and v2 in the index segment.
Summary: If you use the including clause when defining an IOT, you have to reference a non-key column if you want any column other than the key columns in the index segment. Oracle rearranges the column ordering internally, so what you see is NOT what you get.