topic Re: St_Geometry spatial indexes are not creating S###$_IX2 in Data Management Questions

St_Geometry spatial indexes are not creating S###$_IX2

MatjazHabic — Wed, 12 Jun 2013 11:31:31 GMT

We have following configuration Oracle on RHEL linux:

**********************ArcSDE install summary:**********************
ArcSDE 10.1 Oracle_11g (64-bit) Install Location:
/home/sde/sdeexe101

Service Pack / Patch / Hot Fix Found:

SP-ID: ArcSDE 10.1 Service Pack 1

$ uname -a
Linux xxxxxx 2.6.18-274.el5 #1 SMP Fri Jul 8 17:36:59 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux

As described in "NIM084235 - St_Geometry spatial indexes are not creating S###$_IX2 indexes on the IOT SP_ID column" for "ArcGIS 10.1 SP1 for (Desktop, Engine, Server) PostgreSQL and Parcel Editing Performance Patch" (http://support.esri.com/en/downloads/patches-servicepacks/view/productid/66/metaid/1941) there is missing index on SP_ID column.

Where can be found patch for Oracle ?

regards
Matjaz

Re: St_Geometry spatial indexes are not creating S###$_IX2

VinceAngelo — Wed, 12 Jun 2013 11:59:40 GMT

If you're looking for patches, you really should be talking to Tech Support.

- V

Re: St_Geometry spatial indexes are not creating S###$_IX2

Anonymous User — Fri, 10 Dec 2021 21:37:44 GMT

I encountered this problem this morning and came up with this script, hopes it helps someone!
// Lars Nordvall Triona AB

/* Use this script to create S###_IX2 indexes on spatialindex tables in ArcSDE 10.1 */
/* Workaround for NIM084235 http://support.esri.com/en/bugs/nimbus/TklNMDg0MjM1 */
set serveroutput ON;
declare 
 cnt number(38);
 sqlStr varchar2(255);
 spatialIndexTable varchar2(32);
 indexName varchar2(32);
 type cref is ref cursor;
 c1 cref;
begin
 cnt := 0;
 sqlstr := 'select table_name from user_tables where table_name like ''S%_IDX%'' order by 1';
 DBMS_OUTPUT.PUT_LINE(sqlStr );
 open c1 for sqlstr;
 LOOP
  fetch c1 into spatialIndexTable;
  EXIT WHEN c1%NOTFOUND;
  cnt := cnt + 1;
  indexName := substr(spatialIndexTable,0,length(spatialIndexTable)-1) || '2';
  sqlStr := 'create index ' || indexName || ' on ' || spatialIndexTable || '(SP_ID)';
  DBMS_OUTPUT.PUT_LINE( sqlstr );
  EXECUTE IMMEDIATE sqlStr; 
 end LOOP;
 close c1;

 DBMS_OUTPUT.PUT_LINE('Index creation finished (' || to_char(cnt) || ' indexes created)');
end;
/

Re: St_Geometry spatial indexes are not creating S###$_IX2

ForrestJones — Mon, 23 Sep 2013 16:06:57 GMT

Hi Matjaz,

The fix/patch for this issue is available at the link you sent (http://support.esri.com/en/downloads...66/metaid/1941).

To upgrade/patch your database on linux, just install the windows setup on your client, connect to your linux database using a direct connection and run upgrade.

Re: St_Geometry spatial indexes are not creating S###$_IX2

Anonymous User — Fri, 04 Oct 2013 12:18:54 GMT

I tried the patch and it did not have any effect on my Oracle installation.
It's for PostGIS?

Re: St_Geometry spatial indexes are not creating S###$_IX2

MarcoBoeringa — Fri, 04 Oct 2013 12:31:49 GMT

I tried the patch and it did not have any effect on my Oracle installation.
It's for PostGIS?

Looking at the installed files, even though PostGreSQL is specifically mentioned in the title of the patch, this patch also seems relevant for Oracle, as files for the other DBMSs are installed as well. Fixes for the other DBMS are related to Parcel Editing.

Do mind the caveat though (see the patch link😞

"If you installed the ST_Geometry type in your Oracle or PostgreSQL database, upgrade the type after you install the ArcGIS 10.1 SP1 for (Desktop, Engine, Server) PostgreSQL and Parcel Editing Performance Patch. See the topic specific to your DBMS for instructions: ..."