Oracle didn't use index - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 28

Thread: Oracle didn't use index

  1. #11
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    make sure u have created index on this column.

    regards
    anandkl
    anandkl

  2. #12
    Join Date
    Jan 2002
    Posts
    474
    Thanks all but my query is not working now on explain plan.

    Please advise the below query

    select /*+ grid_master_lvl_idx (grid_master) */
    from grid_master
    where grid_lvl=2 and grid_priority=1

    it is telling me that missing expression


  3. #13
    Join Date
    Jul 2000
    Posts
    521
    Can you post the table description ?
    How many rows are there in the table ?
    How many are with grid_lvl=2 ?
    svk

  4. #14
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    In addition, If u can post please following:

    select * from all_tables where table_name = 'GRID_MASTER' and owner = 'SPARTIAL';

    select * from all_indexes where table_name = 'GRID_MASTER' and owner = 'SPARTIAL';

    May be (but not absolutly needed)
    analize index III validate structure;
    select * from index_stat;
    for index on the column GRID_LML.

    Then, in sqlplus do following:

    set autotrace TRACEONLY EXPLAIN STATISTICS;
    select grid_name, grid_lvl2_code From spatial.grid_master Where grid_lvl = 2 and
    grid_priority = 1;

    and post result of trace of this query.
    If we'll have this information, I hope, we can explain "why oracle optimazer use FULL SCAN TABLE?"

  5. #15
    Join Date
    Feb 2001
    Posts
    128
    Sometimes, even when you use a hint, it may be performing a full table scan. I guess, if you use the RULE hint, then it is forced to use the index. In your case, the performance may increase.

  6. #16
    Join Date
    Jan 2002
    Posts
    474
    There were about 600,000 rows in the table,
    and 51 rows where grid_lvl=2


    It's doing a full tablespace even I had the index for grid_lvl in there.

    Please help

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Is your GRID_LVL column is of type NUMBER? Is it not perhaps of type VARCHAR2 or CHAR or something like that?

    Is your index on GRID_LVL a single column index or a composite one?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    Jan 2002
    Posts
    474
    JMODIC,

    THANKS FOR YOUR QUICK REPLY

    grid_lvl is number and it's one column index

    please advise


  9. #19
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by ashley75
    Thanks all but my query is not working now on explain plan.

    Please advise the below query

    select /*+ grid_master_lvl_idx (grid_master) */
    from grid_master
    where grid_lvl=2 and grid_priority=1

    it is telling me that missing expression

    You're syntax is wrong:
    Code:
    select /*+index(gm grid_master_lvl_idx) */
    from grid_master gm
    where gm.grid_lvl=2 
    and gm.grid_priority=1
    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."

  10. #20
    Join Date
    Jan 2002
    Posts
    474
    Jeff,

    I ran your query and I got an error

    ora-00936: missing expression

    please advise


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