Designing database tables for performance?

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? Received on Fri Feb 09 2007 - 06:04:08 CET