Re: help with Object Model, relation daabase model, - some theory

Don't denormalize. What indexes do you have? (are the tables analyzed) I would expect on the following query:

> SELECT part, SUM(qty)
> FROM inventory
> WHERE location='A'
> AND state='on_hand'
> GROUP BY part;

Would have an index on
part and state and location. eg
create index part_loc_state on inventory(location,state, part) compress 3; 1.2 million records isn't a lot of records.

> SELECT part,
> SUM(DECODE(state,'on_hand',qty,0)),
> SUM(DECODE(state,'on_hold',qty,0)),
> .... other types
> FROM inventory
> WHERE location='A'
> GROUP BY part;

could be
 SELECT part,

          state,
sum(qty)

    FROM inventory
    WHERE location='A'
    GROUP BY part,state;

or

 SELECT part,

          statetab.state,
sum(nvl(qty,0))

    FROM inventory,statetab st
    WHERE location='A'
    inventory.state=statetab.state(+)
    GROUP BY part,statetab.state;

Post the explain plans and the tkprof results (also version)
Jim

"Don" <dchamber_at_mindspring.com> wrote in message news:0l40ev06lt987bram0a0k0vg2171hnme9e_at_4ax.com...
> What yu describe is approach number 1 in my original post. My post
> only concerned the inventory table but I do have a part and lcoation
> table.
>
> My inventory table has the QTY and a state which I call inventoryType.
>
> This seems like the way to do it on the first approach but after I
> loaded some data and tried a query I found it was slow and required a
> complicated query. I'm thinking my model was right but I need to
> denormalize the tables.
>
> My inventory table has about 1.2 million rows. If I want to see the
> total qty on_hand from each part and location A I have this query:
>
> SELECT part, SUM(qty)
> FROM inventory
> WHERE location='A'
> AND state='on_hand'
> GROUP BY part;
>
> But I need to fill a table with the quantity in each state and this
> got more complicated. I had to have 4 SUMs in the SELECT statement
> and had to use a DECODE to make sure I summed the values in the
> correct column.
>
> SELECT part,
> SUM(DECODE(state,'on_hand',qty,0)),
> SUM(DECODE(state,'on_hold',qty,0)),
> .... other types
> FROM inventory
> WHERE location='A'
> GROUP BY part;
>
> This was very slow because of the Functions in select statement. This
> also became very hard to follow as we moved to more complicated
> queries that joined other tables, including a table with authorized
> quantities setup the same way.
>
> When I created a table with columns for each state I had fewer rows
> becasue each part/location combination was only in the table once. I
> also had no DECODE fucntion.
>
> SELECT part,
> SUM(qty_on_hand),
> SUM(qty_on_hold)
> FROM inventory
> WHERE location='A'
> GROUP BY part;
>
>
> But I don't really like this approach. Seems like I've created
> attributes that are not needed.
>
> Thanks for the input,
> Don
>
>
> On Fri, 06 Jun 2003 03:46:52 GMT, "Jim Kennedy"
> <kennedy-down_with_spammers_at_attbi.com> wrote:
>
> >How about this?
> >
> >1 You have parts.
> >2. You have places parts can be.(locations)
> >3. In those places a part can be in a state. (on hold, on hand, etc.)
> >
> >Table of states of a part.
> > state description
> > on_hold -- in the warehouse put on hold for some reason (don't use
it)
> > on_hand -- ready to be issued
> > ready --- pre-packaged and will be shipped in an emergency
> > owed --- stocked owed by a maintenance shop
> >
> >Table of parts (really a look up table of what parts could possibly be in
> >the system or allowable parts)
> > part_number (usually have a part number or SKU -> stock keeping unit)
> >description
> >
> >Table of Locations
> > Location_number description other attributes of location (eg
> >address?)
> >
> >inventory (what you have where and in what state)
> > location_number , part_number, quanity, state
> >
> >done. New stuff comes in, you add to your inventory, Stuff goes out you
> >subtract from your inventory, you move stuff from one state to another
you
> >update your inventory. all constrained by locations, parts, states.
> >Send me $50.
> >Jim
>
Received on Thu Jun 05 2003 - 23:37:04 CDT