"raghu" <raghu_vnin_at_yahoo.com> wrote in message
news:1133049585.955536.310710_at_g14g2000cwa.googlegroups.com...
> Hi,
>
> We are trying to compare the data of two tables of the same structure.
> In other words, the missing and the different records have to be
> determined. The database being used is Oracle 9i. The options I am
> currently evaluating are
>
> 1. Minus Operator - Since the table has nearly 4 million records, I am
> not sure if this is a feasible option.
>
> 2. Cursors - For each of the tables, cursors can be opened and then the
> data can be compared. But the data cannot be fetched in blocks (using
> the BULK COLLECT LIMIT option), since different records can be fetched
> for each table.
>
> Though the time taken for the comparison is not a constraint, the
> number of hits to database and the processing memory certainly needs to
> be considered.
>
> What are your thoughts? Are there any other mechanisms for comparison?
>
> Thank you.
>
Is there a primary key?
Maybe something like read each table as a stream (in primary key order) and
do a CRC32 check on each. If the tables are the same the CRC 32 should be
equal and if not they will differ. It would cost 2 full table scans and
some calculation.
Jim
Received on Sat Nov 26 2005 - 18:09:06 CST
