Home
/
Blog
/
3 Restrictions on Indexing and Partitioning

3 Restrictions on Indexing and Partitioning

There are three limitations to indexing and partitioning.

A special index cannot be regional or non-prefixed.
A transnational non-prefixed index is not possible.
A bitmap index cannot be transnational.

Why these restrictions? The reason could be that they are there to stop users from doing something mindless. Let’s see more about this in detail.

How Does It Work?
Can We Have a Local Non-Prefixed Unique Index?
What’s The Reason for Not Having a Global Non-Prefixed Index?
What’s The Reason For Not Having a Global Partitioned Bitmap Index?
Conclusion

How Does It Work?

Below is the table utilized for all examples:
CREATE TABLE EMPLOYEE (EMPNO NUMBER(4) CONSTRAINT PK_EMPNO PRIMARY KEY, EMPNAME VARCHAR2(20), JOB VARCHAR2(12), MGRNO NUMBER(5), JOINDATE DATE, SALARY NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(3) ) PARTITION BY HASH (EMPNO) PARTITIONS 5;

Now, the standard EMPLOYEE table, with a partitioning clause, is linked. It is of course an artificial example. Maybe we are hiring so many employees together that a non-partitioned table has difficulties with buffer friction that can be decrypted only with hash partitioning.

Can We Have a Local Non-Prefixed Unique Index?

Now, a local non-unique index is not an issue, but distinctive is impossible:

orclz> create index employeenamei on employee(employeename) local;

Index created.

orclz> drop index employeenamei;

Index dropped.

orclz> create unique index employeenamei on employee(employeename) local;
create unique index employeenamei on employee(employeename) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
As we can see, we cannot fix the issue by dividing the index from the constraint (which is invariably the right approach):

orclz> create index employeenamei on employee(employeename) local;

Index created.

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

So what is the problem? Indeed, it is not a technical restriction. But if it were feasible, assess the importance of execution. When inserting a row, a unique index (or a non-unique index implementing a unique condition) must be explored to notice if the key-value already lives. For a small little five-partition table, that would suggest five index searches: one of each local index partition. Well, OK. But what if the table was content partitioned into numerous partitions? Then every insert would have to create numerous index lookups. This would be extremely slow. By limiting unique indexes to transnational or local prefixed, Oracle is confirming that we cannot develop such a horrific case.

What’s the Reason for Not Having a Global Non-Prefixed Index?

In the above example, maybe the user wants a global index on deptno, partitioned by mgr. But it is not possible:
orclz> create index departmentnoi on employee(departmentno) global partition by hash(mgr) partitions 5; create index departmentnoi on employee(departmentno) global partition by hash(mgr) partitions 5

ERROR at line 1:
ORA-14038: GLOBAL partitioned index must be prefixed

This index, if it were viable, might help a query with an equivalency predicate on mgr and a scope predicate on departmentno. But exactly the identical outcome would be accomplished by utilizing a global nonpartitioned connected index on mgr and departmentno. If the query had only departmentno in the predicate, it would have to scour each partition of the global partitioned index, a method that would be just about similar to a drop the scan of the nonpartitioned index. And of course, the linked index could be globally partitioned – on mgr. In other words, a global non-prefixed index would deliver nothing that is not general in other methods.

What’s The Reason For Not Having a Global Partitioned Bitmap Index?

It is important to note that global indexes must be prefixed. Taking that in mind, the query ought to be re-phrased: why would a user ever desire a prefixed partitioned bitmap index? For example:
orclz> orclz> create bitmap index bitmi on employee(departmentno) global partition by hash(departmentno) partitions 5; create bitmap index bitmi on employee(departmentno) global partition by hash(departmentno) partitions 5 * ERROR at line 1: ORA-25113: GLOBAL may not be used with a bitmap index

In this, users would not obtain the expected advantage of decreasing contention for simultaneous inserts, because of the requirement to seal whole blocks of a bitmap index (and thus scopes of rows) when accomplishing DML. Scope partitioning a bitmap index would be absurd, because of the necessity to utilize parity predicates to obtain actual value from bitmaps. Even with hash partitions, users would not gain any advantage from partition pruning, because utilizing equivalency predicates on a bitmap index in development prunes the index already: that is what a bitmap index is for. So it appears that a globally partitioned bitmap index would give no advantage while measuring the difficulty and concerns of index supervision.

A regional index on a partitioned table is formed where the index is partitioned identically as the base table. That is, the regional index brings the partitioning technique of the table. This is called equi-partitioning.
For regional indexes, the index keys will direct only to the rows held in the one-based table partition. A local index is constructed by defining the LOCAL trait and can be completed as UNIQUE or NON-UNIQUE.
The local index is either partitioned exactly or has the identical partition key because the local indexes are implicitly held, and can deliver increased availability.
As the Oracle database confirms that the index is synchronized with their related table partitions, it observes that the database implicitly supports the index partition whenever any supervision operation is executed on the base tables
For instance, when partitions are inserted, dropped, or merged.

Conclusion

A partitioned index is just an index split into numerous segments. By splitting an index into numerous actual pieces, users are getting much shorter segments (quicker), and they may divide the segments onto various disk drives (reducing I/O conflict).

If users are executing supervision on data in a typical partition that requires an index reorganization. For instance, assume a table with 12 compartments, each corresponding to a typical month of the year. Users might like to edit or delete multiple rows that are precise to one month of the year. This move could give a fragmented index, which might need that users execute an index reorganization. With a partitioned index, users can rebuild just the index partition that coordinates to the data partition where the modifications were completed, which could maintain a considerable amount of time corresponding to reorganizing a whole, nonpartitioned index.