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: 8 Feb 2007 22:37:25 -0800
Message-ID: <1171003039.992130.68050@v45g2000cwv.googlegroups.com>


On Feb 9, 1:09 am, JXStern <JXSternChange..._at_gte.net> wrote:
> On 8 Feb 2007 21:04:08 -0800, "Timasmith" <timasm..._at_hotmail.com>
> wrote:
>
>
>
>
>
> >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.
>
> Are you sure about the use-case?
>
> If the item_id list is so long, it sounds like you only want to
> exclude a few very rare orders. That sounds like a very odd business
> case to me.

I dont understand that comment, I want to include say 1000 items out of a possible 50,000. That is 2% of the possible items, perhaps it will cover 2-20% of the items ordered on the customer - impossible to say as it depends on many factors.

>
> You'd probably want to build some kind of association table and store
> the various subtrees in some implicit or explicit form, then the last
> clause would just be
>
> and i.item_type = 12
>
> plus or minus whatever the predecessor function is,

Yes, that sounds interesting, you might be onto something there.

>
> and maybe you can make time in your busy day to learn the ANSI join
> forms:

I've used it but it is a little verbose for a google post. You are not telling me it runs faster I hope that the one I wrote I hope.

>
> from order o inner join items i on o.item_id = i.item_id.
>
> Doesn't look like a horrible query, should go in a fraction of a
> second on a big server with lots of RAM cache.
>

Yes, Oracle does like its RAM.

>
>
>
>
>
>
> >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?
>
> Make sure you have a nice covering index on customer_id + order_date,
> and this should crank just fine.
>
> J.- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Received on Fri Feb 09 2007 - 00:37:25 CST

Original text of this message

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