There are three limitations to indexing and partitioning.
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.
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.
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.
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.
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 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.
About the Author
ByteScout Team of Writers
ByteScout has a team of professional writers proficient in different technical topics. We select the best writers to cover interesting and trending topics for our readers. We love developers and we hope our articles help you learn about programming and programmers.
The purpose of the query optimizer is to discover the query performance and execution plan that calculates the result precisely. This article is all about...
Successful programmers love reading different coding books. Reading is an essential part of the programmers’ life. Reading and practicing are two signs of an exceptional...