Re: A nice PL*SQL Problem..

In article <3193A358.C0B_at_us1j69.glaxo.com>, John Jones <jmj22026_at_us1j69.glaxo.com> says:
>
>I know this is not what you asked, but I thought I would give you a little insight
>anyway. When you do a sub-select, it does a full table scan instead of using the
>indexes. I would suggest using Exists and Not Exists in your select statement. It
>forces the use of indexes. I did an Explain Plan of the sub-select statements that went
>across database links and it did some merge joins, sort joins, unique sorts, and full
>table accesses. There were about 7 steps in all. When I did the Exists and Not Exists
>it was down to 3 statements.
>

When you make a sub-select using the in operator the sub-select is processing first using whatever indexes might fit. Then it uses the result of the subselect to access the outermost select using whatever index that might fit.

The morale is, if you expect it to perform the subselect first then use the in-operator, else use the exist-operator.

If you expect the result of a subselect using the in-operator should hit a concatened index then be aware, that all fields in the index both from the subselect and others for instance constants, all must be selected from the subselect in order to use the index.

For instance

 select 1 from tab_a
  where f1=123 and f2 in (select x1 from tab_b where x2=1 )

 This will do the subselect using an index on tab_b.x2if any, then try  to solve the select in tab_a. If a concatened index exists on tab_a(f1,f2)  then the f2-part will not be used. Instead the f1-part will be used, followed by a table scan  to test the f2-part.

Instead you should write

 select 1 from tab_1
   where (f1,f2) in (select x1,123 fromtab_b where x2=1),

Yours

Rene Brisson
rbrisson_at_centrum.dk Received on Tue May 14 1996 - 00:00:00 CEST