Re: How to 'force' optimizer to use indexes?

In article <8a3gkl$ig$1_at_nnrp1.deja.com>,   amanda95_at_my-deja.com wrote:
> Hi, everyone:
>
> We need to run a query described as follows:
>
> select f1, f2, .., fn
> from t1
> where f3=f5,
> and f2 like '%string%'
> order by f2
>
> where f1, f2,..., fn are fields of table t1 (with
> about 1.2 million records), f2 is the leading
> column of a concatenated index.
>
> In the past, when a leading % is used in like
> clause, the index on f2 would not be used. Yet,
> when I tested the query on the Oracle 8.06 box, a
> full index scan (using the concatenated index) is
> used to retrieved rows. When I tested the query
> on Oracle 8.05 box, it used full table access.
> It took about 0.5 seconds when using full index
> scan and over 20 seconds using full table
> access. Here are my questions:
>
> 1. When a leading % is used in like clause, an
> index can still be used. Is this a new feature
> of Oracle 8( or 8.06)?
>
> 2. How can I 'force' or 'induce' optimizer to use
> index instead of full table access? For example,
> I also need to run the following query:
>
> select count(*)
> from t1
> where f3=f5,
> and f2 like '%string%'
>
> I could not get optimizer to use the index on f2
> although doing full index scan would improve the
> performance significantly.
>
> Any of your input or suggestions would be greatly
> appreciated.
>
> Chang
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

It won't force it but here is something to consider trying...

instead of:

select * from T where indexed_column like '%ZZ%' try:

select /*+ FIRST_ROWS */ *
 from T
where rowid in

      ( select /*+ INDEX_FFS( T IDX_NAME ) */ rowid
          from T
         where INDEXED_COLUMN like '%ZZ%' )

that does a fast full scan on the table to find the rowids that match and then uses them to do an table access by rowid to pick up the rest of the row.

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.