1. Junior Member
Join Date
Jun 2003
Location
Vietnam
Posts
9

Compare two records

Hi all,
I have a table to store information about one person(firstname,lastname,birthday,sex,address,...). My table have a lot of record (about 1000000 rec). Could you show me how to compare one record with the other in this table(to count the number of the same column)? I have tried to test compare but it very slowly. I think my algorithm is not optimal.
Can you help me?(show me the algorithm) Thanks!
Trung

2. Foreign Script Kiddie
Join Date
Aug 2002
Location
Posts
5,253
Can you give an example of what you want to do?

4. Junior Member
Join Date
Jun 2003
Location
Vietnam
Posts
9
for example: I have a table Customer(firstname varchar2(20),
lastname varchar2(20),
sex number,
.......................)
I want compare a record with other record in this table. I write a function:
Function Compare(rowid1 varchar2,rowid2 varchar2) return number is
str1 varchar2(255);
str2 varchar2(255);
v_value1 varchar2(100);
v_value2 varchar2(100);
n_count number:=0;
Begin
str1:='select firstname from customer where rowid=''rowid1''';
str2:='select firstname from customer where rowid=''rowid2''';
execute str1 into v_value1;
execute str1 into v_value2;
if str1=str2 then
n_count:=n_count+1;
end if;
return n_count;
End;

But when the number of record is too big(about 1000000 record) then the speed is very bad, very slow. Can you show me another algorithm?
Thanks!
Trung

5. Junior Member
Join Date
Mar 2003
Posts
60
Do a self join - should run faster than PL SQL:

select count(a.rowid) from cust a, cust b
where a.fname = b.fname

This should give all rows with same fname. try.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

×