how to understand if indexes are needed.

[Giada Bonfà]

Hi
How can I understand if a database need some indexes?
What are statistics? How can I get them?

thank you

[Steve Steel]

Hi


Basically an index on a large table is always good.

It makes your access faster .

http://www.dba-oracle.com/art_9i_indexing.htm

If you have large tables with many repetetive selects on the same fields then index on the fields and you work faster.

See
http://www.tc.umn.edu/~hause011/article/Tunage.html

http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part2.htm

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x0e6cc1c4ceddd61190050090279cd0f9,00.html

http://www.eweek.com/article2/0,3959,12410,00.asp

steve Steel

[Brian Crabtree]

Basiclly, tables over about 2000 rows almost have to have indexes on it. Really, unless the table itself is very small, you should probably have an index on it anyway. Indexes need to be placed on columns that you are restricting (columns in the where clause).

To analyze, there are a few options.

analyze table compute statistics;

This gathers the complete statistics about the table and indexes by reading all of the rows from the table to generate the statisitics needed.

analyze table estimate statistics sample 10 percent;

This gathers a partial view of the data by reading only 10% of the table. If the 10% queried does not reflect your data, this can cause a bad query plan.

You can also use the "DBMS_UTIL.ANALYZE_SCHEMA" package to analyze the full schema with the different options. This is probably the easiest to use to generate all of the statistics for the tables at once as a job.

Remember that with indexes and analyze, you will want to generate sql plans or traces to view the optimization route as well if you are trying to tune queries.

Hope this helps,

Thanks,

Brian

[Yogeeraj_1]

hi,

Indexing is a crucial aspect of your application design and development. Too many indexes and the performance of DML will suffer. Too few indexes and the performance of queries (including inserts, updates and deletes) will suffer. Finding the right mix is critical to your application performance.

Indexes should not be an afterthought in application development. From the very beginning, if you understand how the data will be used, you should be able to come up with the representative set of indexes you will in your application. Too many times the approache seems to be to throw the application out there and then see where indexes are needed. This implies you have not taken the time to understand how the data will be used and how many rows you will ultimately be dealing with. You'll be adding indexes to this system forever as the volume of data grows over time (reactive tuning). You'll have indexes that are redundant, never used, and this wastes not only space but also computing resources. A few hours at the start, spent properly considering when and how to index your data will save you many hours of 'tuning' further down the road. (it WILL, not it MIGHT)

If your goal is to tune for performance, you should start looking into tools like SQL_TRACE, TIME_STATISTICS, and TKPROF which comes with the database. Advance tuning will involve analysis of STATSPACK reports.

If you need further help, please let us know.

Best Regards
Yogeeraj

[R. Allan Hicks]

There not much more to say than what yogeeraj has already said. (bravo!)

There is a SQL Tuning Workshop class at Oracle University that covers a lot about statement tuning. To index or not to index and what to index. I took it and liked the hands on that it gave.

-Happy Computing
Allan