Tunning

[ashley75]

could someone tell me why it's still doing a full table scan on GRID_MASTER ??? I have index on GRID_LVL and here is the the query.



SELECT /*+ INDEX(GRID_MASTER A GRID_MASTER_LVL_IDX)*/ a.grid_lvl2_code, b.grid_lvl3_code
FROM grid_county_centroid B,grid_master A
WHERE sdo_relate(B.centroid,A.sob_geometry, 'mask=anyinteract querytype=join') = 'TRUE'
and a.grid_lvl = 2;

for some reason, it still didn't force to user the index, maybe my hint is not right.

Please advise

[marist89]

Yes, your hint is not correct. Try:
SELECT /*+ INDEX(A GRID_MASTER_LVL_IDX)*/ a.grid_lvl2_code, b.grid_lvl3_code
FROM grid_county_centroid B,grid_master A
WHERE sdo_relate(B.centroid,A.sob_geometry, 'mask=anyinteract querytype=join') = 'TRUE'
and a.grid_lvl = 2;

[mike73]

you might want to force to use the ORacle SPatial index in there as well.

What is your Oracle SPatial index ??? and what type RTREE or QTREE ???


[Edited by mike73 on 01-28-2002 at 03:03 PM]

[ashley75]

Jeff,

thanks so much for your help as always but I have another question I need to ask you. It does use the index instead of doing a full table scan;however, the cost is really high by looking at the explain plan.

BUT if I am running this query I have much better cost then the above query.


SELECT /*+ INDEX(A GRID_MASTER_SDX)*/ a.grid_lvl2_code, b.grid_lvl3_code
FROM grid_county_centroid B,grid_master A
WHERE sdo_relate(B.centroid,A.sob_geometry, 'mask=anyinteract querytype=join') = 'TRUE'
and a.grid_lvl = 2 and a.grid_lvl2_code like 'WY%' and b.grid_lvl = 3



the only thing different is I add two more conditions

and a.grid_lvl2_code like 'WY%' and b.grid_lvl = 3


why the cost is much better when I at this level ??? Could you please give some explainanation of how to reduce the cost on the first query???
Thanks

[ashley75]

Jeff and Julian or anyone in here,


Do you have any advises on the above queries ????



Thanks so much