Oracle Indexes

Richard Foote's picture

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

Richard Foote's picture

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

KarenMorton's picture

Effective Indexing Webinar

Thanks to everyone for attending today's Effective Indexing webinar sponsored by IOUG with Embarcadero. For attendees, IOUG will likely send out a link to the recording and PDF of the presentation, but I also wanted to post it here.

Presentation PDF
Webinar recording

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

12c Invisible Columns (The Invisible Man)

Oracle 12c introduced the ability to make user specified columns “Invisible” such that they’re invisible by default and have to be explicitly referenced to be accessed. So a simple SELECT * won’t display columns defined as invisible, but they will be displayed if explicitly referenced in the SELECT column list. From an indexing perspective, columns […]

Richard Foote's picture

12c Indexing Extended Data Types Part II (15 Steps)

Finally, at long long last, I have a spare 30 minutes in my life to complete this blog entry !! As discussed previously, Oracle has extended the maximum length of varchar2, nvarchar and raw columns to 32K, but this comes with some challenges when it comes to indexing such columns due to restrictions on the […]

Syndicate content