Re: How to optimize this query?

Have you considered creating a function index using SOUNDEX, and then performing a soundex query instead ?

SELECT
 count(*)
FROM t
WHERE soundex(surname) = soundex('SMITH');

On 12 Aug 2003 01:43:42 -0700, vslabs_at_onwe.co.za (Billy Verreynne) wrote:

>ctcgag_at_hotmail.com wrote in
>
>> Out of curiousity, if there's an index based on (surname, age), might
>> Oracle decide to combine a range scan on surname with a skip-scan
>> on age for only things in the right surname range?
>
>Yes. That is exactly the point. If the only index on surname is index
>(surname,age) and you do a :
> SELECT
> count(*)
> FROM t
> WHERE surname = 'SMITH'
>
>.. then you stand an excellent change of hitting an index range scan
>as Oracle will likely decide on using the (surname,age) index.
>
>Just remember though - I am _not_ saying that index range scans are
>bad. Simply that they can be bad. And that only happens when the range
>scan hits a sizeable portion of the index.
>
>If 80% of the data matches your criteria, why use an index to identify
>that 80%? Why not process the table instead? Very likely the cost of
>reading that additional 20% of table data you do not need, is _less_
>than the cost of having to read the index with a range scan.
>
>This percentage is also not fixed. It can decrease very quickly with
>the increase in size of the table and index. In which case a full
>table scan using PQ can be faster than an index range scan even on 10%
>of the data (as 80 PQs spread among 10 OPS/RAC nodes can work faster
>through the 90% non-relevant data, than a single process can range
>scan through a 10+GB index).

MylxHQ - Oracle Resource Portal
http://oracle.mylxhq.com
bsc7080mqc_at_mylxhp.com
(replace mylxhp with mylxhq to reply/contact) Received on Tue Aug 12 2003 - 06:20:04 CDT