In the part1 we only really looked at the global index, so let’s
take a look at the local indexes example. Using the same partitioned table as
before
rajesh@PDB1> create
table t
2
INDEXING OFF
3
partition by list(x)
4 (
5
partition p1 values (1) ,
6
partition p2 values (2) ,
7
partition p3 values (3) INDEXING ON
8 )
9 as
10
select object_id,object_name,object_type,
11
owner,status,1 as x from all_objects a
12
union all
13
select object_id,object_name,object_type,
14
owner,status,2 as x from all_objects a
15
union all
16
select object_id,object_name,object_type,owner,
17
CASE when rownum <=100 then 'OPEN' else 'CLOSED' end status,
18 3
as x from all_objects a ;
Table created.
This time we will create a local partial index.
rajesh@PDB1> create
index t_ind on t(status) local indexing partial;
Index created.
rajesh@PDB1>
If we look at the details of the resultant local index:
rajesh@PDB1> select
table_name,partition_name,num_rows
2 from
user_tab_partitions
3
where table_name ='T';
TABLE_NAME PARTITION_NAME NUM_ROWS
--------------------
-------------------- ----------
T P1 89731
T P2 89731
T P3 89731
3 rows selected.
rajesh@PDB1> select
index_name,partition_name,num_rows,leaf_blocks,status
2 from
user_ind_partitions
3
where index_name ='T_IND';
INDEX_NAME PARTITION_NAME NUM_ROWS LEAF_BLOCKS STATUS
--------------------
-------------------- ---------- ----------- --------
T_IND P1 0 0 UNUSABLE
T_IND P2 0 0 UNUSABLE
T_IND P3 89731 225 USABLE
3 rows selected.
rajesh@PDB1>
We can see that for those table partitions with INDEXING OFF
the associated index local indexes have simply been made UNUSABLE. Since unusable
index consume no storage there is effectively no corresponding index segments
for these index partitions.
For the one and only table partition P3 with INDEXING ON, its
associated local index has been created as normal. So its end result is very similar
to pervious global index example. There are scenarios where partial index are
not permitted.
1) Creation of
partial unique index.
rajesh@PDB1> create unique index t_ind2
2 on t(x,object_id)
3 indexing partial ;
on t(x,object_id)
*
ERROR at line 2:
ORA-14226: unique index may not be PARTIAL
rajesh@PDB1> create unique index t_ind2
2 on t(x,object_id) local
3 indexing partial ;
on t(x,object_id) local
*
ERROR at line 2:
ORA-14226: unique index may not be PARTIAL
2) Using partial non-unique to
force primary key or unique constraint.
rajesh@PDB1> alter table t
2 add constraint t_pk
3 primary key(x,object_id)
4 using index ( create unique index t_pk
5 on t(x,object_id) indexing partial ) ;
alter table t
*
ERROR at line 1:
ORA-14226: unique index may not be PARTIAL
rajesh@PDB1>
No comments:
Post a Comment