Re: Efficiency and usage of IN operator

Alexander,

If you're considering pre-processing the container hirearchy, why not just explode it into a two column table of ("ancestor","descendant") then join to your object table on container_explode.descendant = object.container_id and container_explode.ancestor = '534'. (Obviously here each container would have an explosion record mapping it to itself).

I belive that you can use Oracles 'connect by' syntax to construct your explosion table dynamically from a trigger on the container table. Assuming that containers are infrequently updated/inserted as you have suggested.

What do you think?

Regards, David.

Alexander Staubo wrote:

> <SNIP>
>
> Basically, slightly abstracted, each "object" (table row) has information
> about which "container" (some external object) it's in. The reference is
> a simple ID referencing a foreign key. Containers are nested in a tree-
> like structure, though, so when I want to list the contents of
> MyContainer, I want to include the contents of all sub-containers, and
> sub-sub-containers of MyContainer, as well. In other words, I'd like to
> do a recursive query.
>
> Example: If MyContainer has the ID 534, and its subcontainers have IDs
> 945 and 733, then the SQL query becomes something like...
>
> select ...
> from objects o
> where o.id in (534, 945, 733)
>
> This is pretty decent for a small number of subcontainers, but for deeply
> nested hierarchies the list given to IN can be large.
>
> I thought of a marginally "dirty" way of optimizing this query. Or
> at least I believe it can be faster. Since the containment structure is
> fairly static -- the tree doesn't change all that much -- I can
> preprocess the database. For every object I encode the containment path
> that can refer to the object, and reverse it so that the deepest
> container is listed first: So in the case above, the object has a
> character-type field named "preproc_ids" with the value "733,945,534,".
> To find an object "which is either in container 534 or any of its
> subcontainers", I perfer this lookup:
>
> select ...
> from objects o
> where o.preproc_ids like "534,%"
>
> I believe this will work, although I'm still not getting the sheer
> performance of an equality comparison
>
> Unfortunately the above solution requires the database to be pre-
> processed. Not very cool.
>
> [snip]
> > Bottom line, as a standard rule (and I have never known of an exception to
> > this rule), given a choice between IN and multiple OR's, I always go for the
> > IN, simply because it takes less typing without effecting the query plan one
> > way or the other.
>
> That seems to be the case, yes.
>
> --
> Alexander Staubo http://www.mop.no/~alex/
> "It has taken the planet Earth 4.5 billion years to discover it is
> 4.5 billion years old." --George Wald
Received on Mon May 31 1999 - 20:31:57 CDT