| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Index hints are not working
Hi Mark
Thank you for your answer.
I have tried to use the label. No change there. I have searched: http/technet.oracle.com for the bug or a patch on my 8.0.5.0.0 release. I can't seem to find any. The only thing I found to download was a complete Oracle EE server 8.1 400 Mb or something like that. !!!
I have changed the statement to "UNION ALL'. For the query specified its now working well. But I am afraid that some other querys will not read the data using the INDEX.
The simple:
SELECT /*+ INDEX(DS001 IDX_DS001A) */ * FROM DS001 Does a table scan. I want to have the records returned in the index order. If I add an ORDER BY clause the query takes to long. Over the fact that a full table scan would be the best way:
I hope you or another one have some comment on this.
Kind regards Klaas Oosterhuis
markp7832_at_my-deja.com wrote in message <8a8llf$ote$1_at_nnrp1.deja.com>...
>Well I notice that your first couple of explain plans say RULE. You
>also have OR clauses and the OR disables the use of an index. The
>optimizer should try to convert this into the union of multiple queries
>each of which uses the index for one value for both the RULE and CBO.
>
>Try select /*+ INDEX(a IDX_DS001A) */ a.*
> from ds001a a
> where ......
>
>I have had a few problems getting Oracle to use an index hint and it
>seems like using a label instead of the table_name is the way to go. I
>would also update the statistics with a large sample size or a compute.
>
>I do not know your data but are you sure a full table scan is not the
>best way to go on this?
>
>One last thing. I read on Metalink about a bug in the CBO with one
>release of ver 8.0.5 something where the CBO would not use an index.
>Oracle posted the bug number and said there was a patch. I believe
>that if you are at 8.0.5.2 you are past the bug.
>
>In article <8a7siq$4to1m$1_at_reader2.wxs.nl>,
> "Klaas Oosterhuis" <K.Oosterhuis_at_asysco.com> wrote:
>>
>> Hi
>>
>> I am using Oracle 8.0.5 evaluation version.
>>
>> I want to read records from a TABLE USING IN ALL SITUATIONS an
selected
>> index. To force this I use hints but nothing happens. Its ignored for
some
>> reason by the ORACLE database and a full table scan is executed!!!!!!
>> How can I force the INDEX ??????????????????
>>
>> EXPLAIN PLAN FOR SELECT /*+ INDEX(DS001 IDX_DS001A) */ * FROM DS001
>> WHERE (SW_DS001A = 'J' And DS001FD1 > 18) Or
>> (SW_DS001A = 'J' And DS001FD1 = 18 And DS001FD2 > 0) Or
>> (SW_DS001A = 'J' And DS001FD1 = 18 And DS001FD2 = 0 And DS001FD3 > 0)
Or
>> (SW_DS001A = 'J' And DS001FD1 = 18 And DS001FD2 = 0 And DS001FD3 = 0
And
>> DS001FD4 >= 0)
>>
>> Operation Options Owner Obj-Name
>> Optimizer Search Id/par.id/pos./cost/cardi./bytes
>>
>> SELECT STATEMENT
RULE 0.00
>> 0 1 1 2 200
>> TABLE ACCESS FULL PCSBASE DS001 1
0.00 1
>> 0 1 1 2 200
>>
>> Same statement but with an ORDER BY:
>> ORDER BY SW_DS001A, DS001FD1, DS001FD2, DS001FD3, DS001FD4;
>>
>> SELECT STATEMENT
RULE 0.00
>> 0 3 3 2 200
>> SORT ORDER BY
>> 0.00 1 0 1 3 2 200
>> TABLE ACCESS FULL PCSBASE DS001 1
0.00 2
>> 1 1 1 2 200
>>
>> And still it uses an FULL scan.
>> When i turn the session back:
>> ALTER SESSION SET OPTIMIZER_MODE=CHOOSE
>> There is no change.
>> SELECT STATEMENT CHOOSE
>> 0.00 0 3 3 1 100
>> SORT ORDER
BY 0.000.00 2 1
>> 1 0 1 3 1 100
>> TABLE ACCESS FULL PCSBASE DS001 1
("SW_DS001A",
 |
 |