Re: optimizing problem: strange behaviour...

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 6 Sep 1999 16:27:17 +0100
Message-ID: <936631873.261.0.nnrp-14.9e984b29@news.demon.co.uk>

Is this any good ?

select * from a
where pk_a in (

select a from (

        select distinct a,b
        from r
        where pk_b in (value1, value2, value3.. valueN)

    )
    group by a
    having count(*) = N
)

This get all rows in R with pk_b in the required list, then gets all the distinct combinations of (pk_a,pk_b), then returns only the pk_a values for which there are exactly N values (where N is the number of input pk_b values)

The reason why sorting the subqueries by length of pk_b sometimes works may simply be to do with the fact that the long pk_b is, the 'more unique' it is, so that the list supplied to the next (higher) layer of subquery is already small and therefore more efficient.

You __might__ get Oracle to re-write the query in this fashion automatically by creating a stats histogram on the pk_b column.

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Received on Mon Sep 06 1999 - 10:27:17 CDT