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.
Oracle Certified Master DBA