Re: Auditing Index usage

There is no built-in for auditing an index, but if you are very confident about which indexes will not be used, a cheap starting point is to put the redundant ones into the same (small set of) tablespaces, and
then show that the only activity on those tablespaces is write activity.

You could also try arguing the case that if you do a query:

    select * from table where indexed_col = 'constant' and Oracle does not use an index, then Oracle will never use that index. There are a couple of conditions to apply on that argument though - e.g. - getting the correct optimizer_goal, queries which might to a fast-full-scan on the index and avoid the table, other queries which can be satisfied completely inside the index etc. Logic is so much harder than a sledge-hammer ;)

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Iain Knowles wrote in message <8b6csj$80m$1_at_newsg1.svr.pol.co.uk>...
>Hi
>
>Does anyone know if it is possible to audit index usage on 8i.
>
>I use a database, that has 6 main tables. The biggest table has approx
>48million rows with approx 90 columns. There are other tables with between
>18M and 30M rows. I have been asked to index every column in the six main
>tables. In my opinion less than 5% of the indexes would ever be used.
Received on Tue Mar 21 2000 - 13:17:33 CST