Re: Measuring index usage

On a technical note -

Bear in mind that indexes representing foreign keys are often worthless as data access paths, but may have to exist to avoid pk/fk lock problems.

Common errors in over-indexing can result from this, you may see indexing on a single table like:

    taba_fk_a(cola)
    taba_fk_b(colb)
    taba_uk(cola,colb)

In this example, taba_fk_a is redundant as its pf/fk role is handled by taba_uk.

Similarly, watch out for:

    tabx_ab(cola, colb)
    tabx_ba(colb, cola)

As a general rule one of these indexes can be reduced in size by dropping the second column, leaving one of them as a single column index. Under 8.1.6 the optimum strategy is to leave the higher-precision column as the single column index, and use the lower-precision column as the leading (compressed) column of the two-column index.

There are cases where the general rule does not apply of course.

--

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

Steven Hauser wrote in message <8qu04t$3s4$1_at_garnet.tc.umn.edu>...


>Others say:

>>>Ooooo, I am so afraid to change anything in a badly architected, poorly

>>>designed product that is implemented by a bunch of morons.

>

>Bah, I say if you are not worthless and weak and have a test system

>go for it.

>

>To your real question: at intervals sample v$sqlare, v$sql,

>v$access, v$db_object_cache, and any other v$ performance views

>and look for indexes.  This will not be perfect but who cares.

>The stuff is documented in the Oracle 8 Reference (or whatever version.)

>

>Just make sure to keep a copy of the DDL to recreate any index you

>whack. Good luck.

>

>--

>---------------------------------------------------------

>Steven Hauser

>email: hause011@tc.umn.edu   URL: http://www.tc.umn.edu/~hause011

>---------------------------------------------------------