Skip navigation.

Richard Foote

Syndicate content Richard Foote's Oracle Blog
Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music
Updated: 28 min 6 sec ago

Index Advanced Compression: Multi-Column Index Part II (Blow Out)

Thu, 2015-09-24 02:00
I previously discussed how Index Advanced Compression can automatically determine not only the correct number of columns to compress, but also the correct number of columns to compress within specific leaf blocks of the index. However, this doesn’t mean we can just order the columns within the index without due consideration from a “compression” perspective. As […]
Categories: DBA Blogs

Index Advanced Compression: Multi-Column Index Part I (There There)

Thu, 2015-09-17 00:57
I’ve discussed Index Advanced Compression here a number of times previously. It’s the really cool additional capability introduced to the Advanced Compression Option with, that not only makes compressing indexes a much easier exercise but also enables indexes to be compressed more effectively than previously possible. Thought I might look at a multi-column index to highlight just […]
Categories: DBA Blogs

Presenting the Hotsos Symposium Training Day – 10 March 2016 (Heat)

Wed, 2015-09-16 04:06
I’ve just accepted an invitation to present the Hotsos Symposium Training Day on 10 March 2016 in sunny Dallas, Texas. In the age of Exadata and In-Memory databases, it’ll be an updated and consolidated version of my Index Internals and Best Practices seminar. With an emphasis on using indexes appropriately to boost performance, it’ll feature […]
Categories: DBA Blogs

Presenting in Perth on 9 September and Adelaide on 11 September (Stage)

Sat, 2015-08-22 05:54
For those of you lucky enough to live on the western half of Australia, I’ll be presenting at a couple of events in both Perth and Adelaide in the coming weeks. On Wednesday, 9th September 2015, I’ll be presenting on Oracle Database 12c New Features For DBAs (and Developers) at a “Let’s Talk Oracle” event […]
Categories: DBA Blogs

Why A Brand New Index Might Benefit From An Immediate Coalesce (One Slip)

Mon, 2015-07-06 01:58
A recent question on the OTN Forums Reg: Index – Gathering Statistics vs. Rebuild got me thinking on a scenario not unlike the one raised in the question where a newly populated index might immediately benefit from a coalesce. I’ve previously discussed some of the pertinent concepts such as how index rebuilds can make indexes bigger, not smaller […]
Categories: DBA Blogs

Quiz Time. Why Do Deletes Cause An Index To Grow ? (Solution)

Mon, 2015-06-29 00:27
OK, time to reveal how a couple of simple deletes can cause an index to double in size. If we go back and look at the tree dump before the delete operation: —– begin tree dump branch: 0x180050b 25167115 (0: nrow: 19, level: 1) leaf: 0x180050c 25167116 (-1: row:540.540 avs:4) leaf: 0x180050d 25167117 (0: row:533.533 […]
Categories: DBA Blogs

Quiz Time. Why Do Deletes Cause An Index To Grow ? (Up The Hill Backwards)

Thu, 2015-06-25 01:02
OK, time for a little quiz. One of the things I’ve seen at a number of sites is the almost fanatical drive to make indexes as small as possible because indexes that are larger than necessary both waste storage and hurt performance. Or so the theory goes …   :) In many cases, this drives DBAs to […]
Categories: DBA Blogs

Empty Leaf Blocks After Rollback Part II (Editions of You)

Wed, 2015-06-24 01:35
In my last post, I discussed how both 1/2 empty and totally empty leaf blocks can be generated by rolling back a bulk update operation. An important point I made within the comments of the previous post is that almost the exact scenario would have taken place had the transaction committed rather than rolled back. A […]
Categories: DBA Blogs

Empty Leaf Blocks After Rollback Part I (Empty Spaces)

Tue, 2015-06-23 00:09
There’s been an interesting recent discussion on the OTN Database forum regarding “Index blank blocks after large update that was rolled back“. Setting aside the odd scenario of updating a column that previously had 20 million distinct values to the same value on a 2 billion row table, the key questions raised are why the blank index leaf blocks […]
Categories: DBA Blogs

Index Tree Dumps in Oracle 12c Database (New Age)

Sun, 2015-06-21 23:56
I’ve previously discussed Index Tree Dumps but I’ve recently found a nice little improvement that’s been introduced in Oracle Database 12c. Let’s begin by creating a little table and index: To generate an Index Tree Dump, we first need the OBJECT_ID of the index: And then use it to generate the Index Tree Dump: Previously, an […]
Categories: DBA Blogs

Indexing and Transparent Data Encryption Part III (You Can’t Do That)

Tue, 2015-06-16 00:28
In Part II of this series, we looked at how we can create a B-Tree index on a encrypted column, providing we do not apply salt during encryption. However, this is not the only restriction with regard to indexing an encrypted column using column-based encryption. If we attempt to create an index that is not a […]
Categories: DBA Blogs