Re: Suitable index for the query
- From: Kellyn Pedersen <kjped1313@xxxxxxxxx>
- To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>, cary.millsap@xxxxxxxxxxxx
- Date: Sun, 27 Jun 2010 19:18:07 -0700 (PDT)
I loved seeing some light on this subject. I drive my developers crazy because
before I will add an index, I need to see a balance sheet that will prove to me
the cost-effectiveness of it.
My developer has got to show me that it's worth slowing down transactions to
speed up his queries with an index when there may be another option.. I just
started looking into a process where 75% of the waits were on index contention
for massive inserts. Cost effectivenes is essential to the decision of adding
an index, (as well as keeping one, IMO!)
Good show, folks!
Kellyn Pedersen
Sr. Database Administrator
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen
www.dbakevlar.blogspot.com
"Go away before I replace you with a very small and efficient shell script..."
--- On Sun, 6/27/10, Cary Millsap <cary.millsap@xxxxxxxxxxxx> wrote:
From: Cary Millsap <cary.millsap@xxxxxxxxxxxx>
Subject: Re: Suitable index for the query
To: "Stephane Faroult" <sfaroult@xxxxxxxxxxxx>
Cc: davepacia@xxxxxxxxx, "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
Date: Sunday, June 27, 2010, 9:01 AM
Oh, yes, absolutely, Stephane.
Cary Millsap
Method R Corporation
http://method-r.com
http://carymillsap.blogspot.com
On Sun, Jun 27, 2010 at 5:33 AM, Stephane Faroult <sfaroult@xxxxxxxxxxxx> wrote:
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
Other related posts:
