Results 1 to 5 of 5

Thread: Index not picking up

  1. #1
    Junior Member
    Join Date
    Feb 2009
    Answers
    11

    Index not picking up

    Scenario: There is a tablespace and an index is created on that.When we run a Sql script for a query.It is not picking up the Index,instead it is doing a full table scan.

    What could be the reason and how should u resolve it?


  2. #2
    Expert Member
    Join Date
    Nov 2008
    Answers
    300

    Re: Index not picking up

    Indexes might not get picked when you are using Funtions in where clause of your SQL statement. You can force your query to use an INDEX by using HINTS in your select query. INDEX HINT will be helpful to ask the query to use that Index while doing table scan.

    Example:

    Code:
    select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias
    In the above query, Hint INDEX is used that is denoted by /*+ ...... */

    emp_alias is a table name and ix_emp is the index name created on the table emp_alias.
    Thus the above SELECT statement will use the index ix_emp.

    But in many cases making use of HINT would degrade the performance of the query, say in case when the amount of data is less. Dont force your query to use Index and thus kill the performance.


  3. #3
    Junior Member
    Join Date
    Mar 2009
    Answers
    2

    Re: Index not picking up

    Is an Hint is forcefull task (which is madatory) for orracle to perform...?


  4. #4
    Expert Member
    Join Date
    Nov 2008
    Answers
    300

    Re: Index not picking up

    yes INDEX HINTS are something which will force oracle to make use of an Index for the query. Some times oracle does not use hint in case if the amount of data is very less and the performance is awesome without using an INDEX. Now you force the query to use Index by specifying Index Hint in the query and the performance Hits.....

    yes it is a forceful task and not always advisable to use.


  5. #5
    Junior Member
    Join Date
    Jun 2007
    Answers
    11

    Re: Index not picking up

    Analyze the table and index.
    and then check it.


  6.  Sponsored Ads
    Ad


     

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact