ArcSDE 10.0 - Partitioned IOT issue

[Surendrakumar_Thirumappan]

Greetings,

We are experiencing an issue with ArcSDE 10.0 SP5 and Oracle 10G.  We have some large spatially-enabled tables that are partitioned.  The performance for adding a partition to the largest table was very bad, it was taking 4 hours to rebuild the associated IOT.  We ran the following command to create a new DBTUNE keyword and used this new keyword when registering the partitioned tables.

sdedbtune -o insert -k PART_IOT -P ST_INDEX_PARTITION_LOCAL -v TRUE  -i sde:oracle10g:xxxxxx -u sde -p xxxxxx

This worked well and ArcSDE created one IOT per partition for each of the tables.  However, we're running into a problem when we try to add a partition to any of the tables.  The following steps are performed just fine:
1. Table partition created
2. LOB partition created
3. Index partition created (for each local index)

However, we then get the following error:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1454
<errors related to how I called it, either via alter table...add partition or via my PL/SQL>

It seems that the problem is that the IOT related to the new partition is not created, but the ESRI code is trying to insert into the (non-existent) IOT.  I verified this by performing the following steps, which succeeded.
1. Created the IOT manually
2. Created the partition

This is obviously not a good solution because the SDE reference tables aren't updated, but it did confirm the problem.  For now, I've gotten around my immediate problem by putting the tables into load-only io mode, adding the partitions and switching them back to normal-io mode. 

Can anyone advise how to create a new partition without the ESRI code failing?  I'm wondering if I created the DBTUNE data incorrectly.  I can't update the DEFAULT DBTUNE data because that causes problems with other datasets.

Thanks!

[VinceAngelo]

Partition management is better done with SQL than with DBTUNE files.  The main
problem you have to avoid is splitting an active partition, which invalidates the
entire index.  Partitions are exotic enough that you should take proactive control
over table and index creation (spatial and otherwise), only registering the result
after all the necessary tweaks are twiddled.

- V

[Surendrakumar_Thirumappan]

I can certainly make a change to create the new IOT before creating a new partition.  However, when I register the partitioned table, the SDE process is creating a # for it and creating the IOT's with the number embedded in the table name.  How would I get around this?

Also, the data loads will be ongoing so I will need to continue to add partitions.  It isn't clear to me how I would accomplish this if I need to set everything up before registering the table.

Here is an example of how I'm doing this:

create table part_test
(OBJECTID    INTEGER
,MY_DATE     DATE
,SHAPE       ST_GEOMETRY
)
partition by range (my_date)
(partition P201301 values less than (to_date('02/01/2013','mm/dd/yyyy'))
,partition P201302 values less than (to_date('03/01/2013','mm/dd/yyyy'))
);

create bitmap index part_test_idx1 on part_test
(my_date)
local;


insert into part_test
(select objectid, eff_dt, shape
 from   other_table
 where  rownum <= 100
);

commit;

 sdelayer -o register -l part_test,shape -e p -u XXXXXX -p XXXXXX -i sde:oracle10g:XXXXXX -C objectid,SDE -t ST_GEOMETRY -E empty -k PART_IOT

This properly creates the ST_SPATIAL_INDEX as partitioned.  It also creates the unique index on the OBJECTID field.  I could create those manually, but the ST_SPATIAL_INDEX has a particular number embedded in it (the same number as the IOT's).  Also, I had issues using some of the other sde commands when I created the OBJECTID unique key via SQL (they would fail because the index already existed). 

In addition, the following IOT's are created:
S7193P201301
S7193P201302

Records for the IOT's are put in the SDE.ST_PARTITION_INDEX table, which doesn't happen if I manually create the IOT's.

Here's what happens when I try to add a new partition to the main table:

alter table part_test 
add partition P201303
values less than (to_date('04/01/2013','mm/dd/yyyy'));

Error:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1454

The code successfully took care of everything except creating a new IOT.  Are you suggesting that I manually create it and ignore the fact that the SDE reference table is missing a record?  Is there something I should be doing differently when I create/register the object?

Thanks for your help.

[VinceAngelo]

I think your problem is the open-ended date ranges.  This causes index invalidation, which
causes real-time loading to crash and burn.  You need to manage your range constraints
explicitly, adding new partitions before they're needed (usually just-in-time).

If you create the LOCAL spatial index explictly during creation (before registration) then
the SDE-named index will not be created at registration, and then you can drop the index
before population, then add it back in after you're done with initial loading.

I've never had issues with the registered rowid index being created in advance, so you
should review your index procedure, to make sure it's compatible with ArcSDE.

- V

[Surendrakumar_Thirumappan]

Thanks for the advice.  I made a new test table and tried to create the spatial index myself:

CREATE INDEX PART_TEST2_SPATIAL_INDEX ON PART_TEST2
(SHAPE)
INDEXTYPE IS SDE.ST_SPATIAL_INDEX
  LOCAL ;

It gave me an error:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20083: Parameter ST_SRID  does not exist in ST_SPATIAL_REFERENCES table.
ORA-06512: at "SDE.SPX_UTIL", line 938
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1291

I am creating the partitions on the data table before I try to use them, but they're failing.

I'm going to try setting the ST_INDEX_PARTITION_LOCAL value in the DEFAULTS DBTUNE set instead of separately.  I'm thinking the ESRI software may not be consistent in where it checks for this value.  The documentation does indicate that it should be in the DEFAULTS set, perhaps there is a reason for that.

[VinceAngelo]

It's been years and years since I last played with partitioning, but your index
creation syntax looks wrong.  I was doing SDO_GEOMETRY and ST_GEOMETRY
side by side, so I used the SDO documentation for partitioned index creation
as the template for ST_GEOMETRY syntax.  I never once touched the DBTUNE.

You might be better served by contacting Tech Support.

- V

[Surendrakumar_Thirumappan · Accepted solution]

We reached out to ESRI technical support and they determined it is a bug that was introduced with ArcSDE 10.0 SP5.  A bug was logged (see below)
[INDENT][#NIM091900  After applying SP5 for ArcSDE 10, adding a new partition on an ST_Geometry table that contains a spatial index causes an error "ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine". ]

For now, a valid workaround for this issue would be:

Drop the spatial index, add the new partition, and then re-create the spatial index.
[/INDENT]


We discovered this issue when we tried to create the first partitioned spatial indexes.  We will continue using non-partitioned spatial indexes until a fix is available or we upgrade to 10.1, whichever comes first.