Re: Designing database tables for performance?

On Feb 9, 6:04 am, "Timasmith" <timasm..._at_hotmail.com> wrote:
> Hi,
>
> I am trying to get my head around who to best design a couple of
> tables for performance. The entities are fake so ignore that, I am
> just focusing on the table sizes and values.
>
> Suppose I had a table called items with 50,000 rows
>
> items (item_id number, parent_item_id number, item_name string)
>
> and I had a table called orders with 10,000,000 rows
>
> orders (order_id number, order_date timestamp, order_status number,
> customer_id number, item_id number)
>
> Now items are a hierarchy (hence the parent_item_id) and your average
> custom has 1000 rows in the orders table.
> The 1000 rows represents 100 different items on average.
>
> There are two primary use cases for querying. One is like this:
>
> select i.item_name, o.order_date, o.order_status
> from orders o, items i
> where o.customer_id = ?
> and o.order_date > sysdate-1
> and o.item_id = i.item_id
> and i.item_id in (?....?);
>
> Now ?...? represents a potentially very long list of items -
> essentially a subtree of the item hierarchy.
>
> The other query is searching for a few items across a longer timeframe
>
> from orders o, items i
> where o.customer_id = ?
> and o.order_date > sysdate-30
> and o.item_id = i.item_id
> and i.item_id in (?,?,?);
>
> It seems to me very difficult to optimize this, obviously we leave it
> to the database and cross our fingers that the optimizer will pick the
> right query in the right situation but I cant help thinking there must
> be *something* one can do to help out. The fact the items are a
> hierarchy doesnt help - difficult to avoid the 'in' statement without
> knowing how many levels there are in the hierarchy.
>
> Any ideas?

select ...
from orders
where item_id in
(select ...
 from items
 connect by prior parent_item_id = item_id  start with item_id = < your item>)

should work like I charm (I may have parent_item_id and item_id in the connect by reversed, I always forget).
The idea of JXStern to store the hierarchies in a separate table, when there is CONNECT BY: Uggggghhh!!!
I would recommend learning the ANSI joins only when you know Oracle. Ansi Joins are basically bogus.

--
Sybrand Bakker
Senior Oracle DBA