Re: Suitable index for the query

From: Kellyn Pedersen <kjped1313_at_yahoo.com>
Date: Sun, 27 Jun 2010 19:18:07 -0700 (PDT)
Message-ID: <262734.71405.qm_at_web120208.mail.ne1.yahoo.com>

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_at_method-r.com> wrote:

From: Cary Millsap <cary.millsap_at_method-r.com> Subject: Re: Suitable index for the query To: "Stephane Faroult" <sfaroult_at_roughsea.com> Cc: davepacia_at_yahoo.com, "Oracle-L" <oracle-l_at_freelists.org> 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_at_roughsea.com> 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

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

Received on Sun Jun 27 2010 - 21:18:07 CDT