Skip navigation.

Three impossibilities with partitioned indexes

There are three restrictions on indexing and partitioning: a unique index cannot be local non-prefixed; a global non-prefixed index is not possible; a bitmap index cannot be global. Why these limitations? I suspect that they are there to prevent us from doing something idiotic.

This is the table used for all examples that follow:

CREATE TABLE EMP
      (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
       ENAME VARCHAR2(10),
       JOB VARCHAR2(9),
       MGR NUMBER(4),
       HIREDATE DATE,
       SAL NUMBER(7,2),
       COMM NUMBER(7,2),
       DEPTNO NUMBER(2) )
PARTITION BY HASH (EMPNO) PARTITIONS 4;

the usual EMP table, with a partitioning clause appended. It is of course a contrived example. Perhaps I am recruiting so many employees concurrently that a non-partitioned table has problems with buffer contention that can be solved only with hash partitioning.

Why can't I have a local non-prefixed unique index?
A local non-unique index is no problem, but unique is not possible:

orclz> create index enamei on emp(ename) local;

Index created.

orclz> drop index enamei;

Index dropped.

orclz> create unique index enamei on emp(ename) local;
create unique index enamei on emp(ename) local
                              *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

You cannot get a around the problem by separating the index from the constraint (which is always good practice):

orclz> create index enamei on emp(ename) local;

Index created.

orclz> alter table emp add constraint euk unique (ename);
alter table emp add constraint euk unique (ename)
*
ERROR at line 1:
ORA-01408: such column list already indexed


orclz>

So what is the issue? Clearly it is not a technical limitation. But if it were possible, consder the implications for performance. When inserting a row, a unique index (or a non-unique index enforcing a unique constraint) must be searched to see if the key value already exists. For my little four partition table, that would mean four index searches: one of each local index partition. Well, OK. But what if the table were range partitioned into a thousand partitions? Then every insert would have to make a thousand index lookups. This would be unbelievably slow. By restricting unique indexes to global or local prefixed, Uncle Oracle is ensuring that we cannot create such an awful situation.

Why can't I have a global non-prefixed index?
Well, why would you want one? In my example, perhaps you want a global index on deptno, partitioned by mgr. But you can't do it:

orclz> create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4;
create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4
                                                                *
ERROR at line 1:
ORA-14038: GLOBAL partitioned index must be prefixed


orclz>
This index, if it were possible, might assist a query with an equality predicate on mgr and a range predicate on deptno: prune off all the non-relevant mgr partitions, then a range scan. But exactly the same effect would be achieved by using global nonpartitioned concatenated index on mgr and deptno. If the query had only deptno in the predicate, it woud have to search each partition of the putative global partitioned index, a process which would be just about identical to a skip scan of the nonpartitioned index. And of course the concatenated index could be globally partitioned - on mgr. So there you have it: a global non-prefixed index would give you nothing that is not available in other ways.

Why can't I have a global partitioned bitmap index?
This came up on the Oracle forums recently, https://forums.oracle.com/thread/2575623
Global indexes must be prefixed. Bearing that in mind, the question needs to be re-phrased: why would anyone ever want a prefixed partitioned bitmap index? Something like this:

orclz>
orclz> create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4;
create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4
                                       *
ERROR at line 1:
ORA-25113: GLOBAL may not be used with a bitmap index

orclz>

If this were possible, what would it give you? Nothing. You would not get the usual benefit of reducing contention for concurrent inserts, because of the need to lock entire blocks of a bitmap index (and therefore ranges of rows) when doing DML. Range partitioning a bitmap index would be ludicrous, because of the need to use equality predicates to get real value from bitmaps. Even with hash partitions, you would not get any benefit from partition pruning, because using equality predicates on a bitmap index in effect prunes the index already: that is what a bitmap index is for. So it seems to me that a globally partitioned bitmap index would deliver no benefit, while adding complexity and problems of index maintenance. So I suspect that, once again, Uncle Oracle is protecting us from ourselves.

Is there a technology limitation?
I am of course open to correction, but I cannot see a technology limitation that enforces any of these three impossibilities. I'm sure they are all technically possible. But Oracle has decided that, for our own good, they will never be implemented.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

Great article

How can you walk past an article that not only enumerates the revelations therein, but also labels them "impossibilities"? I put it to you that ignoring such an article is a fourth impossibility. Lucky for you it was a good one!

You gave me two scares, though. I thought you were wrong on the first two impossibilities; one was just my misunderstanding of your assertion, but I will take you to task on the other because I think you have over-stated the impossibility.

Starting with my misunderstanding...

With the "a global non-prefixed index is not possible", I nearly choked on my cuppa because I recommend this exact thing all the time. Lazy DBAs (and lazy, stupid DBAs too) will sometimes ban global indexes because they complicate partition maintenance on the table. But sometimes - especially with highly selective keys - a global index just makes good sense. Furthermore, partitioning such a global index frequently makes sense. By saying that such a global, partitioned index must be "prefixed", I thought you meant that its first indexed column must be the *Table* Partition Key. In fact, the restriction is that the first column(s) of the index must be the *Index* Partition Key. The body of your article cleared this up nicely.

But still, in your example, you made the index partition key (MGR) a column that is not in the index at all. That just doesn't make sense to me. Of course you can't partition an index on a column it does not contain. A more interesting example (in my mind) would be:

orclz> create index deptnoi on emp(mgr, deptno) global partition by hash(mgr) partitions 4;

versus

orclz> create index deptnoi on emp(deptno, mgr) global partition by hash(mgr) partitions 4;

The first one would work, because the index partition key (MGR) is the first column in the index. But the second example would (should? I haven't tested it) fail; even though the index contains MGR, it is not a prefix.

Going back to your reason for the article - explaining why these things are impossible - this makes for a more difficult explanation. Why is it not enough to simply have the column present? Why must it be a prefix?

And taking you to task...

In the first impossibility, I think you have over-stated the case: "a unique index cannot be local non-prefixed". The clue is in the error message: "ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index"

It is enough that the local unique index simply *contains* the table partition key; it does not need to be a prefix. My understanding of the term "prefixed" is that the first column(s) of a prefixed index must form the partition key.

Eg. If the partition key was DEPTO, then it would be OK to create a local unique index on (ENAME, DEPTNO) even though it is not prefixed.

Again, good article, and thanks for stirring up my thought processes.

Ross Leishman

local non-prefixed indexes

Thank you for taking me to task, Ross. I was hoping you would comment. It was your excellent series on bitmaps (to which I have referred a few people) that pushed me to write up these topics.
I stated "a unique index cannot be local non-prefixed", which as you say is not correct. Using my same sample table, this is a unique local non-prefixed index, and it works:

orclz> create unique index enamei on emp(ename,empno) local;

Index created.

orclz>
because, as you say, the table partitioning key is present in the index key. My previous test was not sufficient. It is understandable why this works: including the table partitioning key in the index key means that when inserting rows only one index partition need be searched in order to check for uniqueness. Without the table partitioning key, all index partitions would need to be searched. But clearly, the table partitioning key need not be the leading column of the index key.