Unique Index
A reader, October 08, 2018 - 1:06 pm UTC
DROP TABLE test1;
CREATE TABLE test1
(
id NUMBER
, start_date TIMESTAMP (0)
, num NUMBER
)
PARTITION BY RANGE (start_date)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
SUBPARTITION BY HASH (num)
SUBPARTITION TEMPLATE (SUBPARTITION s1 , SUBPARTITION s2 )
(PARTITION x VALUES LESS THAN (TIMESTAMP ' 2018-01-01 00:00:00')
(SUBPARTITION x1 ))
NOCACHE
NOPARALLEL
MONITORING;
INSERT INTO test1
VALUES (1, SYSDATE, 2);
INSERT INTO test1
VALUES (1, SYSDATE + 400, 3);
INSERT INTO test1
VALUES (1, SYSDATE + 800, 4);
COMMIT;
SELECT *
FROM user_tab_subpartitions
WHERE table_name = 'TEST1';
DROP INDEX ix_2;
CREATE UNIQUE INDEX ix_2
ON test1 (start_date
, num)
LOCAL
UNUSABLE;
SELECT *
FROM user_ind_subpartitions
WHERE index_name = 'IX_2';
ALTER INDEX ix_2
REBUILD SUBPARTITION sys_subp1847160;
EXEC dbms_stats.gather_table_stats (tabname=>'TEST1', ownname => USER, method_opt => 'FOR ALL COLUMNS', GRANULARITY => 'SUBPARTITION');
SELECT *
FROM user_tab_subpartitions
WHERE table_name = 'TEST1';
DELETE FROM test1 SUBPARTITION (sys_subp1847167);
ora-01502: INDEX '.IX_2' OR PARTITION OF such INDEX IS IN UNUSABLE state
SELECT * FROM v$version;
oracle DATABASE 12C ENTERPRISE EDITION RELEASE 12.1.0.1.0 - 64bit production 0
pl/SQL RELEASE 12.1.0.1.0 - production 0
core 12.1.0.1.0 production 0
tns FOR linux: VERSION 12.1.0.1.0 - production 0
nlsrtl VERSION 12.1.0.1.0 - production 0
Continuation ...
A reader, October 08, 2018 - 6:48 pm UTC
Sorry I am using a partitioned table with a Unique index ... with non-unique index this works good ... I require it to work with a Unique index ... Please any help would be great!!
October 10, 2018 - 1:40 am UTC
Then you'll have a problem - uniqueness is something that cannot be avoided with 'skip_unusable_indexes'.
So heading back to:
"I was thinking is there a way in Oracle 12c + wherein we can delete rows from a partition in which the local index partition is UN-USABLE."
Then (assuming the partition key is in the unique index) you can do:
delete from t
where rowid in
( select rid
from
( select rowid rid,
row_number() over ( partition by object_name order by null ) as seq
from t partition ( p1 )
)
where seq > 1
)
and repeat for each partition
Did not works for me ...
A reader, October 15, 2018 - 1:53 pm UTC
DROP TABLE test1;
CREATE TABLE test1
(
id NUMBER
, start_date TIMESTAMP (0)
, num NUMBER
)
PARTITION BY RANGE (start_date)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
SUBPARTITION BY HASH (num)
SUBPARTITION TEMPLATE (SUBPARTITION s1 , SUBPARTITION s2 )
(PARTITION x VALUES LESS THAN (TIMESTAMP ' 2018-01-01 00:00:00')
(SUBPARTITION x1 ))
NOCACHE
NOPARALLEL
MONITORING;
INSERT INTO test1
VALUES (1, SYSDATE, 2);
INSERT INTO test1
VALUES (1, SYSDATE, 2);
INSERT INTO test1
VALUES (1, SYSDATE, 2);
INSERT INTO test1
VALUES (1, SYSDATE, 2);
INSERT INTO test1
VALUES (1, SYSDATE + 400, 3);
INSERT INTO test1
VALUES (1, SYSDATE + 800, 4);
COMMIT;
SELECT *
FROM user_tab_subpartitions
WHERE table_name = 'TEST1';
DROP INDEX ix_2;
CREATE UNIQUE INDEX ix_2
ON test1 (start_date
, num)
LOCAL
UNUSABLE;
SELECT *
FROM user_ind_subpartitions
WHERE index_name = 'IX_2';
ALTER INDEX ix_2
REBUILD SUBPARTITION sys_subp1847160;
EXEC dbms_stats.gather_table_stats (tabname=>'TEST1', ownname => USER, method_opt => 'FOR ALL COLUMNS', GRANULARITY => 'SUBPARTITION');
SELECT *
FROM user_tab_subpartitions
WHERE table_name = 'TEST1';
select * from test1 SUBPARTITION (SYS_SUBP1847651);
DELETE FROM test1 SUBPARTITION (SYS_SUBP1847651);
delete from test1
where rowid in
( select rid
from
( select rowid rid,
row_number() over ( partition by start_date
, num order by null ) as seq
from test1 partition ( SYS_P1847653 )
)
where seq > 1
)
Same index error
Did I miss something ...
October 16, 2018 - 2:14 am UTC
Sorry, I should have been more clear. I wasn't saying you could leave the index (or constraint) in place.
I was simply saying that if the partitioning key is part of the columns that are being used to define uniqueness, then you could iterate a partition at time to remove duplicates, because there is no chance of duplicates occurring cross-partition.