[Bugs] arcsde10/10.1 Delete non-version data slow problem

Environment:

Middleware: arcsde10/10.1

Database: Oracle

Phenomenon: Users in the non-version editing process, if the amount of data is very large, in the use of the desktop to delete a few pieces of data, the speed is very slow.

Speaking slowly, everyone will think is not the problem of spatial index, will also be accustomed to the reconstruction of spatial index, or analysis of operations, but after doing these, the effect is still not obvious, then the cause of the problem is where.

----------------------------------------------------------------------------------

Copyright, the article is allowed to reprint, but must be linked to the source address, otherwise held legal responsibility!

blog:http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

First, let's explain the spatial index of ARCSDE for Oracle.

About ARCSDE performance Optimization series of the knowledge of the index can be referred to: http://blog.csdn.net/linghe301/article/details/6269847

An element class with spatial indexes created using St_geometry storage creates another table in the Oracle database. The name of this spatial index table is s<n>_idx$, where <n> is the geometry index value of the table. SDE can be queried by query. St_geometry_columns the table to get the value. This spatial index table is created as an Oracle index organization table (IOT). When viewed through Enterprise Manager, the spatial index of the St_geometry property is displayed as A<n>_ix1. The value <n> represents the layer_id value stored in the LAYERS table.


Two other indexes were also created in the s<n>_idx$ table: S<n>$_ix1 and s<n>$_ix2. You can specify how these indexes are stored in the DBMS by changing the s_storage parameters in the dbtune configuration keyword that you specify when you create the feature class.

From the above description, we can conclude that the ARCSDE factor class of efficient browsing is directly due to the spatial index, but the spatial index is also indexed, which means that the so-called S table is the spatial index table, but the spatial index table and the index is mentioned above s<n>$_ix1 and S<n >$_ix2.

Let's take ArcSDE10.1 as an example to see the relevant information

Let's take a look at the structure of the spatial index table

sql> desc s45_idx$
 name is                                      null? type
 ------------------------------------------------------------------- ---GX NOT null number (a) GY NOT null number (a) MINX NOT null number (a)
 miny                                      not Null number () MAXX NOT NULL number (a) Maxy NOT null number (a)                                      sp_id not                                     null ROWID

And then based on this spatial index table, we look at the index of this index table is

Sql> Select Index_name,index_type from user_indexes where table_name= ' s45_idx$ ';

Index_name                     index_type
---------------------------------------------------------
s45$_ix1                       Iot-top

We see that the index of arcsde10/10.1 's spatial index has only one table, not the two tables mentioned above, and the type of the index table is IoT, and IoT index is usually to create primary key index

----------------------------------------------------------------------------------

Copyright, the article is allowed to reprint, but must be linked to the source address, otherwise held legal responsibility!

blog:http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

Let's look at the index of the ArcSDE10.2 spatial index

Sql> Select Index_name,index_type from user_indexes where table_name= ' s4_idx$ ';

Index_name                     index_type
---------------------------------------------------------
s4$_ix1                        IOT- Top
s4$_ix2                        NORMAL

Here we see that there are two index objects created, one is IoT, one is normal.

Sql> Select Dbms_metadata.get_ddl (' INDEX ', ' s4$_ix1 ', ' SDE ') from dual; Dbms_metadata. GET_DDL (' INDEX ', ' s4$_ix1 ', ' SDE ')------------------------------------------------------------------------------ -CREATE UNIQUE INDEX "SDE". S4$_ix1 "on" SDE "."  s4_idx$ "(" GX "," GY "," MAXX "," Maxy "," MINX "," Miny "," sp_id ") PCTFREE 0 Initrans 4 Maxtrans 255 STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist GROUPS 1 buffer_pool DEFAULT FLASH _cache default Cell_flash_cache default) tablespace "SDE" sql> Select Dbms_metadata.get_ddl (' INDEX ', ' s4$_ix2 ', ' SDE ')

) from dual; Dbms_metadata. GET_DDL (' INDEX ', ' s4$_ix2 ', ' SDE ')------------------------------------------------------------------------------ --CREATE INDEX "SDE". S4$_ix2 "on" SDE "."  s4_idx$ "(" sp_id ") PCTFREE Initrans 2 Maxtrans 255 COMPUTE STATISTICS STORAGE (INITIAL 65536 NEXT 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist GROUPS 1 BUFFEr_pool default Flash_cache default Cell_flash_cache default) tablespace "SDE"
 

From these two different index types, the index of IX2 (Normal) is more selective than IX1 (IOT), and IX2 is indexed by the sp_id field of the indexed object, and is highly efficient.

Therefore, the root cause of the problem is that arcsde10/10.1 modified the source code, resulting in the creation of the normal type of spatial index index, resulting in query spatial index based on inefficient.

But the problem has been solved in ArcSDE10.2.

Solution can refer to: http://support.esri.com/en/knowledgebase/techarticles/detail/40871

Patch release: http://support.esri.com/en/downloads/patches-servicepacks/view/productid/66/metaid/1941

----------------------------------------------------------------------------------

Copyright, the article is allowed to reprint, but must be linked to the source address, otherwise held legal responsibility!

blog:http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------

Bug NIM-084235

Nimbus ID	NIM084235
Submitted	Aug 4:22 PM
Severity	Critical
Applies To	ArcGIS
Version Found	10.1
Prog Language	N/A
Server Platform	All
Client Platform
Database	Oracle
Locale	N/A
Status	Resolved
Version Fixed	10.2
SP Fixed	10.2

Synopsis

St_geometry Spatial indexes do don't create s###$_ix2 indexes on the IOT sp_id column. Additional Status Information

N/a alternate Solution

Create the index manually

Thank @liufeng for the technical guidance.

----------------------------------------------------------------------------------

Copyright, the article is allowed to reprint, but must be linked to the source address, otherwise held legal responsibility!

blog:http://blog.csdn.net/linghe301

----------------------------------------------------------------------------------