Oracle Spatial hints

/*+NO_QUERY_TRANSFORMATION*/

The NO_QUERY_TRANSFORMATION hint causes the optimizer to skip all query transformations including but not limited to OR expansion,view merging, subquery unnesting, star transformation and materialized view rewrite.use the "NO_QUERY_TRANSFORMATION" hint to disable most of the transformations , although some transformations still seem to take place, e.g. IN seems to be always transformed into EXISTS .

This hint which improve performance to a great extent for my Spatial query.

SELECT /*+ NO_QUERY_TRANSFORMATION*/ * FROM CABLE WHERE MDSYS.sdo_filter
(shape,MDSYS.SDO_GEOMETRY (2003,3785,NULL,MDSYS.sdo_elem_info_array (1,1003,3),MDSYS.sdo_ordinate_array (-9003954.980329761, 4271239.694744173, -9003673.417374918, 4271428.398654524)),'querytype=WINDOW') = 'TRUE' and (sdo_RELATE(shape, (SELECT geom FROM user_boundary WHERE userid = 103),'MASK=INSIDE') = 'TRUE') ORDER BY OID 

Execution Time : 47msec

SELECT * FROM CABLE WHERE MDSYS.sdo_filter
(shape,MDSYS.SDO_GEOMETRY (2003,3785,NULL,MDSYS.sdo_elem_info_array (1,1003,3),MDSYS.sdo_ordinate_array (-9003954.980329761, 4271239.694744173, -9003673.417374918, 4271428.398654524)),'querytype=WINDOW'
) = 'TRUE' and (sdo_RELATE(shape, (SELECT geom FROM user_boundary WHERE userid = 103),'MASK=INSIDE') = 'TRUE') ORDER BY OID

Execution Time : 1:09 min

....

if you execute this spatial query without this hint it take around 3 minutes to execute ! amazed

If you want dumps you can mail us :oracleplsql10g@gmail.com


The cost of a query is not proportional to the time execution of a query for example If cost of a query (A) is greater than cost of another query (B) then its not always query (A) takes more execution time than query (B) .

............

Try this simple query too

SELECT /*+ NO_QUERY_TRANSFORMATION */
shape FROM cable WHERE (sdo_relate (shape,(SELECT geom FROM user_boundary WHERE userid =103),'MASK=INSIDE')='TRUE')


SELECT shape FROM cable WHERE (sdo_relate (shape,(SELECT geom FROM user_boundary WHERE userid =103),'MASK=INSIDE')='TRUE')

----------------------
These queries may get executed when no query transformation hint is not given for in some case

SELECT diminfo   FROM all_sdo_geom_metadata  WHERE owner = :own AND table_name = :tab AND column_name = :col

SELECT sdo_relation   FROM sdo_relatemask_table  WHERE sdo_mask = :b1

So evaluate your query and the data in the tables and check execution plan in both scenario and its performance and decide .

Oracle Spatial index

Spatial index creation

-- Create the index without any parameters

CREATE INDEX geod_counties_sidx ON geod_counties(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX

The following parameters are recommended when creating spatial indexes (ie: whihc is a R-Tree index).

CREATE INDEX sp_idx ON my_table (location)
INDEXTYPE IS mdsys.spatial_index
PARAMETERS ('tablesapce=tb_name work_tablespace=work_tb_name')

• WORK_TABLESPACE - During spatial index creation, the process creates intermediate
tables that get dropped when the index is complete. The intermediate tables can take up to 2 times the size of the final index. If WORK_TABLESPACE is not specified, the
intermediate tables are created in the same tablespace as the final index, causing
fragmentation, and possible performance degradation.
You can use SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE, and multiply the result by
2 to provide guidance on sizing the work tablespace. The work tablespace can be re-used to create other spatial indexes.

• LAYER_GTYPE – This parameter is needed especially when working with point-only
layers. If a point-only layer stores its points in the SDO_ORDINATE_ARRAY, you can
still specify LAYER_GTYPE=POINT on spatial index creation. This can help query
performance when performing spatial analysis.

• SDO_NON_LEAF_TBL – This parameter is useful for very large spatial indexes (not
necessary for smaller spatial indexes). This generates two spatial index tables instead of one.The smaller spatial index table is the non-leaf table, which is traversed most often during spatial analysis. It can be beneficial to pin the non-leaf table into the buffer pool, since it is accessed most often. See the example below.

-- Create the index
CREATE INDEX geod_counties_sidx ON geod_counties(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('sdo_non_leaf_tbl=TRUE');

-- Find the non leaf index table name
SELECT sdo_nl_index_table FROM user_sdo_index_metadata
WHERE sdo_index_name='GEOD_COUNTIES_SIDX';


Pin the table in memory
ALTER TABLE MDNT_A930$ STORAGE(BUFFER_POOL KEEP);