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: Designing database tables for performance?

Re: Designing database tables for performance?

From: Timasmith <timasmith_at_hotmail.com>
Date: 19 Feb 2007 04:58:28 -0800
Message-ID: <1171889908.532512.184500@v33g2000cwv.googlegroups.com>


On Feb 18, 8:00 pm, "Mike Preece" <mich..._at_preece.net> wrote:
> On Feb 9, 5: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?
>
> Are you interested in logical or physical performance?- Hide quoted text -
>
> - Show quoted text -

When tables get that big the data is rarely in memory and so while perhaps some of the indexes will be, I have to say physical performance assuming reads from disk. Seems like I got some good ideas from the posts, in a few weeks I will mock it up and see how it flies with the various options. Received on Mon Feb 19 2007 - 06:58:28 CST

Original text of this message

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