Re: Table Design - Index Method

Create an index on every column which can be used alone in the where clause. If a combination of these columns is used in the search criteria then let Oracle choose an index. If the partitioning feature is available, you can partition your table on the least selective columns (up to two columns if you use composite partitioning) such as zipcode. That may still give you acceptable performance but you don't need indexes on these columns. The fewer indexes you have, the faster your DML will be.

If this is an OLTP database you may want to reconsider using the less selective columns to do your search. A common mistake people make is allow searches on city, county, zipcode, etc. only to find out that they are not going to get fast response time. Nor are the search results very useful. But for reporting purposes it's perfectly fine.

madhu17_at_vsnl.net (S Madhusudhanan) wrote in message news:<2eead8c7.0301240717.9882ee9_at_posting.google.com>...
> Hi All,
>
> -8.1.7.4 on Solaris 8-
>
> A question on indexing strategy...I have a customer table with about
> 25 Million records with another 25000 rows getting added daily,
> through out the day.
>
> This table has fields like Forename, Surname, Address fields, Zipcode,
> Phone number etc - about 14 columns in all. A front-end app would
> accept search criteria based on any combination of the fields and send
> across the query.
>
> All the columns are almost equally good candidates as search criteria.
> What would be the best method of indexing this table so that it allows
> best throughput for any combination of columns?
>
> Thanks and regards
> S Madhusudhanan
Received on Fri Jan 24 2003 - 17:01:51 CST