Re: Query optimisation

On 21 Nov 1997 04:23:35 GMT, "Geoff Dewhurst" <Geoff_at_Bass-Soft.com.au> wrote:

>Hi,
>
>Even when I use a hint, it still does not want to use that index! I thought
>that a hint would force it to use the index, but that does not seem to be
>the case. Any thoughts?
>
>Geoff
>
>Keith Boulton <boulke_at_globalnet.co.uk> wrote in article
><3472cca1.506968_at_read.news.global.net.uk>...
>> On 18 Nov 1997 23:24:40 GMT, "Geoff Dewhurst" <Geoff_at_Bass-Soft.com.au>
>> wrote:
>> .....
>> Even using an index the second query would be slower than the first
>> because of the need to perform a table access. You could use the index
>> hint to see the performance with an index e.g.
>>
>> SELECT /*+ INDEX( CLR CLRIDX ) */ CODE FROM CLR WHERE CLRIDX = 1;
>>
>

 It took me a little while to reproduce this. create table clr ( clridx number not null, code varchar2(200) not null );
insert into clr values( 1, 'Fred' );
analyze table clr compute statistics;
alter table clr add constraint clridx primary key ( clridx ); explain plan for select code from clr where clridx = 1; SELECT STATEMENT Cost = 1
  TABLE ACCESS FULL CLR Repeating the analyze command then cause the index to be used even though the full table scan should be quicker. SELECT STATEMENT Cost = 1
  TABLE ACCESS BY INDEX ROWID CLR
    INDEX UNIQUE SCAN CLRIDX Repeating the exercise with a hint in the select statement explain plan for select /*+ index( clr clridx ) */ code from clr where clridx = 1; without re-analyzing also causes the primary key index to be used;

There are two things to note however. First, both table and index name have to be given. Secondly, the index name is the name of the index not the name of the primary key column so for instance if the primary key is created with alter table clr add constraint fred primary key ( clridx ); then the statement select /*+ index( clr clridx ) */ code from clr where clridx = 1; results in a full table scan.

These experiments were carried out using oracle 8 on windows NT 4. So you should first regenerate statistics for the table and check the access path and second, ensure that the syntax of the hint is correct. The biggest problem with hints is that they can be incorrect and ignored with no warning. Received on Fri Nov 21 1997 - 00:00:00 CST