Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Fetching from a hierarchical data structure?

Re: Fetching from a hierarchical data structure?

From: Jan Hidders <hidders_at_REMOVE.THIS.win.tue.nl>
Date: 6 Apr 2001 23:30:34 GMT
Message-ID: <9aljiq$g37$2@news.tue.nl>

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 Hidders
Received on Fri Apr 06 2001 - 18:30:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US