Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    24

    Unanswered: How to make oracle use my index?

    Without index these 2 queries take 23 seconds on Oracle developer edition on my PC:

    (1) select invline from bisales order by invline desc
    (2) select invline from bisales where invline >0 order by invline desc

    All 2 million rows in the table have invline > 0.

    Then I added index
    create index index2 on bisales (invline desc)

    Now query (1) still takes 23 seconds and (2) only takes 0.01 second.

    Why query (1) does not use the index?

    I tried give it a hint
    select /*+ index(bisales index2) */ invline from bisales order by invline desc
    it does not help.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2010
    Posts
    24
    Where do I get EXPLAIN PLAN?

    I have Oracle on my PC and I am using DBVisuallizer to run SQL.

    Thanks

  4. #4
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    After you create the index, try this to make sure the system has analysed/gotten table stats:
    Code:
    begin
    dbms_stats.gather_table_stats('schema_name', 'bisales');
    end;
    /

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Why query (1) does not use the index?
    When it appropriate to not use an index?

    Index is NOT required to be used by every query.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Aug 2010
    Location
    Bangalore
    Posts
    7
    If you really want to force oracle to use your index, you can do that using hint as shown below.

    select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias

  7. #7
    Join Date
    Aug 2010
    Posts
    1
    Thanks for the information!! Keep up the good work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •