oracle index creation guidelines

[bob hollis]

We are adding some tables to an existing 3rd party application (JDE/Peoplesoft). What is a good source of guidelines/ check list /procedure/ etc for determining what indexes should be put on these tables. We also may need to add some indexes to existing (very large) tables to help our new queries run in a timely manner. Iâ m familiar with TKPROF and all that, but what Iâ m looking for is some guidance on getting started.
T

[Jean-Luc Oudart]

1) If you run statspack you will get the list of top transaction (SQL). From the code you could deduct some of the (missing) indexes

2) TKPROF : surely when a transaction us running slow you will trace it, analyze and take action (the index creation may not be the only answer to the performance pb, but if you don't have access to the code ...)


Also, sometimes you may have the index but the order of the columns is not the best.

Regards,
Jean-Luc

[bob hollis]

there is too much going on for statspack to help. i'm just looking for some generic guidelines here.

[Jean-Luc Oudart]

Bob,

You will have to know the application (SQL) a way or another. Ih the 3rd party does not give you the list of indexes to create (or this might be an incomplete list !) you will have to obtain the list of SQL that require attention.
Statspack is one way.

Check also this doc from Metalink on SQL tuning (attachment)

Regards,
Jean-Luc

[James A. Donovan]

Pretty much any good book on Oracle performance tuning will have a section on why you build a particular type of index, and what columns are good candidates for indexing.

The trace analyzer tool from Oracle can diagnose where an index could be of use.

http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=224270.1

[Volker Borowski]

In general,

design your queries to contain as much "column = AND" in your WHERE clause as possible. Put these at the very beginning of your statement with the column of the most distinct values at the very beginning.
If your query is expected to deliver less than 30% of the table-content, create an index on those columns in the order specified above.
If your query delivers more ( 40%+), a full table scan may be the best choice (depending on average row length).

If you have a large percentage of data to be selected, and need sorting, an index on the sorting columns may serve you better than the one on WHERE, it depends.

If you have to use ranges, try to use "WHERE column BETWEEN AND " and avoid ">" and "<".

If you have to use aggregates (sum, count, avg, ... ) partitioning the table on the corresponding group -columns may help, because the aggregates can be calculated by parallel query in the diffrent partitions.

If you use max, min functions, be sure to specify a corresponding ASC or DESC index.

....

Just starters
Volker

[Yogeeraj_1]

hi,

also consider Function Based Indexes.

Oracle8i introduces a feature virtually every DBA and programmer will be using immediately -- the ability to index functions and use these indexes in query. In a nutshell, this capability allows you to have case insenstive searches or sorts, search on complex equations, and extend the SQL language efficiently by implementing your own functions and operators and then searching on them.

hope this helps too!
regards
Yogeeraj

[Yogeeraj_1]

hi again,

more on Function Based Indexes.
see attachment.

regards
Yogeeraj

[Greg OBarr]

I recommend using the SQL analyzer in Enterprise Manager. If you haven't already set it up, it's worth doing. All it really does is give you the same info that statspack would, but it's much FASTER and easier to read and understand. You can use it to show you the worst performing SQL statements based on disk I/O, buffer gets, etc. Plus, there is a "virtual index" tool. You can select a SQL statement that takes a long time to run, then create a "virtual index" and see how the explain plan would change IF you created different indexes - it will show you the % improvement in execution time too.

If you're going to do this, use the latest 9i version. Even if you're running 8i database, you can analyze with the 9i Enterprise Manager (you have to setup a small 9i database for the repository).

[Printaporn_1]

Hi T,

Oracle Expert feature in Oracle Enterprise Manager can also guide you the advise on index that need to create.
It was very easy and power full , they can also give you the script to create indexes.

HTH