Jonathan Lewis's picture

Modify PK

Sitting in the lounge waiting to be called for my flight I was musing on the 12c feature of having multiple indexes defined on the same ordered column set  when a thought crossed my mind and I decided to run a little test that looked like this:

Jonathan Lewis's picture


I was involved in a thread on Oracle-L recently started with the question: “How many LIOs is too many LIOs”. Rather than rewrite the whole story, I’ve supplied a list of links to the contributions I made, in order – the final “answer” is actually the answer to a different question – but travels an interesting path to get there.#

Jonathan Lewis's picture

Bitmap question

If you know anything about bitmap indexes you probably know that a single entry in a bitmap index takes the form (key_value, starting rowid, ending rowid, BBC compressed bit string). So an entry covers a single value for a column over a range of rowids  in the table, and the string of bits for that (notional) range is reduce to a minimum by a compression mechanism that eliminate repeated zeros in multiples of 8.

So here’s a question – to which I don’t know the answer, although you may be surprised when you try to find it:

If you have a very large table and in one of its columns the first row and the last row (and no others) hold the value 0 (say) and you create a bitmap index on this column, what’s the largest number of rows you could have in the table before Oracle would HAVE to create two index entries in order to cover both rows ?

Jonathan Lewis's picture

Index Hash

I’m afraid this is one of my bad puns again – an example of the optimizer  making a real hash of the index hash join. I’m going to create a table with several indexes (some of them rather similar to each other) and execute a query that should do an index join between the obvious two indexes. To show how obvious the join should be I’m going to start with a couple of queries that show the cost of simple index fast full scans.

Here’s the data generating code:

Jonathan Lewis's picture

dbms_space usage

Just throwing out a brief comment (one of my many draft notes that I don’t have time to complete) about the dbms_space package. You’re probably familiar with this package and how, for ASSM segments, it can give you a measure of the available space in the blocks in a data segment, reporting 6 possible states of the blocks below the high high water mark (HHWM) e.g.:

Unformatted                   :       12
Freespace 1 (  0 -  25% free) :        0
Freespace 2 ( 25 -  50% free) :       14
Freespace 3 ( 50 -  75% free) :        0
Freespace 4 ( 75 - 100% free) :        0
Full                          :       34

The above is the output from a little procedure I wrote to wrap around a call to the procedure dbms_space.space_usage().

Jonathan Lewis's picture

Bitmap Question

This question came up on the OTN database forum a couple of months ago: “Why doesn’t Oracle allow you to create globally partitioned bitmap indexes?” The obvius answer is “It just doesn’t, okay.” But it can be quite interesting to think of reasons why a particular mechanism might not have been implemented – sometimes the answers can give you an insight into how a feature has been implemented, it might suggest cases where a feature might not work very well, it might give you some ideas on how to work around a particular limitation, and sometimes it just an entertaining puzzle to while away a short flight.

Jonathan Lewis's picture

Bitmap join indexes

Here’s another of my “draft” notes that needs some exapansion and, most importantly, proof.

I have a fact table with a “status id” column that shows a massive skew. Unfortunately I have a dimension table that holds the “status code”, so (in theory, at least) I have to do a join from the statuses table to the fact table to find rows of a given status. Unfortunately the join hides the skew:

select  f.* 
from    facts f, statuses s
where   s.code = 'C'
and     f.status_id = s.status_id

The optimizer knows that the status_id column on the facts table has a highly skewed distribution and will create a histogram on it, but it can’t know which status code corresponds to which status_id, so the histogram doesn’t help in calculating the join cardinality.

Will a bitmap join index help ? Answer – NO.

Jonathan Lewis's picture

32K Columns

Oracle 12c has increased the maximum length of character-based columns to 32K bytes – don’t get too excited, they’re stored out of lines (so similar in cost to LOBs) and need some modification to the parameter file and data dictionary (starting the database in upgrade mode) before you can use them.

Richard Foote has a pair of articles on indexing such columns:

Jonathan Lewis's picture

Reverse Key

Here’s one of those little details which I would have said just couldn’t be true – except it’s in the manuals, and the manuals happen to be right.

Jonathan Lewis's picture

12c In-memory

I wrote a note about the 12c “In-Memory” option some time ago on the OTN Database forum and thought I’d posted a link to it from the blog. If I have I can’t find it now so, to avoid losing it, here’s a copy of the comments I made:

Juan Loaiza’s presentation is probably available on the Oracle site by now, but in outline: the in-memory component duplicates data (specified tables – perhaps with a restriction to a subset of columns) in columnar format in a dedicated area of the SGA. The data is kept up to date in real time, but Oracle doesn’t use undo or redo to maintain this copy of the data because it’s never persisted to disc in this form, it’s recreated in-memory (by a background process) if the instance restarts. The optimizer can then decide whether it would be faster to use a columnar or row-based approach to address a query.

To prevent automated spam submissions leave this field empty.
Syndicate content