Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    16

    Unanswered: Hint to skip index?

    Hi!

    I'm in the proccess of tuning some sql queries. Is there some way I can hint ( /*+ */ ) oracle to skip using an index when executing a query?

    /Mattias

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Hint to skip index?

    Originally posted by umciggy
    Hi!

    I'm in the proccess of tuning some sql queries. Is there some way I can hint ( /*+ */ ) oracle to skip using an index when executing a query?

    /Mattias
    You can use the FULL hint with the alias of the table whose index you want to ignore, e.g.
    SELECT /*+ FULL(e) */ empno, ename
    FROM emp e
    WHERE deptno = 123;

    Of course, it's only a hint so Oracle may choose to ignore it.

    An alternative is to experiment with the optimizer_index_cost_adj parameter- see here:

    http://technet.oracle.com/docs/produ...htm#REFRN10143

  3. #3
    Join Date
    Jan 2003
    Posts
    16

    Re: Hint to skip index?

    Originally posted by andrewst
    You can use the FULL hint with the alias of the table whose index you want to ignore, e.g.
    SELECT /*+ FULL(e) */ empno, ename
    FROM emp e
    WHERE deptno = 123;

    Of course, it's only a hint so Oracle may choose to ignore it.

    An alternative is to experiment with the optimizer_index_cost_adj parameter- see here:

    http://technet.oracle.com/docs/produ...htm#REFRN10143
    Did the trick. Great, thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    By rule - RBO uses the latest table statistics to prepare the execution plan for a query. If you delete the statistics for particular table, then the RBO will force FTS (no index use in this case).

    There is a package DBMS_STATS which allows you to manipulate the statistics - export first, then delete then, then import back. Of course you can also gather them as well.


    Hope that helps,

    clio_usa - OCP - DBA


Posting Permissions

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