Forum Stats

  • 3,740,604 Users
  • 2,248,280 Discussions
  • 7,861,341 Comments

Discussions

need idea about table index

marco
marco Member Posts: 338
edited Mar 15, 2013 8:47PM in SQL & PL/SQL
Hi all,

could someone please advise me tool for definition of needed index? In other words, I got query with "table access full", because one of tables doesn't have any indexes, so I should know what index would be the best to create. I don't say that "table access full" is always evil, but I guess index can possibly improve performance.
Tagged:

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    marco wrote:
    Hi all,

    could someone please advise me tool for definition of needed index? In other words, I got query with "table access full", because one of tables doesn't have any indexes, so I should know what index would be the best to create. I don't say that "table access full" is always evil, but I guess index can possibly improve performance.
    columns which appear within WHERE clause are good candidates to be indexed
  • onedbguru
    onedbguru Member Posts: 831 Silver Badge
    edited Mar 15, 2013 7:38PM
    best indexes include the most frequently used columns in the ON (Join) and WHERE clauses. With no index, it can only do a FTS.

    create an index in a test environment
    set autotrace traceonly explain;
    select.... from table where <indexed col> etc..
    set autotrace off -- to turn it off...
    --REPEAT this sequence until you have the plan you expect


    Using this autotrace does not actually execute the query (no rows retrieved). Review the output looking for ACCESS method vs. FILTER method
  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    but I guess index can possibly improve performance.
    Why guess? Do some testing.
This discussion has been closed.