Re: SQL Tunning Issue

IN sucks and causes full scans
You can replace it by exists

select type from sec_view a where exists ( select 1 from sec_view b where objid = 12445 or
objid =12446 and a.pk = b.pk)

pk = primary key
do an explain plan
you will see the differerence

Otherwise i don't see how you can force a IN clause no to do full scans.

Patrick Wtterwulghe
Oracle DBA

Roman Gelfand <rgelfand_at_masmid.com> wrote in article <6q8fhs$3vt$1_at_news.monmouth.com>...
> It appears that when multiple values are used in the IN clause, Oracle
gets
> very confused.
>
> In the following example
>
> select type from sec_view where objid in (12445, 12446);
>
> When using the tkprof to explain this statement, it tells me that a full
> table scan is done on this table eventhough the objid is indexed
uniquely.
> Also, this table has been analyzed and it's size is 300,000 rows.
>
> Since I can not change this query nor the view, can anyone suggest how I
> could force Oracle to use indexes in this situation (since I can not
change
> the query or the view, the hint can not be used).
>
> Thanks
>
>
>
>
Received on Wed Aug 05 1998 - 02:52:22 CDT