DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Oracle didn't use index

  1. #1
    Join Date
    Jan 2002
    Posts
    474
    please help me with this query:

    select grid_name, grid_lvl2_code
    From spatial.grid_master
    Where grid_lvl = 2 and
    grid_priority = 1

    I look at the explain plan table and notice that it does a full tablescan, I have an index on the column grid_lvl.

    Please advive what I need to do.

    Thanks

  2. #2
    Join Date
    Mar 2002
    Posts
    48
    Analyze the table and check the plan.If still index not used,
    use hints in th query.

    It could be like you might small number of rows where cost would be more to use index then full table scan.

  3. #3
    Join Date
    Jan 2002
    Posts
    474
    Table has been analyzed but it still do a full tablescan.

    Jeff or Sam or Julian

    do you have any advises ???

    thanks

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You can either add a composite index on grid_lvl and grid_priority or use a hint for the index.

  5. #5
    Join Date
    Mar 2001
    Posts
    635
    Hi

    Try Creating a compund index on

    grid_lvl

    and

    grid_priority

    then try the query

    Regards
    Santosh

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Use a hint.
    Jeff Hunter

  7. #7
    Join Date
    Jan 2002
    Posts
    474
    is it the right syntax ???

    select /*+ grid_name, grid_lvl2_code*/
    From spatial.grid_master
    Where grid_lvl = 2 and
    grid_priority = 1

    please advise




  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I think that you need to name the inndex that you want to use.


    select /*+ XIE1_IndexName */
    From spatial.grid_master
    Where grid_lvl = 2 and
    grid_priority = 1


  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, your hint syntax is wrong.

    BTW, are you realy sure you want oracle to use the index for that query? What if the performance with index will actually be worse? What does the following two queries return for your table?

    select count(*) from spatial.grid_master
    Where grid_lvl = 2;

    select count(*) from spatial.grid_master;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Feb 2002
    Posts
    27
    select /*+ index( grid_master XIE1_IndexName) */
    From spatial.grid_master
    Where grid_lvl = 2 and
    grid_priority = 1


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