Re: Compare 2 tables
Date: Mon, 9 Mar 2009 06:40:53 -0700 (PDT)
Message-ID: <663e65a0-ae10-46c4-a0ea-208e31b5ebc8_at_x13g2000yqf.googlegroups.com>
On Mar 9, 7:44 am, coral.ressour..._at_gmail.com wrote:
> On Mar 9, 1:00 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>
>
>
> > coral.ressour..._at_gmail.com schreef:
>
> > > I have 2 tables: Input and Output.
> > > Input table stores records in this manner:
> > > recno, prod_first, prod_span, prod_last
> > > 1, 1, 1000, 1000
> > > 2, 1001, 700, 1700
> > > 3, 1701, 100, 1800
> > > ...and so on
> > > prod_first is the id of the product,
> > > prod_span is the number of products stored by recno,
> > > prod_last is the last product stored by recno
>
> > > Output tables stores records related to outgoings:
> > > recno, prod_first, prod_span, prod_last
> > > 1, 500, 25, 525
> > > 2, 300, 100, 400
> > > 3, 1243, 100, 1343
> > > That mean productss no from 500 to 525, 300 to 400 and 1243 to 1343
> > > were delivered, while the rest from input table are still in stock.
> > > Question: how to find what is [still] in input table and not [yet] in
> > > output table?
> > > prod_first to prod_last are the items to be found in both tables.
> > > I tought like this:
> > > 1. make 2 temp tables: input_temp, output_temp where i would insert
> > > all records [one-by-one] since in original tables i have only start &
> > > end.
> > > 2. make a comparison between the 2 temp tables (using SQL aggregation)
> > > I am concerned that step 1 would hit the db performance as i have for
> > > input table aprox 100 000 [and growing!] records to insert.
>
> > In your system, is it possible to have as outgoing:
>
> > 1, 1650,100,1750 ? So: overlap on boundaries of ingoing?
>
> > Shakespeare
>
> yes, we can have, but interval could not be overlapped; for instance:
> i can have as outgoing following records:
> 1, 500, 25, 525
> 2, 300, 100, 400
> but not:
> 1, 500, 25, 525
> 2, 300, 300, 600
> because 2. record would overlap something that is [already] out...
So really the question is which is faster:
- loading a couple temp tables and letting the SQL engine do the comparison this seems the "brute force" solution. It will work. I think the slow part of this is loading the temp tables. Properly indexed (use IOTs?), the comparison should not be a problem.
- running a PL/SQL procedure that does the comparison and stores it in some temp table. This puts all the burden in the developers hands. Generally PL/SQL is slower than SQL, but this might take advantage of some knowledge of the tables. there will be two open cursors and I cannot figure out a algorithm that could be any faster than row by row comparisons.
It seems to me that it should not take much time to write and run both. Run some timings and let the best plan win. 100,000 rows is not that many really. Even if that is the row count for the input/output tables, then the temp tables would be on order of 100,000,000 rows, but each row would be just the ID. I actually think I would have designed this using tables more like your temp tables. The net space used would be less and these comparisons would be easier.
Given no overlap in either input or output tables. But I think the
question was:
is there overlap between input rows and output rows? IOW,
given input table rows like:
95, 301,100,400
96, 401,200,600
can there exist an output table row of:
67, 351,200,550
???
Sorry if this is not much help so far. This sounds interesting. I'll think about it some more. It sure looks like some memory allocation schemes I have seen and used. I think I'd lean to a PL/SQL process and one other table to store results.
Ed Received on Mon Mar 09 2009 - 08:40:53 CDT