Skip navigation.

Richard Foote

Syndicate content Richard Foote's Oracle Blog
Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music
Updated: 12 hours 7 min ago

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

Indexing and Transparent Data Encryption Part II (Hide Away)

Wed, 2015-05-20 02:03
In Part I, I quickly ran through how to setup an encrypted tablespace using Transparent Data Encryption and to take care creating indexes outside of these tablespaces. Another method of encrypting data in the Oracle database is to just encrypt selected columns. Although the advantage here is that we can just encrypt sensitive columns of interest (and that the […]
Categories: DBA Blogs

Indexing and Transparent Data Encryption Part I (The Secret Life of Arabia)

Mon, 2015-05-18 23:42
Database security has been a really hot topic recently so I thought I might write a few posts in relation to indexing and Transparent Data Encryption (TDE) which is available as part of the Oracle Advanced Security option. To protect the database from unauthorized “backed-door” accesses, the data within the actual database files can be encrypted. […]
Categories: DBA Blogs

Singapore Maths Question Solution and Very Interesting Observation (The Trickster)

Thu, 2015-04-23 02:22
OK, time to reveal the solution to the somewhat tricky Singapore maths exam question I blogged previously. Remember, there were 10 dates: May 13   May 15   May 19 June 13   June 14 July 16   July 18 August 14   August 15   August 16 Bowie only knew the month of my birthday, Ziggy only knew the day. Bowie […]
Categories: DBA Blogs

Good Singapore Maths Students Would Likely Make Good Oracle DBAs (Problems)

Tue, 2015-04-14 19:46
An interesting mathematics based question from a Singapore high school exam has been doing the internet rounds in the past few days. Considering it’s aimed at high school students, it’s a tricky one and obviously designed to filter out the better students, in a country with a very good reputation for churning out mathematically gifted […]
Categories: DBA Blogs

Oracle Database In-Memory Test Drive Workshop: Canberra 28 April 2015

Sun, 2015-03-29 21:17
I’ll be running a free Oracle Database In-Memory Test Drive Workshop locally here in Canberra on Tuesday, 28th April 2015. Just bring a laptop with at least 8G of RAM and I’ll supply a VirtualBox image with the Oracle Database 12c In-Memory environment. Together we’ll go through a number of hands-on labs that cover: Configuring the Product Easily […]
Categories: DBA Blogs