Search

OakieTags

Who's online

There are currently 0 users and 33 guests online.

Recent comments

Affiliations

Primary Key

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

Primary Keys Guarantee Uniqueness? Think Again.

When you create a table with a primary key or a unique constraint, Oracle automatically creates a unique index, to ensure that the column does not contain a duplicate value on that column; or so you have been told. It must be true because that is the fundamental tenet of an Oracle database, or for that matter, any database.

Well, the other day I was checking a table. There is a primary key on the column PriKey. Here are the rows:

Select PriKey from TableName;

PriKey
------
1
1

I got two rows with the same value. The table does have a primary key on this column and it is enforced. I can test it by inserting another record with the same value - “1”:

SQL> insert into TableName values (1,…)

Application Design is the only Reason for Deadlocks? Think Again

#38761d;">[Updated on 4/20/2013 after feedback from #38761d;">Charles Hooper#38761d;">, #38761d;">Jonathan Lewis#38761d;">, #6aa84f;">Lau#38761d;">rent Schneider and #38761d;">Mohamed Houri#38761d;"> and with some minor cosmetic enhancements of outputs]

IOT Secondary Indexes – The Logical ROWID Guess Component Part I (Lucky)

As discussed previously, an index entry within a Secondary Index on an Index Organized Table (IOT) basically consists of the indexed column(s) and the Logical Rowid, the PK column(s) and a “guess” to the physical block in the IOT containing the corresponding row. Let’s discuss this “guess” component in a bit more detail. When the Secondary [...]

IOT Secondary Indexes: Primary Key Considerations (Beauty And The Beast)

As discussed previously, one of the nice features of an IOT Secondary Index is that it contains the mandatory Primary Key of the IOT, which is always maintained and can be used to access the necessary rows of the IOT regardless of  row movement within the IOT itself. This can also be beneficial if only the PK [...]

Index Organized Tables – Overflow Segment Part II (The Loneliest Guy)

In my previous post on Index Organized Tables (IOT), I introduced the concept of the IOT Overflow Segment, where we can store columns that we may not want to include within the actual IOT index structure. Before we move on, I just wanted to cover off a few additional points that could be a trap for the [...]

Index Organized Tables – An Introduction Of Sorts (Pyramid Song)

Thought it was high time that I covered in a little detail the subject of Index Organized Tables (IOTs). When used appropriately, they can be an extremely useful method of storing and accessing data. Hopefully by the end of this series, you’ll have a better understanding of IOTs, their respective strengths and weaknesses and so perhaps [...]