Database Software

Hi,
here is my problem. I'm trying to find which lines are composing polygons I have selected from intersection of polygons in one table intersecting pre-selected rectangles in another tables at 1 / 20K scale. Tables from testing environment are :

- dde_20k_feuil_carto_se for rectangles (number of rows 6844)
- dde_20k_agence_se for polygons (number of rows 14)
- dde_20k_limit_combi_admin_le (number of rows 47638)

I have another similar case based on the following tables in the same environment and of course performance is worse in that case :

- dde_20k_feuil_carto_se for rectangles (number of rows 6844)
- dde_20k_depot_surfa_se for polygons (number of rows 65810)
- dde_20k_limit_peupl_ecofo_le (number of rows 577202)

In production environment the volumes of these tables will be much bigger.

here is the last query I got the best performance from all the tests I did :

select
arc.OBJECTID
from
(select
age.objectid, age.shape
from dde_20k_feuil_carto_se f20,
dde_20k_agence_se age
where SDO_FILTER(age.shape,f20.shape, 'querytype=WINDOW') = 'TRUE'
and f20.fca_no_feuil_carto like '22B07%') sl2,
(select * from dde_20k_limit_combi_admin_le where lca_ind_limit_age = 'O') arc
where SDO_RELATE(arc.shape, sl2.shape, 'mask=on querytype=WINDOW') = 'TRUE'
group by arc.OBJECTID


I've tried /*+ ordered */ and also with and without the
NO_INDEX(OPS$DDEF02.LCA_LIMIT_AGENC_I) to see if I can get better performance but I didn't get any difference. I've tried also with mask=anyinteract to get better performance but it was still very long before to get any results and these results were not corresponding to waht I can get from ArcMAP selection.

I've used SDO_FILTER rather than using SDO_RELATE for primary filter between polygons because it returns the same number of rows in that case and performance is better. But I'm still worry that sometimes results cannot be so accurate if I'm doing that.

SDO_RELATE is pretty fast with polygons selection but get really extremely slow when I'm combining polygons and lines composing these polygons.

Is there anybody who can help me about this ?

thank's !

Norman