Three impossibilities with partitioned indexes

Submitted by John Watson on Sun, 2013-09-01 11:22
articles: 
RDBMS Server

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

»

Comments

Submitted by rleishman on Sat, 2013-09-07 00:12.

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

»

Submitted by John Watson on Sun, 2013-09-08 08:01.

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.

»

Submitted by manuel6677 on Tue, 2015-02-24 11:24.

Thanks to both of you for kindly sharing yor know-how.

I was wondering which order of the columns of a unique partitioned local index would be better in order to get better access time.

For example lets say we have Customer_number and Sales_date as the index columns. Is it better to have the index ON (Customer_number,Order_number, Sales_date) or is it better ON (Sales_date, Customer_number, Order_number)? In this example Customer_number is the PK and Sales_date is the range partitioning key.

I think that because Oracle will try to make the partition pruning at first instance, it may be better to have the index ON (Sales_date, Customer_number, Order_number).

Could you please comment me about it?

»

Submitted by rleishman on Mon, 2015-03-02 23:25.

It depends on the queries you want the index to serve.
- If you query with an equals predicate on all three columns, then it doesn't matter the order.
- If you want to query ONLY on the PK (Customer_number) then it needs to be first (though you should note that if you are not supplying the partition key Sales_date then that PK query will scan every partition of the index)

The same rules apply as for non-partitioned indexes: the columns in your WHERE clause should form a leading subset of the index, regardless of partitioning.

»

Submitted by manuel6677 on Tue, 2015-02-24 13:33.

Thanks for your kind sharing of your Oracle know-how. I would like to ask for your advice if it is preferrable for an local unique index to define the partitioning key as the first column of the index or as the last column. By preferrable I mean better response time.

For example:

Range Partitioning column: ORDER_DATE

Unique Identifier: CUSTOMER_ID + CUSTOMER_ORDER_NUMBER

is
CREATE UNIQUE INDEX .... on (CUSTOMER_ID, CUSTOMER_ORDER_NUMBER, ORDER_DATE) LOCAL

better than

CREATE UNIQUE INDEX .... on (ORDER_DATE, CUSTOMER_ID, CUSTOMER_ORDER_NUMBER) LOCAL

Thanks in advance for your attention.

»

Submitted by ojock on Thu, 2016-06-16 04:48.

Interestingly, with REFERENCE PARTITIONING, you can create a LOCAL UNIQUE index without having columns from the table partition key.

create table parent (f2 number, f3 number primary key)
partition by hash (f2)
partitions 4;

create table child (f3 number not null, constraint fk_p foreign key (f3) references parent (f3))
partition by reference;
create unique index f3_local on child (f3) local;

So we have a child table partitioned based on parition scheme of parent (hash on f2), yet we can have a local unique index on f3. If you run;

select * from user_ind_partitions where index_name = 'F3_LOCAL'; -- 4 rows will be returned.

I have not proved this, but I suspect when you INSERT into CHILD, it knows what index partition to look into when checking for uniqueness, therefore there it would not scan all 4 index partitions.

»