Richard Foote

Well, it’s certainly been a hectic time these past few weeks so I thought it might be time to catch up on a few things. I’ve just returned from attending and presenting at the Oracle Days 2008 Conference held by the Puget Sound Oracle User Group in Seattle, USA (well Bellevue actually). I had a great time, [...]

I’ve just passed yet another milestone since starting this Blog. I’ve just deleted my 500th spam comment. How exciting is that !! To celebrate this momentous occasion, I thought I might open up the Blog to some requests and suggestions. Are there any particular topics you would like covered ? Is there something I might already have previously [...]

I’ve recently been discussing how empty index blocks or those blocks that contain nothing but deleted index entries are placed on the index freelist and can potentially be recycled during subsequent index block split operations. A point that’s not so well known about such empty index blocks is how Oracle considers them when calculating index related statistics and the [...]

Just a quick note to say you can now book a place on my Oracle Index Internals Seminar as part of the Oracle University Celebrity Seminar Series scheduled for various European locations in November. Dates and links are: Belgium: 3-4 November 2008 Finland: 6-7 November 2008 Germany: 10-11 November 2008 Turkey: 13-14 November 2008 Hope to catch up with many of you at one of these events. Also, [...]

Last night, I answered a question on the Database OTN forum regarding Database Re-Org and Performance Tuning. I thought it might be worthwhile sharing my response here as it’s something I feel quite strongly about. Basically my response to the question of what basic steps one should follow when performing performance tuning was: 1) Identify an actual problem that [...]

As discussed in Part IV, index blocks that contain nothing but deleted index entries are placed on the index freelist and can be recycled in subsequent index block split operations. This of course is a good thing as it helps to keep the index as small and efficient as possible without having to perform relatively expensive [...]

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 [...]

Yet another method of cleaning out deleted space Oracle has up its sleeve is the recycling of index blocks that contain nothing but deleted index entries. In some cases, it’s possible for an index block to contain no current index entries with all the corresponding index entries within the index block having been deleted. The index block may be totally empty [...]

Another little post while I look after some unwell munchkins … I’ve already looked at the most common example of when Oracle will automatically clean out deleted index entries, that being any subsequent insert into a leaf block will clean out the deleted entries that may exist from the associated leaf block. Another example of Oracle automatically [...]

You can now finally enroll for all the Australian Oracle Index Internals Seminar dates scheduled by Oracle University for most capital cities in September 2008: http://www.oracle.com/education/apac/au_richard_foote_sept08.html Dates are: Brisbane: 1st - 2nd September Canberra: 4th - 5th September Sydney: 8th - 9th September Melbourne: 11th - 12th September Perth: 15th - 16th September Oracle University are offering an early bird price if you book on [...]

I’m back !! 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 [...]

  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 [...]

Just before I hop on a plane to do some training in Europe, thought I might begin a little series on deleted space within an index. I’ll begin with a short piece on how we can determine what deleted space an index may currently have before beginning a discussion on whether this deleted space can indeed be reused by Oracle. Generally speaking, [...]

Based on this OTN thread, it appears there may still be folk out there that think Oracle uses Unique Indexes to police a Primary Key (or Unique Key) constraint. This is of course not necessarily true as since 8.0 and the introduction of Deferrable Constraints, Oracle can just as easily police a PK (or UK) constraint [...]

For all the many many readers I have from the beautiful island of Singapore, just a short note to let you both know you can now enroll for my 2 day Index Internals Seminar scheduled for 18-19 September 2008: http://www.oracle.com/global/sg/education/eblast/sg_richard_foote_sept08.html Let me please apologise up front for the associated photo, hopefully it won’t scare too many people away … Interestingly, [...]

I’ve recently dug up an old presentation I did for the local Oracle User Group a number of years ago: Yet Another Presentation On Extended Tracing It’s slightly dated but has some useful general information on the subject of diagnosing performance issues and Extended SQL Tracing in Oracle that some may find useful, so I thought it might be worth [...]

In my previous post regarding Reading 100% of Data via an Index, one of the examples I described was the scenario where an index was used to avoid a sort. Index entries are always logically stored in the same order as the indexed columns (except of course when using a Reverse Key Index). Therefore if an index [...]

I recently came across this video on the Green Plastic Radiohead website, of a performance of 15 Steps by Radiohead on the excellent “Later With Jools Holland” show. It’s totally hilarious and so very very clever. The challenge is indeed to watch it all without laughing !! Whoever put it together deserves much credit, although I do slightly prefer the original version [...]

I’ve already previously discussed how a Full Table Scan can sometimes be the most effective execution path for reading a relatively small proportion of all rows (< 1%). Thought I might quickly discuss how an index can be the most effective execution path for reading a relatively high proportion of rows. Such as 100% of all [...]

An excellent question by fellow Aussie Chris Muir on this OTN Thread reminded me of a little trick I picked up in my travels. Basically the question is how can one make a table read-only before 11g ? The thread mentions a number of possibilities, some better than others. I thought I might just mention this possible [...]