Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Designing indexes

Re: Designing indexes

From: HansF <News.Hans_at_telus.net>
Date: Fri, 10 Feb 2006 03:43:11 GMT
Message-Id: <pan.2006.02.10.03.43.10.169733@telus.net>


On Thu, 09 Feb 2006 10:13:36 -0800, News wrote:

>

>> Although - from Oracle - the Enterprise Manager Diagnostic Pack,
>> specifically AWR and ADDM in 10g, is an attempt to assist with this
>> requirement.

>
> I used EM and don't remember it gives hints about indexes. If it does,
> this will assume dropping all indexes and letting EM make suggestions ?

Nope. Remember that indexes have two [primary] purposes:

-access path (usually an attempt to impact the explain plan); and -structural support (the index to assist enforcing primary key, unique  data, foreign key,etc).

I find it is usually assumed that the DBA or developers will handle the second case indexes anually. That seems to be an unwritten assumption in EM as well.

In 9i, you want to use the Diagnostics pack SQL Statement analyzer.

In 10g you want to fire up the SQL Access advisor.

In both cases you need to have a reason to consider moving towards an index (you are in a tuning mode).

Note that Diagnostics pack (all versions) is an extra $ option. In your case, offset the cost with the cost of managing the indexes manually and it tends to have a reasonable price tag.

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** Top posting [replies] guarantees I won't respond. ***
Received on Thu Feb 09 2006 - 21:43:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US