Re: force use of an index - how to?

In article <3n35ie$7ue_at_server.st.usm.edu>, jwingram_at_whale.st.usm.edu says...
>
>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

In your situation, I would not expect that you would want to use an index. All you are doing is adding more overhead to your query. Indexes are most benificial when retreiving a small (my personal preference is 15% of the table or less) sub-set of the rows in a table. What you are talking about is going to a an index to find the row in a table for EVERY row in the table. You are doing a full table scan anyway, as well as a full index scan. Doesn't make much sense to me.

Oracle does not use indexes if you do not specify any where conditions because this means you are selecting all rows from a table therefore a full table scan would DEFINITELY be quicker than using an index.

From the Oracle Application's Developer's Guide:

Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, you should create indexes on tables that are often queried for less than 2% or 4% of the table's rows. This guideline is based on these assumptions:

· Rows with the same value for the column on which the query is based are uniformly distributed throughout the data blocks allocated to the table.

· Rows in the table are randomly ordered with respect to the column on which the query is based.

· Each data block allocated to the table contains at least 10 rows.

· The table contains a relatively small number of columns.

· Most queries on the table have relatively simple WHERE clauses.

If these assumptions do not describe the data in your table and the queries that access it, the percentage of the table's rows selected under which an index is helpful may increase to as much as 25%."

If you really want to use an index though, you can use the INDEX hint in your query.

Hope this helps.

• Rick