Re: force use of an index - how to?

Jim Kennedy (odysscci_at_teleport.com) wrote:
: Let us say I have a table called person with lastname,firstname,middlename. I
: want a list of everyone but sorted by lastname,firstname,middlename.
 

: thus:
: select * from person order by lastname,firstname,middlename;
 

: Assume I have created an index on lastname,firstname,middlename.
 

: I am using Oracle 7.1.3.3 and It keeps doing a full table scan
: and then sorting the results. Not good. I have added where rownum<1000 and
: that certainly is much faster, but I want the possibility of fetching the
: whole result set. I have tried hints by specifying the index to use and I
: have tried setting things up so I get best response time on first rows.
: Nothing made it any better. Any suggestions besides don't do that.
 

: Jim Kennedy

Jim,

I would suggest doing something with the soundex function that is a SQL built-in. This function alone could signifigantly improve your performance.

How to implement this use I don't know right now, but I can think of some distinct ways:

• Add a fourth column to you table to contain the soundex output of the lastname column. Then place the index on this column. This would definitely help performance, since the soundex value would almost be a unique index.
• Order by the soundex of the lastname column (not sure about this one, but I don't think it will help).
• Place a where clause on the select statement along the order of "where soundex (lastname) > 0". Remove the index on the table, since it is now unused (executing soundex on the column before the comparison assures that the index is unused).

Again, these are only ideas. I've never had call to use the soundex function in my work, but I do know that it provides signifigant performance enhancements over string comparisons alone.

Please let me know how your problem turns out. Hope this can be helpful in some way.

Jonathan Ingram
jwingram_at_whale.st.usm.edu Received on Wed Apr 19 1995 - 00:00:00 CEST