Tunning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Tunning

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    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


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2001
    Posts
    391
    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]

  4. #4
    Join Date
    Jan 2002
    Posts
    474
    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


  5. #5
    Join Date
    Jan 2002
    Posts
    474
    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
  •  



Click Here to Expand Forum to Full Width