Re: Suitable index for the query

   Although I agree with all you said, I have to nuance the following sentence, not really for you :-), but for those who read this thread:
>
> The bottom line: If you don't know what your queries look like, then
> there's no way you can know what indexes will optimize your system.
> Specifically, you /cannot/ define indexes optimally by looking only at
> your data. You have to look at your SQL.
>

I'd rather say "look at what you want to do" rather than "look at your SQL".

Firstly, SQL code, unless you get a canned application, isn't immutable. I keep function based indexes, for instance, for cases when there is really no other way to salvage a query - I fisrt try to change

  where substr(my_column, 1, 5) = 'XXXXX'

into

  where my_column like 'XXXXX%'

if my_column is indexed and if the index is likely to be useful. To summarize, don't assume that statements are sacred, even if you are a production DBA. Don't hesitate to discuss them with developers, you may teach them something (or make them exclaim "oops!")

Secondly, I have met many applications where individual queries were untunable but yet there still was ample scope for improvement. Suppose that you get from a file a large number of values that you have to use as search criteria to query a table. If in your program you open the file, read record after record and use each value read as the search condition in a WHERE clause, you may decide that an index on the column that contains this value would speed up the process. On the other hand, if you create an external table atop the file and use it in a straight join instead of looping on a SELECT statement, you may discover that the index isn't really useful after all, and that a hash join far outperforms the loop, even with the index. It often requires a bit of lateral, "out of the statement" thinking, but it's often very rewarding.

My 0.02 euros

S Faroult

--
http://www.freelists.org/webpage/oracle-l