12.1.0.2 Released With Cool Indexing Features (Short Memory)

Oracle Database 12.1.0.2 has finally been released and it has a number of really exciting goodies from an indexing perspective which include: Database In-Memory Option, which enables specific portions of the database to be in dual format, in both the existing row based format and additionally into an efficient memory only columnar based format. This in […]

Presenting at UKOUG Tech14 Conference (Ian Fish, U K Heir)

I’ve been lucky enough to present at various conferences, seminars and user group events over the years in some 20 odd countries. But somewhere I’ve never quite managed to present at before is the place of my birth, the UK. Well this year, I’ve decided end my drought and submitted a number of papers for the UKOUG Tech14 Conference and […]

12c Index Like Table Statistics Collection (Wearing The Inside Out)

This change introduced in 12c has caught me out on a number of occasions. If you were to create a new table: And then populate it with a conventional insert: We find there are no statistics associated with the table until we explicitly collect them: But if we were to now create an index on this […]

It was 12 years ago today…

It was exactly 12 years ago today that I first presented my Index Internals – Rebuilding The Truth presentation at a local ACT Oracle User Group event. And so my association with Oracle indexes started. It would be an interesting statistic to know how many people have subsequently read the presentation :) It would no doubt result in […]

Richard Foote’s Art Work (Art Decade)

Just a quick note to say I have a new tab on the blog called “Richard Foote’s Art” where I’ll periodically upload a new piece of my art work. As the page says, they highlight both what’s in my mind (so beware) and how I like to relax in the evenings. All pieces are for […]

Estimate Index Size With Explain Plan (I Can’t Explain)

I discussed recently an updated MOS note that details the needs vs. the implications of rebuilding indexes. Following is a neat little trick if you want to very quickly and cheaply estimate the size of an index if it were to be rebuilt or a new index before you actually create the thing. I meant to blog about this sometime […]

Presenting at ODTUG Kscope14 Conference in Seattle June 22-26 2014

  Just a short note to say I’ll be presenting at the Oracle Development Tools User Group (ODTUG) Kaleidoscope 14 Conference this year in beautiful Seattle, Washington on June 22-26 2014. I had a fantastic time when I attended this conference a few years ago when it was held in Monterey so I’m really looking forward to […]

Indexing Foreign Key Constraints With Invisible Indexes (Invisible People)

In my previous post I discussed when deleting rows from parent tables, how Bitmap Indexes based on the FK constraint can prevent the expensive Full Tables Scans (FTS) on the child tables but not the associated exclusive table locks. Last year, I discussed how it was possible in Oracle Database 12c to have multiple indexes on the same column list. […]

Indexing Foreign Key Constraints With Bitmap Indexes (Locked Out)

Franck Pachot made a very valid comment in my previous entry on Indexing Foreign Keys (FK) that the use of a Bitmap Index on the FK columns does not avoid the table locks associated with deleting rows from the parent table. Thought I might discuss why this is the case and why only a B-Tree index does […]

Indexing Foreign Keys (Helden)

A recent question on an internal forum asked whether an index on a Foreign Key (FK) constraint designed to avoid locking issues associated with deletes on the parent tables needs to precisely match the columns in the FK. Could the columns in the index potentially be a different order or be appended with additional columns ? The answer is […]

Index Rebuild, the Need vs the Implications Support Note 989093.1 (Getting Better)

Once upon a time, Oracle Support had a note called Script: Lists All Indexes that Benefit from a Rebuild (Doc ID 122008.1) which lets just say I didn’t view in a particularly positive light :) Mainly because it gave dubious advice which included that indexes should be rebuilt if: Deleted entries represent 20% or more of […]

Modify Primary Key Using Index (Learning To Fly)

One of the things I love about working with Oracle Database technologies is that there’s just so much one can learn. I make it an active goal of mine to try to learn something new at work each and every day, no matter how big or small. One of these days, I might blog about a presentation I put together […]

12c Enhanced Online Index DDL Operations (Lady Godiva’s Operation)

In my last couple of posts, I discussed how table partitions can be moved online since 12c, keeping all indexes in sync as part of the process. 12c also introduced enhancements to a number of index related DDL statements, removing blocking locks and making their use online and far less intrusive. The following commands now have a new ONLINE […]

12c Online Partitioned Table Reorganisation Part II (Move On)

In Part I, we looked at some of the issues associated with locking and unusable indexes when Moving both tables and table partitions. The Oracle 12c Database has introduced a number of great new capabilities associated with online DDL activities. One of these really useful features is the capability to now move table partitions online while […]

12c Online Partitioned Table Reorganisation Part I (Prelude)

First post for 2014 !! Although it’s generally not an overly common activity with Oracle databases, reorganising a table can be somewhat painful, primarily because of the associated locking implications and the impact it has on indexes. If we look at the following example: So we have a table with a couple of indexes. We […]