@yockee
Question: Based on that article, so I am thinking that those 2 indexes are actually the same. Am I wrong? But I think , the A<n>_IX and S<n>$_IX are 2 different entities.
Answer: they are 2 different indexes.
Question: Should I move both the A<n>_IX and S<n>$_IX ?
Answer: yes
Example:
--------------------------------------------------------------------------------------------------------
--REBUILD INDEXES
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
--TABLE STREETS--
--------------------------------------------------------------------------------------------------------
ALTER INDEX A241_IX1 REBUILD TABLESPACE GIS_BINDEX ONLINE PARALLEL 4; --index_type=DOMAIN
/*
Error report -
ORA-29871: invalid alter option for a domain index
29871. 00000 - "invalid alter option for a domain index"
*Cause: The user specified an invalid option for altering a domain index.
*Action: Reissue the alter statement with a valid option.
*/
--GENERATE THE INDEX SQL DDL THEN DROP THE INDEX AND TRY TO RECREATE ON ANOTHER TABLESPACE!!!
DROP INDEX "GIS"."A241_IX1";
--Index "GIS"."A241_IX1" dropped.
CREATE INDEX "GIS"."A241_IX1" ON "GIS"."STREETS" ("SHAPE")
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=6000:0:0 ST_SRID=300003')
TABLESPACE "GIS_BINDEX";
/*
Error report -
ORA-29850: invalid option for creation of domain indexes
29850. 00000 - "invalid option for creation of domain indexes"
*Cause: The user specified an invalid option like ASC, DESC, SORT or a
parallel clause, partitioning clause or physical attributes clause.
*Action: Choose one of the valid clauses for creation of domain indexes.
*/
DROP INDEX "GIS"."A241_IX1";
--add the tablespace as parameter
CREATE INDEX "GIS"."A241_IX1" ON "GIS"."STREETS" ("SHAPE")
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=6000:0:0 ST_SRID=300003 TABLESPACE=GIS_BINDEX');
/*
Error report -
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-02216: tablespace name expected
ORA-06512: at "SYS.DBMS_SQL", line 1199
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1425
29855. 00000 - "error occurred in the execution of ODCIINDEXCREATE routine"
*Cause: Failed to successfully execute the ODCIIndexCreate routine.
*Action: Check to see if the routine has been coded correctly.
*/
DROP INDEX "GIS"."A241_IX1";
--add the tablespace as parameter
CREATE INDEX "GIS"."A241_IX1" ON "GIS"."STREETS" ("SHAPE")
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=6000:0:0 ST_SRID=300003 TABLESPACE GIS_BINDEX');
--Index "GIS"."A241_IX1" created. --Works!!! but do not think it uses the tablespace parameter !!!
DROP INDEX "GIS"."A241_IX1";
CREATE INDEX "GIS"."A241_IX1" ON "GIS"."STREETS" ("SHAPE")
INDEXTYPE IS "SDE"."ST_SPATIAL_INDEX" PARAMETERS ('ST_GRIDS=6000:0:0 ST_SRID=300003');
--Index "GIS"."A241_IX1" created.
--CONCLUSION: CANNOT MOVE A DOMAIN INDEX !!!
--Building Domain Indexes
https://docs.oracle.com/en/database/oracle/oracle-database/19/addci/building-domain-indexes.html#GUI...
--------------------------------------------------------------------------------------------------------
ALTER INDEX R290_SDE_ROWID_UK REBUILD TABLESPACE GIS_BINDEX ONLINE PARALLEL 4; --index_type=normal
--Index R290_SDE_ROWID_UK altered.
--------------------------------------------------------------------------------------------------------
--TABLE A290--
--------------------------------------------------------------------------------------------------------
ALTER INDEX A290_PK REBUILD TABLESPACE GIS_AINDEX ONLINE PARALLEL 4; --index_type=normal
--Index A290_PK altered.
ALTER INDEX A241_IX1_A REBUILD TABLESPACE GIS_AINDEX ONLINE PARALLEL 4; --index_type=DOMAIN
--CONCLUSION: CANNOT MOVE A DOMAIN INDEX !!! SEE EXAMPLE ABOVE WITH DETAILS !!!
ALTER INDEX A290_STATEID_IX1 REBUILD TABLESPACE GIS_AINDEX ONLINE PARALLEL 4; --index_type=normal
--Index A290_STATEID_IX1 altered.
--TABLE D290--
ALTER INDEX D290_PK REBUILD TABLESPACE GIS_DINDEX ONLINE PARALLEL 4; --index_type=normal
--Index D290_PK altered.
ALTER INDEX D290_IDX1 REBUILD TABLESPACE GIS_DINDEX ONLINE PARALLEL 4; --index_type=normal
--Index D290_IDX1 altered.
--TABLE S349_IDX$--
ALTER INDEX S349$_IX1 REBUILD TABLESPACE GIS_SINDEX ONLINE PARALLEL 4; --index_type=IOT
/*
Error report -
ORA-28650: Primary index on an IOT cannot be rebuilt
28650. 00000 - "Primary index on an IOT cannot be rebuilt"
*Cause: An attempt is made to issue alter index rebuild on IOT-TOP
*Action: Use Alter table MOVE to reorganize the table(IOT)
*/
ALTER TABLE S349_IDX$ MOVE TABLESPACE GIS_SINDEX PARALLEL 4;
--Table S349_IDX$ altered.
ALTER INDEX S349$_IX2 REBUILD TABLESPACE GIS_SINDEX ONLINE PARALLEL 4; --index_type=normal
--Index S349$_IX2 altered.
I hope this clarifies.
| Marcelo Marques | Principal Product Engineer | Esri |
| Cloud & Database Administrator | OCP - Oracle Certified Professional |
I work with Enterprise Geodatabases since 1997.
“ I do not fear computers. I fear the lack of them." Isaac Isimov