Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Fetching from a hierarchical data structure?
Maury Markowitz wrote:
>
> shipping billing
>
> A 1
> / \ / \
> B C 2 3
>
> If the user wants to see everything in B and 3 that's fine...
>
> SELECT * FROM orders WHERE shipping_id='B' and billing_id='3'
>
> But consider what happens when they select A and 1...
>
> SELECT * FROM orders WHERE (shipping_id='B' and billing_id='3') or
> (shipping_id='B' and billing_id='2') or (shipping_id='C' and billing_id='3')
> or (shipping_id='C' and billing_id='2')
>
> That might not look so bad, but consider the case when there's 50 items in
> each tree and they pick items in the middle.
I assume you have a good reason why you compute the identifiers first and then generate a query that explicitly mentions them instead of simply computing them directly in the SQL query itself. Otherwise I would suspect that this is a case of sloppy design by someone who is afraid of complicated SQL queries.
Anyway, you can make the query shorter by not using the disjunctive normal form, i.e., a condition of the form
( .. and .. ) or ( .. and .. ) or ( .. and .. )
but in the conjunctive normal form, i.e., of the form
( .. or .. or .. ) and ( .. or .. or .. )
So, in this case you would get:
SELECT *
FROM orders
WHERE (shipping_id='B' or shipping_id='C') and
(billing_id='2' or billing_id='3')
As you can see this is shorter and with 50 items in both trees you would have 50*50*2=5000 terms in the disjunctive normal form and "just" 50+50=100 terms in the conjunctive normal form.
Kind regards,
-- Jan HiddersReceived on Fri Apr 06 2001 - 18:30:34 CDT