-
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
-
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;
Jeff Hunter
-
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]
-
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
-
Jeff and Julian or anyone in here,
Do you have any advises on the above queries ????
Thanks so much
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|