Thanks for the question, FrantiĊĦek.
Asked: December 02, 2020 - 3:13 pm UTC
Last updated: December 04, 2020 - 4:13 am UTC
Version: Oracle 12.2
Viewed 1000+ times
You Asked
drop table p_table;
select * from PRODUCT_COMPONENT_VERSION;
PROMPT create partitioned table P_TABLE
CREATE TABLE p_table
(
PERIOD DATE NOT NULL,
ID NUMBER(10) NOT NULL
)
PARTITION BY RANGE (PERIOD)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P_0 VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
LOGGING
NOCOMPRESS
)
;
PROMPT create UC with "implicit" local index ( with "using index local" )
ALTER TABLE p_table ADD (
CONSTRAINT PTAB_PER_ID_UK
UNIQUE (period, id)
USING INDEX LOCAL
ENABLE VALIDATE)
;
select index_name,uniqueness,partitioned from all_indexes where index_name = 'PTAB_PER_ID_UK'; -- index is local
PROMPT after disable constraint, index will be dropped, this is expected/required behavior
alter table P_TABLE disable constraint PTAB_PER_ID_UK; -- index will be dropped
select index_name,uniqueness,partitioned from all_indexes where index_name = 'PTAB_PER_ID_UK';
PROMPT enable constraint
alter table P_TABLE enable constraint PTAB_PER_ID_UK; -- index will be created, bat isn't local
PROMPT after enable constraint, index is created, but is not local
select index_name,uniqueness,partitioned from all_indexes where index_name = 'PTAB_PER_ID_UK';
PROMPT from my point of view, this is not expected behivor. Is it bug or exist some session/instance configuration or "something" for choosing expecting "right" type of implicit index?
PROMPT yes, I can enable constraint with explicit clause "using index local"
PROMPT yes, I can create separate unique local index
and Connor said...
This is by design, because a feature we also support is using any available index to implement a constraint.
For example, you might have the following indexes on a non-partitioned table
- IX1 on column x,y
- IX2 on column x,y,z
- IX3 on column y,x
- IX4 invisible on columns x,y with compression on column x
Any of those indexes could be used to implement a primary key on (x,y), hence we keep a *separation* between the constraint and the index rather than treating them "as one".
Hence in your partitioned case, there is nothing on the *constraint* that says "Hey, an index to support me should be local". That property belongs solely to the index and you get to nominate it when you enable the constraint.
For the kind of flexibility you are after, you could do something like:
- create index as local (not unique)
- add constraint using index
When you want to disable the constraint you would do:
- alter table ... disable constraint KEEP INDEX;
- alter index UNUSABLE for relevant partitions
Then do your data maintenance etc
- alter index rebuild for relevant partitions
- alter table ... enable using index
Is this answer out of date? If it is, please let us know via a Comment