Error in SQL query

2011-01-19
2012-12-18
1 2 > >> (Page 1 of 2)
  • Sandro Costa

    Sandro Costa - 2011-01-19

    The Georaptor 2.1.4.7 version has a problem when I use the Query data tool!

    It show an Error in SQL query alert (bellow). If I go back to 2.1.2 version, georaptor works fine!

    ORA-13249 SDO_NN cannot be evaluated without using index
    ORA-06512: in "MDSYS.MD", line 1723
    ORA-06512: in "MDSYS.MDERR", line 17
    ORA-06512: in "MDSYS.PRVT_IDX", line 9

    Bellow is a copy SQL to clipboard:

    SELECT rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
      FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
               MDSYS.SDO_GEOMETRY(2003,NULL,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), ?) = 'TRUE'
       AND MDSYS.SDO_NN(t.SHAPE,
                        MDSYS.SDO_GEOMETRY(?,?,MDSYS.SDO_POINT_TYPE(?,?,NULL),NULL,NULL),
                        ?,1) = 'TRUE'
       AND SDO_NN_DISTANCE(1) < ?
    ORDER BY sdo_nn_distance(1)
    ?=585179,398920
    ?=7781676,822400
    ?=632567,385780
    ?=7812928,382700
    ?=querytype=WINDOW'
    ?=2001
    ?=NULL
    ?=603460,540403
    ?=7787600,320758
    ?=sdo_num_res=6
    ?=433,297847

     
  • John O'Toole

    John O'Toole - 2011-01-19

    Hi,

    As you can see from the SQL, the 'Identify' tool uses an SDO_NN query to determine which features are near the location where you have clicked. 
    We can try to figure out what's going wrong by populating the bind variables back into the base query and seeing what happens when run from as a direct sql statement.
    So can you run this statement in sql*plus, or in the SQLDeveloer worksheet and see if you can reproduce the ORA-13249 error. 

    SELECT rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
      FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
               MDSYS.SDO_GEOMETRY(2003,NULL,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(585179,7781676,632567,7812928)), 'querytype=WINDOW') = 'TRUE'
       AND MDSYS.SDO_NN(t.SHAPE,
                        MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(603460,7787600,NULL),NULL,NULL),
                        'sdo_num_res=6',1) = 'TRUE'
       AND SDO_NN_DISTANCE(1) < 433
    ORDER BY sdo_nn_distance(1)

    Also, can you tell me if MUB.REGIONAL is a table or a view?

    Thanks,
    John

     
  • Sandro Costa

    Sandro Costa - 2011-01-25

    Dear John,

    Thanks for your reply!

    MUB.REGIONAL is a TABLE!

    For some reason, I sent the error message from another database! Which hasn't SRID!

    The correct message is:

    SELECT rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
      FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), ?) = 'TRUE'
       AND MDSYS.SDO_NN(t.SHAPE,
                        MDSYS.SDO_GEOMETRY(?,?,MDSYS.SDO_POINT_TYPE(?,?,NULL),NULL,NULL),
                        ?,1) = 'TRUE'
       AND SDO_NN_DISTANCE(1) < ?
    ORDER BY sdo_nn_distance(1)
    ?=535707,746479
    ?=7777000,000000
    ?=684292,253521
    ?=7816000,000000
    ?=querytype=WINDOW'
    ?=2001
    ?=82301
    ?=603820,422535
    ?=7788123,239437
    ?=sdo_num_res=6
    ?=582,616151

    So, I modified your statements to:

    SELECT rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
    FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
        MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                 MDSYS.SDO_ORDINATE_ARRAY(535707,7777000,684293,7816000)), 'querytype=WINDOW') = 'TRUE'
    AND MDSYS.SDO_NN(t.SHAPE,
             MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(603820,7788123,NULL),NULL,NULL),
             'sdo_num_res=6',1) = 'TRUE'
    AND SDO_NN_DISTANCE(1) < 582
    ORDER BY sdo_nn_distance(1);

    The results were the same displayed in GeoRaptor Spatial View:

    In the SQLDeveloer worksheet:

    ORA-13249: SDO_NN cannot be evaluated without using index
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 17
    ORA-06512: at "MDSYS.PRVT_IDX", line 9
    13249. 00000 -  "%s"
    *Cause:    An internal error was encountered in the extensible spatial index
               component. The text of the message is obtained from some
               other server component.
    *Action:   Contact Oracle Support Services with the exact error text.

    In sql*plus:
    ERROR at line 1:
    ORA-13249: SDO_NN cannot be evaluated without using index
    ORA-06512: em "MDSYS.MD", line 1723
    ORA-06512: em "MDSYS.MDERR", line 17
    ORA-06512: em "MDSYS.PRVT_IDX", line 9

    For your concern,  I used the following statements to register the REGIONAL table:

    1) To insert the table into USER_SDO_GEOM_METADATA:

    INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)                                                                                                                                                                                                              
      VALUES ('REGIONAL', 'SHAPE',MDSYS.SDO_DIM_ARRAY                                                                                                                                                                                                                                        
          (MDSYS.SDO_DIM_ELEMENT('X',590000,630000,0.001),                                                                                                                                                                                                                                   
           MDSYS.SDO_DIM_ELEMENT('Y',7777000,7816000,0.001)),                                                                                                                                                                                                                                
         82301);

    2) To update the geometry column with SRID = 82301:

    UPDATE MUB.REGIONAL t SET t.shape.sdo_srid=82301;

    3) To create the spatial index:

    CREATE INDEX MUB.REGIONAL_IX1 ON MUB.REGIONAL(SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

     
  • John O'Toole

    John O'Toole - 2011-01-31

    Hi,

    I used GeoRaptor 2.1.4 against a 9.2.0.1 database today and got a similar error.  What database version are you using?

    Thanks,
    John

     
  • Sandro Costa

    Sandro Costa - 2011-01-31

    I am using  Oracle Standard Edition One 11.2. But, regardless of the database, the georaptor works fine when I run the previous versions!
    Sandro.

     
  • John O'Toole

    John O'Toole - 2011-01-31

    The format of the SDO_NN query was changed in recent releases - the SDO_NN section is now appended to the layer sql in the Layer Properties dialog.  This is done so that it honours any further filters you have added to the layer sql.  Also, it means you can configure what columns to show on the result.  Previously the SDO_NN was run against the entire table that the layer was based on which often wasn't appropriate. 
    SDO_NN can sometimes be tricky to get working properly as it doesn't always use the spatial index (as in your case). 
    It might be worth trying to add a hint to force it to use the spatial index. 

    Try adding this hint /*+ INDEX (t REGIONAL_IX1) */ to the layer sql in the Layer Properties dialog.  If this helps, then we could potentially look at always adding this hint in the layer sql.

    John

     
  • Sandro Costa

    Sandro Costa - 2011-01-31

    John,

    Sorry! I am a geologist who thinks to understand Database! Where do I put the text (/ * + INDEX (t REGIONAL_IX1) / *)?

    The layer SQL is:

    SELECT rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
      FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), ?) = 'TRUE'

    Sandro.

     
  • John O'Toole

    John O'Toole - 2011-01-31

    Sandro,

    The hint goes after the SELECT, so the first line should read:

    SELECT /*+ INDEX (t REGIONAL_IX1) */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE

    John

     
  • Sandro Costa

    Sandro Costa - 2011-02-01

    John,

    I changed the layer SQL:

    SELECT /*+ INDEX (t REGIONAL_IX1) */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
      FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), ?) = 'TRUE'

    The result:

    SQL Error: ORA-13249: SDO_NN cannot be evaluated without using index
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 17
    ORA-06512: at "MDSYS.PRVT_IDX", line 9
    13249. 00000 -  "%s"
    *Cause:    An internal error was encountered in the extensible spatial index
               component. The text of the message is obtained from some
               other server component.
    *Action:   Contact Oracle Support Services with the exact error text.

    COPY TO CLIPBOARD:

    SELECT /*+ INDEX (t REGIONAL_IX1) */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
      FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), ?) = 'TRUE'
       AND MDSYS.SDO_NN(t.SHAPE,
                        MDSYS.SDO_GEOMETRY(?,?,MDSYS.SDO_POINT_TYPE(?,?,NULL),NULL,NULL),
                        ?,1) = 'TRUE'
       AND SDO_NN_DISTANCE(1) < ?
    ORDER BY sdo_nn_distance(1)
    ?=559425,806452
    ?=7777000,000000
    ?=660574,193548
    ?=7816000,000000
    ?=querytype=WINDOW'
    ?=2001
    ?=82301
    ?=608364,516129
    ?=7798638,709677
    ?=sdo_num_res=6
    ?=533,751570

    BUT, THE STATEMENT WORKS:

    SELECT /*+ INDEX (t REGIONAL_IX1) */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
      FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(559425,7777000,660574,7816000)), 'querytype=WINDOW') = 'TRUE';

    ROWID OBJECTID               CODIGO     NOME                                                                                                
    --- --------------- ------- ----------------
    AAAVE7AAMAAAAKbAAE 5                      18         BARREIRO                                                                                            
    AAAVE7AAMAAAAKbAAD 4                      19         CENTRO SUL                                                                                          
    AAAVE7AAMAAAAKbAAF 6                      24         OESTE                                                                                               
    AAAVE7AAMAAAAKbAAI 9                      22         NOROESTE                                                                                            
    AAAVE7AAMAAAAKbAAC 3                      20         LESTE                                                                                               
    AAAVE7AAMAAAAKbAAB 2                      21         NORDESTE                                                                                            
    AAAVE7AAMAAAAKbAAG 7                      25         PAMPULHA                                                                                            
    AAAVE7AAMAAAAKbAAA 1                      23         NORTE                                                                                               
    AAAVE7AAMAAAAKbAAH 8                      26         VENDA NOVA

    (I hid the MI_STYLE and t.SHAPE columns because they are too big)

    The problem is in:

    AND MDSYS.SDO_NN(t.SHAPE,
                        MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),
                        'sdo_num_res=6',1) = 'TRUE'
       AND SDO_NN_DISTANCE(1) < 533
    ORDER BY sdo_nn_distance(1)
                                                  

    I did the following query:

    select INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS,ITYP_OWNER,ITYP_NAME,DOMIDX_STATUS,DOMIDX_OPSTATUS from user_indexes where ITYP_NAME is not null;

    INDEX_NAME   TABLE_OWNER   TABLE_NAME   STATUS   ITYP_OWNER   ITYP_NAME   DOMIDX_STATUS   DOMIDX_OPSTATUS 
    REGIONAL_IX1  MUB   REGIONAL   VALID   MDSYS   SPATIAL_INDEX   VALID   VALID          

    The "Map View" returns the attributes fine!

     
  • Sandro Costa

    Sandro Costa - 2011-02-01

    John,

    I thought the problem might be in using the locator, rather than the spatial feature! But see what the book "Pro Oracle Spatial for Oracle Database 11g" says:

    Locator

    Locator provides a core subset of spatial functionality to cater to specific applications. Specifically, it
    includes the following functionality:

    • The data model for storing spatial data using the SDO_GEOMETRY data type: This includes storing
    all types of geometries (points, lines, polygons, and so on).

    • Query and analysis using the Index Engine: This includes creating spatial indexes and querying
    using associated spatial operators such as SDO_NN. In Locator, this functionality is restricted to
    only two-dimensional data.

    • The SDO_GEOM.SDO_DISTANCE and the SDO_GEOM.VALIDATE_GEOMETRY_XXX functions: These
    functions are also part of Locator.

    I think I will use the previous version until this issue is resolved. The 2.1.2 version  satisfy me!

    Sandro.

     
  • Simon Greener

    Simon Greener - 2011-02-04

    Sandro,

    Simon Greener, chief developer here. I am looking at the problem right now for fix and release in the next version.

    SDO_NN is a bit of a pain in that it is tricky to make it use the RTree index in all cases.

    John's suggestion of using the INDEX() hint:  /*+ INDEX (t REGIONAL_IX1) */ is possibly a bit misleading as the Pro Oracle Spatial book says it should be accompanied by NO_INDEX() hints for all those attribute indexes that exist on the table. Do you have any other attribute indexes on the table?

    But even if you did, I have found that using both these hints does not resolve all possible situations - something we have to try and do in GeoRaptor.

    If you would, could you try running the following query?

    SELECT /*+ LEADING */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE 
      FROM MUB.REGIONAL t 
     WHERE SDO_FILTER(t.SHAPE, 
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), ?) = 'TRUE'
       AND ( MDSYS.SDO_NN(t.SHAPE, 
                          MDSYS.SDO_GEOMETRY(?,?,MDSYS.SDO_POINT_TYPE(?,?,NULL),NULL,NULL),
                          ?,1) = 'TRUE' 
                 AND SDO_NN_DISTANCE(1) < ?  )
     ORDER BY sdo_nn_distance(1)
    ?=559425,806452
    ?=7777000,000000
    ?=660574,193548
    ?=7816000,000000
    ?=querytype=WINDOW'
    ?=2001
    ?=82301
    ?=608364,516129
    ?=7798638,709677
    ?=sdo_batch_size=0
    ?=533,751570
    

    If this fails, could you generate the execution plan for the query for your data?

    http://download.oracle.com/docs/cd/E12151_01/doc.150/e12152/intro.htm#CHDEJJDB

    In addition, have you created any statistics for this table? (Right mouse click on your MUB.REGIONAL table and select the Statistics>Gather Statistics menu entry. Do the same for all the indexes on your table.)

    regards
    Simon

     
  • Simon Greener

    Simon Greener - 2011-02-04

    Sandro,

    If my modified query fails, could you run two more queries for me please?

    1. No Order by.

    SELECT /*+ LEADING */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE 
      FROM MUB.REGIONAL t 
     WHERE SDO_FILTER(t.SHAPE, 
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), ?) = 'TRUE'
       AND ( MDSYS.SDO_NN(t.SHAPE, 
                          MDSYS.SDO_GEOMETRY(?,?,MDSYS.SDO_POINT_TYPE(?,?,NULL),NULL,NULL),
                          ?,1) = 'TRUE' 
                 AND SDO_NN_DISTANCE(1) < ?  )
    

    2. Inner query

    select *
    from (
    SELECT /*+ LEADING */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE , SDO_NN_DISTANCE(1) as nn_distance
      FROM MUB.REGIONAL t 
     WHERE SDO_FILTER(t.SHAPE, 
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), ?) = 'TRUE'
       AND MDSYS.SDO_NN(t.SHAPE, 
                          MDSYS.SDO_GEOMETRY(?,?,MDSYS.SDO_POINT_TYPE(?,?,NULL),NULL,NULL),
                          ?,1) = 'TRUE' 
    )
    order by nn_distance;
    

    Simon

     
  • Simon Greener

    Simon Greener - 2011-02-04

    Error: Forgot predicate on query two

    select *
    from (
    SELECT /*+ LEADING */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE , SDO_NN_DISTANCE(1) as nn_distance
      FROM MUB.REGIONAL t 
     WHERE SDO_FILTER(t.SHAPE, 
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(?,?,?,?)), ?) = 'TRUE'
       AND MDSYS.SDO_NN(t.SHAPE, 
                          MDSYS.SDO_GEOMETRY(?,?,MDSYS.SDO_POINT_TYPE(?,?,NULL),NULL,NULL),
                          ?,1) = 'TRUE' 
    )
    where nn_distance < ?
    order by nn_distance;
    
     
  • Sandro Costa

    Sandro Costa - 2011-02-04

    Dear Simon,

    I'm sorry to tell you that's still not working! Thank you for your concern!

    Yes, I have gathered the statistics!

    I don't know how send to you a picture of the Execution Plan (I am trying!)! But the result was the same!

    1) Query:

    SELECT /*+ LEADING */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
      FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
              MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                 MDSYS.SDO_ORDINATE_ARRAY(559425,7777000,660575,7816000)), 'querytype=WINDOW') = 'TRUE'
      AND ( MDSYS.SDO_NN(t.SHAPE,

    MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),
                         'sdo_batch_size=0',1) = 'TRUE'
                AND SDO_NN_DISTANCE(1) < 533  )
    ORDER BY sdo_nn_distance(1);

    RESULT (WITH OR WITHOUT "ORDER BY"):

    ORA-13249: SDO_NN cannot be evaluated without using index
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 17
    ORA-06512: at "MDSYS.PRVT_IDX", line 9
    13249. 00000 -  "%s"
    *Cause: An internal error was encountered in the extensible spatial index
    component. The text of the message is obtained from some
    other server component.
    *Action: Contact Oracle Support Services with the exact error text.

    2) Inner query:

    select *
    from (
    SELECT /*+ LEADING */ rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE ,
    SDO_NN_DISTANCE(1) as nn_distance
      FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
               MDSYS.SDO_GEOMETRY(2003,82301,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(559425,7777000,660575,7816000)), 'querytype=WINDOW') = 'TRUE'
      AND MDSYS.SDO_NN(t.SHAPE,
                         MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),
                         'sdo_batch_size=0',1) = 'TRUE'
    )
    where nn_distance < 533
    order by nn_distance;

    RESULT: The Same!

    OBS: I've noticed that the X values (min and max) to MDSYS.SDO_ORDINATE_ARRAY have changed if compared to those defined for the USER_SDO_GEOM_METADATA. The values of Y remain the same!

    ?=559425,806452 (Xmin)
    ?=7777000,000000
    ?=660574,193548 (Xmax)
    ?=7816000,000000

    USER_SDO_GEOM_METADATA:

    INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
      VALUES ('REGIONAL', 'SHAPE',MDSYS.SDO_DIM_ARRAY                                                                                 
          (MDSYS.SDO_DIM_ELEMENT('X',590000,630000,0.001),                                                                            
           MDSYS.SDO_DIM_ELEMENT('Y',7777000,7816000,0.001)),                                                                         
         82301);

    I have to change comma for dot! Although, in SQL Developer preferences, I have setted the NLS for english !

    About your question "Do you have any other attribute indexes on the table?", No! I have only the "shape" as spatial index. Objectid is the primary key, so it is indexed, but not spatialy! Please, see bellow its SQL Create!

    CREATE TABLE "MUB"."REGIONAL"
      (
        "OBJECTID" NUMBER(*,0) NOT NULL ENABLE,
        "CODIGO" NVARCHAR2(10) NOT NULL ENABLE,
        "NOME" NVARCHAR2(100) NOT NULL ENABLE,
        "SE_ANNO_CAD_DATA" BLOB,
        "SHAPE" "MDSYS"."SDO_GEOMETRY" ,
        "MI_STYLE" VARCHAR2(254 BYTE),
        CONSTRAINT "REGIONAL_PK" PRIMARY KEY ("OBJECTID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING 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 "MUB" ENABLE
      )
      SEGMENT CREATION IMMEDIATE PCTFREE 0 PCTUSED 40 INITRANS 4 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
      (
        INITIAL 409600 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT
      )
      TABLESPACE "MUB" LOB
      (
        "SE_ANNO_CAD_DATA"
      )
      STORE AS BASICFILE
      (
        TABLESPACE "MUB" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING 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)
      ) ;
    CREATE UNIQUE INDEX "MUB"."SYS_IL0000086331C00012$$" ON "MUB"."REGIONAL"
      (
        PCTFREE 10 INITRANS 2 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 "MUB" PARALLEL (DEGREE 0 INSTANCES 0) ;
    CREATE UNIQUE INDEX "MUB"."SYS_IL0000086331C00011$$" ON "MUB"."REGIONAL" ( PCTFREE 10 INITRANS 2 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 "MUB" PARALLEL (DEGREE 0 INSTANCES 0) ;
    CREATE UNIQUE INDEX "MUB"."SYS_IL0000086331C00004$$" ON "MUB"."REGIONAL" ( PCTFREE 10 INITRANS 2 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 "MUB" PARALLEL (DEGREE 0 INSTANCES 0) ;
    CREATE UNIQUE INDEX "MUB"."REGIONAL_PK" ON "MUB"."REGIONAL" ("OBJECTID") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING 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 "MUB" ;
      CREATE INDEX "MUB"."REGIONAL_IX1" ON "MUB"."REGIONAL"
        (
          "SHAPE"
        )
        INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;

    Sandro.

     
  • Sandro Costa

    Sandro Costa - 2011-02-04

    Just in time! The error occurs for all my tables! Not just for the Regional table!

    Sandro

     
  • John O'Toole

    John O'Toole - 2011-02-04

    Hi Sandro,

    I am able to reproduce this error using some data on a 9i database, but can't reproduce it on 10g or 11g.  However I don't think the problem is version specific - its a case of trying to coerce the optimizer to play ball and use the spatial index.

    I found that if I remove the SDO_FILTER from the query, then it worked ok.  Can you try the query below to see if it works for you?
    If it does then that'll give us more to work with.  Maybe we can look at removing the SDO_FILTER predicate from the query in GeoRaptor.  Its a bit tricky though as we want the Layer SQL to be editable so that a user can add/remove columns and add more predicates, and then we just append the SDO_NN for Identify.  We could pick out the SDO_FILTER, maybe by surrounding it in commends, but it could be messy.

    John

    SELECT rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
      FROM MUB.REGIONAL t
    WHERE MDSYS.SDO_NN(t.SHAPE,
                        MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),
                        'sdo_num_res=6',1) = 'TRUE'
       AND SDO_NN_DISTANCE(1) < 533
    ORDER BY sdo_nn_distance(1)

     
  • Sandro Costa

    Sandro Costa - 2011-02-04

    Hi John,

    Yes! Now it worked!

    SELECT rowid,"OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
    FROM MUB.REGIONAL t
    WHERE MDSYS.SDO_NN(t.SHAPE,
    MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),
    'sdo_num_res=6',1) = 'TRUE'
    AND SDO_NN_DISTANCE(1) < 533
    ORDER BY sdo_nn_distance(1);

    Result:

    ROWID OBJECTID           CODIGO     NOME                                                                                                


    AAAVE7AAMAAAAKbAAI 9     22         NOROESTE

     
  • John O'Toole

    John O'Toole - 2011-02-04

    Hi Sandro,

    Ok, that's good that it works without the SDO_FILTER. 

    Simon, maybe the SDO_FILTER predicate should be hidden from the Layer SQL in Layer Properties.  Only show the Select from the feature table, maybe with a dummy predicate such as WHERE 1=1.  That way we can always safely append with an AND. 
    Then when querying data for the map append the SDO_FILTER and then running Identify append the SDO_NN.

    John

     
  • Sandro Costa

    Sandro Costa - 2011-02-04

    Alright John!

    While you improve the GeoRaptor, I will use the previous version!
    Thank you for your interest!

    Sandro.

     
  • Simon Greener

    Simon Greener - 2011-02-05

    John and Sandro,

    Good suggestion John re hidden SDO_FILTER predicate….

    However, until then can you try this Sandro but replace my attributes, values etc with yours.

    SELECT rowid, "ID", t.GEOM.sdo_point as GEOM
      FROM CODESYS.GEODPOINT2D t
     WHERE SDO_FILTER(t.GEOM,
               MDSYS.SDO_GEOMETRY(2003,8311,NULL,
                                  MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                  MDSYS.SDO_ORDINATE_ARRAY(104.412902,-25.099642,124.147881,-17.429912)),'querytype=WINDOW') = 'TRUE'
       AND SDO_WITHIN_DISTANCE(t.GEOM,
                               MDSYS.SDO_GEOMETRY(2001,8311,MDSYS.SDO_POINT_TYPE(114.257965,-20.098619,NULL),NULL,NULL),
                               'distance=40009, unit=M') = 'TRUE'
    ORDER BY mdsys.sdo_geom.sdo_distance(t.geom,
                                         MDSYS.SDO_GEOMETRY(2001,8311,MDSYS.SDO_POINT_TYPE(114.257965,-20.098619,NULL),NULL,NULL),
                                         0.05,
                                         'unit=M');
    

    You said:

    OBS: I've noticed that the X values (min and max) to MDSYS.SDO_ORDINATE_ARRAY have changed if compared to those defined for the USER_SDO_GEOM_METADATA. The values of Y remain the same!

    If the GeoRaptor layer MBR preference is set to RTree, and the geometry being mapped holds projected data (ie not Long/Lat) GeoRaptor will use the geometry's RTree's spatial extent and NOT the one in USER_SDO_GEOM_METADATA.  If the preference is for Metadata then it will use the USER_SDO_GEOM_METADATA value. You must have RTree set as the preference.

    I have to change comma for dot! Although, in SQL Developer preferences, I have setted the NLS for english !

    GeoRaptor does try to detect the Locale of the operator. However, at the moment I am using the operating system Locale and not SQL Developer's current NLS setting (which may not be the database's). I have been trying to work out how to access the SQL Developer NLS setting Tools>Preferences>Database>NLS but I have not been able to work out how. SQL Developer's developer documentation and online help/community is, effectively, non-existent.

    regards
    Simon

     
  • Simon Greener

    Simon Greener - 2011-02-07

    Sandro,

    I can't replicate your problem (I am running XE, 10gR2 and 11gR2) which is why I keep asking these questions.

    Can you run a version of this (ie modify names and coordinates to match your data) against your data?

    SELECT /*+ ORDERED*/ b.*
      FROM CODESYS.GEODPOINT2D a,
          ( SELECT /*+LEADING*/ rowid,"ID",sdo_nn_distance(1), sysdate, systimestamp, current_timestamp,t.GEOM.sdo_point as GEOM
              FROM CODESYS.GEODPOINT2D t
             WHERE SDO_FILTER(t.GEOM,
                              MDSYS.SDO_GEOMETRY(2003,8311,NULL,
                                                 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                                 MDSYS.SDO_ORDINATE_ARRAY(104.412902,-25.099642,124.147881,-17.429912)),'querytype=WINDOW') = 'TRUE'
           )  b
     WHERE MDSYS.SDO_NN(a.GEOM,
                        MDSYS.SDO_GEOMETRY(2001,8311,MDSYS.SDO_POINT_TYPE(114.257965,-20.098619,NULL),NULL,NULL),
                        'sdo_batch_size=0',1) = 'TRUE'
       AND SDO_NN_DISTANCE(1) < 40008.426
       AND b.rowid = a.rowid
     ORDER BY sdo_nn_distance(1);
    

    regards
    Simon

     
  • John O'Toole

    John O'Toole - 2011-02-07

    Simon,

    I can reproduce this problem on one particular dataset on a 9i database, so I ran your modified queries:

    SELECT /*+ ORDERED*/ b.*
      FROM DAS.ST_LR_PLAN a,
          ( SELECT /*+LEADING*/ rowid,"ID",sdo_nn_distance(1), sysdate, systimestamp, current_timestamp,t.geometry.sdo_point as geometry
              FROM DAS.ST_LR_PLAN t
             WHERE SDO_FILTER(t.geometry,
                              MDSYS.SDO_GEOMETRY(2003,262152,NULL,
                                                 MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
                                                 MDSYS.SDO_ORDINATE_ARRAY(672401,675364,673358,675663)),'querytype=WINDOW') = 'TRUE'
           )  b
    WHERE MDSYS.SDO_NN(a.geometry,
                        MDSYS.SDO_GEOMETRY(2001,262152,MDSYS.SDO_POINT_TYPE(672640,675548,NULL),NULL,NULL),
                        'sdo_batch_size=0',1) = 'TRUE'
       AND SDO_NN_DISTANCE(1) < 5.372495
       AND b.rowid = a.rowid
    ORDER BY sdo_nn_distance(1);

    This ran for ~2 minutes and then failed with a strange error:
    ERROR:
    ORA-29903: error in executing ODCIIndexFetch() routine
    ORA-22160: element at index  does not exist

    The previous query worked better:

    das@dastest> SELECT rowid, "ID"
      2    FROM DAS.ST_LR_PLAN t
      3   WHERE SDO_FILTER(t.geometry,
      4             MDSYS.SDO_GEOMETRY(2003,262152,NULL,
      5             MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
      6             MDSYS.SDO_ORDINATE_ARRAY(672401,675364,673358,675663)),'querytype=WINDOW') = 'TRUE'
      7     AND SDO_WITHIN_DISTANCE(t.geometry,
      8             MDSYS.SDO_GEOMETRY(2001,262152,MDSYS.SDO_POINT_TYPE(672640,675548,NULL),NULL,NULL),
      9             'distance=50, unit=M') = 'TRUE'
    10  ORDER BY mdsys.sdo_geom.sdo_distance(t.geometry,
    11  MDSYS.SDO_GEOMETRY(2001,262152,MDSYS.SDO_POINT_TYPE(672640,675548,NULL),NULL,NULL), 0.005,'unit=M');

    11 rows selected.

    Elapsed: 00:00:00.34

    Execution Plan

       0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2702 Card=94 Bytes=4982)
       1    0   SORT (ORDER BY) (Cost=2702 Card=94 Bytes=4982)
       2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ST_LR_PLAN' (Cost=2699 Card=94 Bytes=4982)
       3    2       DOMAIN INDEX OF 'ST_LR_PLAN_SPIND'

    Statistics

            789  recursive calls
              2  db block gets
           2891  consistent gets
              0  physical reads
              0  redo size
            742  bytes sent via SQL*Net to client
            383  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
             11  rows processed

    It seems that the simplest approach would be to exclude the SDO_FILTER from the Identify query altogether (as per the query below).  But I guess the disadvantage with that (and what you're probably trying to avoid) is that if the 'query distance in pixels' is set to a large value and we exclude the SDO_FILTER, then we could be reporting on features that are outside the current map window.  However, since SDO_FILTER does a primary filter only, it could still do that anyway - particularly for line/polygon features outside the map window, but near the corners as their MBRs in the RTree probably still intersect the map window.  I guess the SDO_FILTER would be most advantageous at excluding point data that is just outside the map window. 
    Note that excluding the SDO_FILTER does marginally improve the performance of the Identify query - the consistent/physical gets are less without the SDO_FILTER, presumably as it only hits the RTree once.

    das@dastest> SELECT rowid, "ID"
      2    FROM DAS.ST_LR_PLAN t
      3   WHERE MDSYS.SDO_NN(t.GEOMETRY,
      4                      MDSYS.SDO_GEOMETRY(2001,262152,MDSYS.SDO_POINT_TYPE(672640,675548,NULL),NULL,NULL),
      5                      'sdo_num_res=20',1) = 'TRUE'
      6     AND SDO_NN_DISTANCE(1) < 50
      7   ORDER BY sdo_nn_distance(1);

    11 rows selected.

    Elapsed: 00:00:00.12

    Execution Plan

       0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2705 Card=472 Bytes=25016)
       1    0   SORT (ORDER BY) (Cost=2705 Card=472 Bytes=25016)
       2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ST_LR_PLAN' (Cost=2699 Card=472 Bytes=25016)
       3    2       DOMAIN INDEX OF 'ST_LR_PLAN_SPIND'

    Statistics

             71  recursive calls
              2  db block gets
            223  consistent gets
              0  physical reads
              0  redo size
            742  bytes sent via SQL*Net to client
            383  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
             11  rows processed

     
  • Simon Greener

    Simon Greener - 2011-02-07

    John,

    I am sorry to hear my last attempt has failed. It didn't incur the SDO_NN index problem but I am surprised at the error it did produce.  However, the SQL is slightly wrong. The

          ( SELECT /*+LEADING*/ rowid,"ID",sdo_nn_distance(1), sysdate, systimestamp, current_timestamp,t.geometry.sdo_point as geometry
    

    should be:

          ( SELECT /*+LEADING*/ rowid,"ID", sysdate, systimestamp, current_timestamp,t.geometry as geom
    

    That is, the sdo_nn_distance() should not be there and one returns the geometry not the geometry.sdo_point.

    Try that.

    Simon

     
  • Sandro Costa

    Sandro Costa - 2011-02-07

    Hello Simon,

    I did what you asked!

    SELECT rowid, "OBJECTID","CODIGO","NOME","MI_STYLE",t.SHAPE as SHAPE
    FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
    MDSYS.SDO_GEOMETRY(2003,82301,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
    MDSYS.SDO_ORDINATE_ARRAY(559425,7777000,660575,7816000)),'querytype=WINDOW') ='TRUE'
    AND SDO_WITHIN_DISTANCE(t.SHAPE,
    MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),
    'distance=533, unit=M') = 'TRUE'
    ORDER BY mdsys.sdo_geom.sdo_distance(t.SHAPE,
    MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),
    0.001,'unit=M')

    RESULT:

    ROWID OBJECTID CODIGO NOME MI_STYLE SHAPE

    AAAVE7AAMAAAAKbAAI 9 22 NOROESTE (null) MDSYS.SDO_GEOMETRY(2003,82301,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(603325.3825,7801421.2539,…,603325.3825,7801421.2539))

    Alright! I get it! I just got curious as to why the Y values are preserved, and no X!

    My Locale system is Portuguese of Brazil (pt-BR)! But I have to set SQL Developer NLS setting to english (America) to avoid bugs with separators in numeric calculate.

    "SQL Developer's developer documentation and online help/community is, effectively, non-existent". Do You have not a channel closer to the Oracle folks?

    Sandro.

     
  • Sandro Costa

    Sandro Costa - 2011-02-07

    Simon,

    The second:

    SELECT /*+ ORDERED*/ b.*
    FROM MUB.REGIONAL a,
    ( SELECT /*+LEADING*/ rowid,"OBJECTID",sdo_nn_distance(1), sysdate, systimestamp,
    current_timestamp,t.SHAPE as SHAPE
    FROM MUB.REGIONAL t
    WHERE SDO_FILTER(t.SHAPE,
    MDSYS.SDO_GEOMETRY(2003,82301,NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
    MDSYS.SDO_ORDINATE_ARRAY(559425,7777000,660575,7816000)),'querytype=WINDOW') = 'TRUE'
    ) b
    WHERE MDSYS.SDO_NN(a.SHAPE,
    MDSYS.SDO_GEOMETRY(2001,82301,MDSYS.SDO_POINT_TYPE(608364,7798638,NULL),NULL,NULL),
    'sdo_batch_size=0',1) = 'TRUE'
    AND SDO_NN_DISTANCE(1) < 533
    AND b.rowid = a.rowid
    ORDER BY sdo_nn_distance(1);

    RESULT:

    ROWID OBJECTID SDO_NN_DISTANCE(1) SYSDATE SYSTIMESTAMP CURRENT_TIMESTAMP SHAPE

    AAAVE7AAMAAAAKbAAI 9 0 07-FEB-11 07-FEB-11 12.02.01.213000000 PM -02:00 07-FEB-11 12.02.01.213000000 PM AMERICA/SAO_PAULO MDSYS.SDO_GEOMETRY(2003,82301,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(603325.3825,7801421.2539,…,603325.3825,7801421.2539))

    Sandro.

     
1 2 > >> (Page 1 of 2)

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks