Database Software

Hi,

I have a big table with 12 million records and this table contains a spatial column which stores only points.

I want to obtain the number of points with some relationship with a polygon that has 5 vertices and 150km².

I'll explain what I did:

CREATE TABLE PROSPECT
(
    COD_PROSPECT NUMBER NOT NULL,
    UFMUN_COD    NUMBER NOT NULL,
    GEOM         MDSYS.SDO_GEOMETRY,
    CONSTRAINT PK_PROSPECTT PRIMARY KEY (COD_PROSPECT) ENABLE
)
    PARTITION BY RANGE(UFMUN_COD)
    (
       PARTITION NORTE         VALUES LESS THAN (211400),
       PARTITION NORDESTE      VALUES LESS THAN (317220),
       PARTITION SUDESTE_MENOR VALUES LESS THAN (355030),
       PARTITION SAO_PAULO     VALUES LESS THAN (355730),
       PARTITION SUDESTE_MAIOR VALUES LESS THAN (412880),
       PARTITION SUL           VALUES LESS THAN (500840),
       PARTITION CENTRO_OESTE  VALUES LESS THAN (522230)
    );

The table is partitioned by UFMUN_COD field.

CREATE INDEX IDX_PROSP_UFMUN_COD ON PROSPECT(UFMUN_COD) LOCAL;

ALTER TABLE PROSPECT PARALLEL 10;

CREATE INDEX IDX_PROSPECT_GEOM ON PROSPECT(GEOM) 
INDEXTYPE IS MDSYS.SPATIAL_INDEX 
PARAMETERS ('WORK_TABLESPACE=ONMAPS_WORK LAYER_GTYPE=POINT') LOCAL PARALLEL;

After creating the table I inserted the 12 million records and ran the query:

SELECT COUNT(1) AS TOTAL
FROM ALPHABASE.PROSPECT A
WHERE A.UFMUN_COD = 355030 AND
SDO_ANYINTERACT(
   A.GEOM, 
   MDSYS.SDO_GEOMETRY(2003, 
                      8307,
                      NULL, 
                      MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), 
                      MDSYS.SDO_ORDINATE_ARRAY(-46.7386735149995, -23.5904745753416, 
                                               -46.5616191027776, -23.5904745753416, 
                                               -46.5616191027785, -23.5055971006428, 
                                               -46.7386735149986, -23.5055971006428, 
                                               -46.7386735149995, -23.5904745753416))
) = 'TRUE'; 
TOTAL                  
------------
2183656  

Problem? It takes 480 seconds to complete.
Am I doing something wrong?
Is there a way to improve the performance of these kind of query?

Thanks!
Waldecir

Edited by: Waldecir P. Junior on 03/05/2012 05:31

Edited by: Waldecir P. Junior on 03/05/2012 05:43

Edited by: Waldecir P. Junior on 03/05/2012 05:48

Edited by: Waldecir P. Junior on 03/05/2012 05:50