need idea about table index

marco

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.

sb92075

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

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

but I guess index can possibly improve performance.

Why guess? Do some testing.