Printable View
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
Can you give an example of what you want to do?
Please only post your questions in ONE forum!
for example: I have a table Customer(firstname varchar2(20),
lastname varchar2(20),
sex number,
address varchar2(255)
.......................)
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;
--similar with other column: lastname,sex,address...
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
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.