Posted by Richard Foote in Oracle Mix.
Just a quick note to say time is running out for anyone interested in suggesting or nominating for a session at this year’s Oracle OpenWorld via Oracle Mix. Nominations close this coming Monday, 30 June 2008, so you don’t have much time left if you wish to nominate a presentation that might be of interest to folks attending OOW 2008. If you think you have something interesting to contribute, go on, give it a go !!
Voting closes on Sunday 13 July 2008. If you want to have a say and help select sessions that would be of interest to you at OOW 2008, this is a great opportunity to have direct input. The most popular 35 sessions as voted by you, the Oracle community, will be selected and will be on this year’s schedule at OOW 2008.
If you’re planning on attending OOW this year and my sessions or any of the many other interesting nominated sessions are of interest to you, then I encourage you to vote and have a say in what will be on the agenda.
Posted by Richard Foote in Index Delete Operations, Oracle Indexes.
In Part I, we looked at how index entries are only marked as deleted during delete/update operations. This means the DML operation and resultant transaction doesn’t have to concern itself with physically cleaning out the deleted entries.
The question is therefore, are these deleted index entries “deadwood”, wasted space that makes the index less compact and less efficient over time requiring a potential periodic index rebuild, or can this deleted space be subsequently reused by Oracle ?
There’s a common perception that deleted space within an index is indeed “deadwood” or can only be reused in very specific circumstances, such as when an identical index value is subsequently re-inserted.
However, this is just another of Oracle’s infamous urban myths. In the vast majority of cases, deleted space within an index can indeed be recycled and can indeed be subsequently reused. Today, I’m just going to begin by focusing on the most common method by which deleted index entries are generally cleaned up.
All it takes to clean out all the deleted index entries within a specific index leaf block is a subsequent insert in the block. That’s it, that’s all it takes. Just one new index entry in a leaf block will automatically clean out all associated deleted index entries that may currently exist within the block. Even if there are hundreds of deleted entries with the leaf block, just the one new index entry will clean them all out. Note the new index value doesn’t have to be the same as any of the deleted index entries, it doesn’t even have to be within the range of any of the deleted index entries within the block. Any new index entry within the leaf block will do the job quite nicely.
A very simple demonstration I use to highlight this point. First, create a simple table and associated index with 10 rows.
Next, deleted say 4 of the rows from the table.
Note: It clearly shows 4 index entries are indeed currently marked as deleted.
Note: It only shows 6 rrow but 10 nrow values, clearly suggesting there are currently 4 deleted index entries.
That indeed there are currently 4 index entries marked as deleted with the ‘D’ flag within the index block.
That indeed, INDEX_STATS no longer has any statistics of deleted index entries.
That the Index Tree dump no longer has any record of the deleted index entries.
the count of deleted index entries within the block has been reset to 0.
For most randomly inserted indexes, this means deleted entries will be eventually automatically cleaned out and the freed space reused by subsequent insert operations. Worrying about deleted space and rebuilding such indexes is typically unnecessarily.
I’ve just recently returned from a very hectic but enjoyable visit to Sweden and The Netherlands to do some training for Oracle University. I had two great classes and met a whole bunch of really nice people so a big thank you to all who attended and made me feel so welcome.
Stockholm is a really beautiful city that reminds me somewhat of Sydney with it’s magnificent harbour. However, as with most cities in Europe, it’s the history and amazing architecture of the place that made it a real joy to simply walk around and explore. The highlight for me though was visiting the Vasa Museum and seeing first hand the incredibly well preserved wreak of the massive but doomed Vasa, which sank in the harbour on it’s maiden voyage in 1628. Hidden for centuries, it was only rediscovered in the 1950’s and can now be seen in almost all her glory at the museum. Amazing. The highlight for the Swedes during my visit was no doubt beating Greece in Euro 2008 and it was fun being in the crowd and watching the match on a large outdoor screen.
Utrecht in the Netherlands was another lovely city to visit. You know the place has some history when the so-called “New” canal was built in the 1400s. Being Australian, anything older than a hundred years is deemed antique here so I soak up history whenever I’m lucky enough to get the opportunity. With it’s narrow cobbled streets and picturesque canals, it was another great place to simply walk around and enjoy. Being a keen cyclist, this was certainly my kind of town with people on bikes just everywhere and interestingly, I saw very few overweight locals. I must have that lucky touch as during my visit, the Dutch beat France 4-1 in Euro 2008. If any country is interested, there’s still time for my services if required, although perhaps not so for the host countries.