Re: Suitable index for the query
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: cary.millsap@xxxxxxxxxxxx
- Date: Sun, 27 Jun 2010 11:33:11 +0200
Cary,
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
